Unique Identifier on Multiple Fields

Question

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
------------------------------

markshnier__you · Answer

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
------------------------------

austink · Answer

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.

babipanjikar · Answer

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 · Answer

Hi Babi and Mark;This was exactly what I was looking for at this point.&nbsp; I have another question, however. Is there a way to modify the error message so that it will make sense to the user?&nbsp; In the example above, the error message would be something like "You have entered a duplicate value in "Reference Key."&nbsp; I can't imagine what a user is going to do with that information!&nbsp; 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?ThanksDave------------------------------David Halter------------------------------

markshnier__you · Answer

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
------------------------------

Forum Discussion

Unique Identifier on Multiple Fields