Ensure unique values or detect duplicates

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.

4 Likes

There is now a video tutorial that breaks down this formula and explains how to modify it.

4 Likes

A couple of notes about this example:

  1. Add just: len(Products.lookupRecords(ProductCode=$ProductCode)) > 1
  1. Add as first rule: newRec.IsDup
3 Likes

I found a way to get unique values in new table.
First you need to group Products table by Product Code.
Create new table, create integer column that you will use to match id column in Products_summary_ProductCode, and get Product Code attribute. Create column that will lookupOne Products table matching Product Code column.
I don’t know if I explained it clearly enough… here is document:
https://docs.getgrist.com/fUFYaMmbYpgD/Prevent-Duplicates

2 Likes