Unique Record Validation
Depending on your requirements, checking fields for unique values can often be somewhat complex and can vary in complexity depending on your requirements. In this short article we'll look into 3 different unique record validation approaches available to you.
Assigning unique record validation on the field level.
To enable unique validation checks on the field level, open any field and set the value of "Unique" to "Yes"
This method will check for unique values no matter where the data is entered or edited including: Inline editing, Record Rules, Importing, and API (coming soon).
Another method would be to add unique validation on the form using Validation Rules within the form. Adding validation on this level will only impact this particular form, not on the data table level. This approach allows you to customize the message displayed to the user when this validation check is triggered.
To do this, open any form and go to the Validation Rules menu as seen in image below:
Both of the above approaches will work to check if the value is unique throughout the entire table on a column level, not record level. Let's see the difference and how we can check for validation on a per record level as well.
When checking uniqueness on a column level, having multiple rules will run against each column and if any values are returned as not unique the error message will be displayed and the value will not be saved.
For example: If we want to ensure that the "Job Name" AND "Job Description" fields are unique we can add a validation rule to check that both Name and Email are unique. The validation will check if both values exist anywhere in those columns, if both are found an error will be returned.
With the above settings every record inserted will be checked for uniqueness in both fields and if they both exist an error will be shows as you can see below:
Now suppose we want to only validate uniqueness within each record, not against all records in column For example: we don't mind having the same value twice, as long as its not within the same record, we can do that with a different setting by enabling the "Check Additional Fields" option and choosing which other fields/columns should be checked.
Even though both values exist, since they don't exist in the same record the value will be inserted without any errors: