How to create sanity checks for input data?

[torwag]

Maybe I did not yet understand where or how to do it. I was trying to use trigger functions to create sanity checks on the newly added data. E.g. for a column which points to a reference list, I wanted to make sure that each entry can only appear once. I tried to use a trigger function for the column "name" and defined list(set($name)), Sure enough, this creates a circular dependency.

What I need is to access the newly added value to check if it already exists.

A sanity check function which, similar to a trigger function, will execute some python code to check if the newly arrived data passes some criteria and only if this comes up true, the data will be written to the cell.

[georgegevoian]

In trigger formulas, the value variable can be referenced, which contains the newly added value. That should get you past the circular reference.

Currently, it isn't possible to prevent a write to a cell with trigger formulas, but you can clean the data, return a default value, or clear the cell if the input doesn't pass your criteria. In this case, I think you can use the formula set(value) to remove duplicate references.

We've also had some discussions about supporting validations that prevent writes, as well as column-level uniqueness constraints, so it's definitely something we're thinking about.

[dsagal]

Does this help? https://community.getgrist.com/t/ensure-unique-values-or-detect-duplicates/76 It describes both a way to detect duplicates, and to prevent them using access rules.