Duplicate Record Check - Fails on Edit?

[mykrobinson]

Yesterday i devised a method to check for duplicate records when entering new people. I created a field called _duplication_test and have the following as a Valid_If forumula:

NOT(IN([_THIS], (SELECT(Patients[_duplication_test],true))))

This works well when adding a new entry, but what i have found today is that it does not allow me to edit a pre-existing record because that check exists.

How do I get around this so where the validation only affects new records?

[Steve · Accepted solution]

NOT(
  IN(
    [_THIS],
    SELECT(
      Patients[_duplication_test],
      ([_THISROW].[_ROWNUMBER] <> [_ROWNUMBER])
    )
  )
)

[mykrobinson]

would you mind breaking down this expression a little bit? I understand until we get to row #6.

Thanks

[mykrobinson]

Actually, the output from the appsheet helper tool may be sufficient:

Note: This expression could impact performance.

This statement is false:
....(The value of column '_duplication_test') is one of the values in the list (The list of values of column '_duplication_test'
........from rows of table 'Patients'
........where this condition is true: ((The value of '_RowNumber' from the row referenced by 'patientID') is not equal to (The value of column '_RowNumber')))