It regularly comes up that some column in your table should have unique values, and you’d like to ensure that when new values are added, they are not duplicates.
Grist doesn’t have a built-in feature to detect or prevent duplicates (yet), but it is possible! Here’s a recipe.
Let’s say you have a table Products, which has a column ProductCode that should have unique values. You can create a column to detect duplicates. Let’s name it IsDup. Give it the following formula:
"DUP" if len(Products.lookupRecords(ProductCode=$ProductCode)) > 1 else ""
This new column will then show the value “DUP” for rows that contain non-unique ProductCode values. You could filter for such values, and then go through manually to remove duplicate rows, or to change codes to make them unique.
To take it a step further, you can prevent the creation of duplicates using Access Rules. Open the Access Rules page, click “Add Table Rules” and select your table, then add a rule with this condition:
newRec.IsDup == "DUP"
And disable Update and Create permissions when this condition matches by toggling the “U” and “C” boxes next to it, to make them red.
Now you will not be able to create new records that create a duplicate code, or to change a code to one that already exists. This rule will also prevent you from making any change to a record with a duplicated code (except to fix the code).
You can see this entire recipe at work in this example document:
https://public.getgrist.com/oWek6m8n9HLo/Prevent-Duplicates/m/fork
The example has a duplicate code to show what it looks like, created before the access rule was set; the rule will prevent new duplicate codes from being created.
