Forum Discussion

MuhammadGhazali's avatar
MuhammadGhazali
Qrew Trainee
5 years ago

Unique Identifier on Multiple Fields

Hi,
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
------------------------------
Formulas and functions
Tables and fields

5 Replies

Sort By
  • AustinK's avatar
    AustinK
    Qrew Commander
    5 years ago
    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.
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      5 years ago
      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
      http://QuickBaseCoach.com
      markshnier2@gmail.com
      ------------------------------
  • BabiPanjikar's avatar
    BabiPanjikar
    Qrew Assistant Captain
    5 years ago
    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
    ------------------------------
    • DavidHalter2's avatar
      DavidHalter2
      Qrew Member
      4 years ago
      Hi Babi and Mark;

      This was exactly what I was looking for at this point.  I have another question, however. Is there a way to modify the error message so that it will make sense to the user?  In the example above, the error message would be something like "You have entered a duplicate value in "Reference Key."  I can't imagine what a user is going to do with that information!  I would need them to know something like "Each user can only make one entry per day - go review your prior entries and adjust as necessary."

      Is that possible? -- Maybe it would be accomplished with a Table Rule?

      Thanks
      Dave

      ------------------------------
      David Halter
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        What is do is stupid simple. Instead of calling my unique field [Reference Key], I contrive a longer field name which is descriptive to the user when integrated with the standard Quickbase message.

        [Sorry, but this combination of blah blah blah is already taken].

        Just iterate on the field name words until you like the error message.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------