Expand all | Collapse all

Unique Identifier on Multiple Fields

  • 1.  Unique Identifier on Multiple Fields

    Posted 10-22-2019 16:15
    Is there a way to set up your Unique Identifier (key) based on multiple field combination? i.e., If I have fields User and Date, I want to restrict to have a specific user on a specific date only once. Same user should only have one record with same date in that table.
    In most databases, that's pretty much standard feature to define your own Key based on combination of fields, but could not figure out how to handle this in QB. Any thoughts?
    Thanks in advance.

    Muhammad Ghazali

  • 2.  RE: Unique Identifier on Multiple Fields

    Posted 10-22-2019 16:54
    I don't believe this is possible anymore but I'd love to find out a way to do it as well. You used to be able to do it via a formula field and setting it with the API but they made a change somewhat recently that made this impossible to do.

  • 3.  RE: Unique Identifier on Multiple Fields

    Posted 10-22-2019 17:04
    You can make a formula text field which concatenates the values together and mark that as unique - but they would not be the Key field.

    The other option if the data entry is on a form is to calculate that concatentation and have a form rule populate a data entry field with the caluclated value and then set that data entry field to be the Key field.

    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach

  • 4.  RE: Unique Identifier on Multiple Fields

    Posted 10-22-2019 23:40
    To add to Marks response, you can achieve this with enhancements to marks response. I do use these kind of scenarios many of the times to control my tables uniqueness 

    Follow these steps:
    1) Create a text field, e.g. Reference Key, type Text and set this as unique unique.
    2) Create a formula text field CF-Reference Key = ToText(UserToEmail(User())) & ":" & ToText(Today()). Replace today with date field. I used UserToEmail becase name can be duplicate but email address cannot.
    3) Use the form rule to update the Reference Key field with CF-Reference Key on record save.

    If you are uploading the data using import functionality, make sure that your excel or csv file is having the concatenated value.

    Happy Quickbasing.

    Babi Panjikar