Unique Calculation Result

[Chuck]

Question: How do I validate that a calculation field, such as a full name field, is unique?

Answer: Here's the situation. You have two fields: First_Name and Last_Name, both of which are text fields. You also have a field Full_Name which is a calculation text field, set to First_Name & " " & Last_Name, that you want to ensure is unique. Unlike text, number, date and time fields, calculation fields don't offer any sort of validation. Here's at least how to validate that the calculation produces a unique value.

First, you file must have a field the uniquely identifies each record. You should have this anyway, but if not, create a field called something like Contact_ID and have it auto enter a serial number.

Then create a self-join relationship with the calculation field as the match field on both sides of the relationship. I'll call this relationship "SelfJoin~Unique".

Next, create a calculation field called Unique_Validation with the same result as your ID field. I my case it's text, but many developers use numbers for their unique IDs. Just make sure what whatever you use, this field has the same result. Set the calculation to be:

SelfJoin~Unique::Contact_ID

Next, for each field that can alter the calculation field you want to validate as unique, go to the validation options and choose "By Calculation" with the following calc:

Unique_Validation = Contact_ID or IsEmpty( Unique_Validation )

Remember to do this for each field that can change the calculation, in this case, First_Name and Last_Name. You can optionally make the validation strict, in which case the user can't override it, but you should at least provide a custom message to let the user know why the validation is failing when that happens.

Here's how this works. Let's say I have a record where the Full_Name field has "Chuck Ross" in it and the Contact_ID is set to "1" I create a new record with Contact_ID "2" and enter "Chuck" for the first name and "Ross" for the second name. The self-join relationship will find the first record created that is a match, which in this case is "1", but the ID of the current record is "2" which isn't the same, so the validation for the last name fails when I enter it. The IsEmpty portion of the validation calc makes sure that it works while you enter something for the first time.

Chuck

[birchtree]

Hi Chuck

What I have been doing to make a calculation field unique is to copy and paste it into another regular field that is defined as unique. It is very easy and works great. At least I think it is working like it is supposed to.

birchtree

[Chuck]

That's a good solution, and one I hadn't thought of. Two things to point out. The solution I've outlined doesn't require a script. It alerts the user to the fact that the full name is not unique as soon as the data is entered. Also, I tend to avoid copy and paste as it destroys any user contents of the clipboard and requires that the fields be on the current layout when the script steps are performed. Set Field doesn't have either of these limitations, and might be a good way to improve what you've done.

Chuck