Not admit duplicates in other column different for primary key column

Hi every one, I know that maybe a lot of you had past for this same situation.

I have a table call INS and the column I am trying to secure not have duplicates is [COD], the situation is that I use a code that apply perfect when is a New record, the probles is that this code make that when I change something in a already existing record recognice like de value in [COD] is duplicated.

NOT(IN([_THIS],SELECT(INS[COD], [COD] = [_ThisRow].[COD])))

That is the code in the Valid_if, I think that the solution is that this column have the same function of a Primary Key column

1 Like

Close, try this:

Not(In([_This], SELECT(INS[COD], [RecordID] <> [_ThisRow].[RecordID])))

The key thing here is the following:

  • [RecordID] <> [_ThisRow].[RecordID]

You’ll need to change the [RecordID] to whatever column is marked as they Key for your table.

What this formula is saying is:

  • Pull every record from the INS table, except for the record that’s open, and create a list of all the COD values;
    • then check that the value entered into the record that’s open IS NOT inside that list.