Apply Valid_if to NEW records, Only?

[Eric_Schwartz]

Hello,
A valid_if is set to test for uniqueness of a field value, ie no other record in the table has the same value.
The test works correctly for new records.
Editing an existing record throws the valid_if error.
How can we apply Valid_if to NEW records, Only?

[Bellave_Jayaram]

Not sure, but you could try this in the Valid_If:

IF(IN([Column], SELECT(TableName[Column], TRUE)), TRUE, <>)

[Steve · Accepted solution]

You probably want to apply Valid_If to existing records, too, in case the user changes the existing value.

NOT(
  IN(
    [MyColumn],
    SELECT(
      MyTable[MyColumn],
      ([KeyColumn] <> [_THISROW].[KeyColumn])
    )
  )
)

1. SELECT(MyTable[MyColumn], ...) gathers MyColumn column values in the MyTable table from rows that match the given criteria.

2. ([KeyColumn] <> [_THISROW].[KeyColumn]) matches only rows where the row’s KeyColumn column value does not match the same column’s value of the row in the form. In effect, this condition matches all rows that aren’t the row in the form, and so the SELECT() then returns a list of all MyColumn values from rows that aren’t this row. “What are the values in other rows?”

3. IN([MyColumn], ...) attempts to locate the form’s MyColumn value in the list gathered by SELECT(). “Does this value occur in any other rows?”

4. NOT(...) inverts the answer given by IN(). “Does this value not occur in other rows?”

[Eric_Schwartz]

Thanks Steve, Elegant and working!
Thanks as well Bellave for wading in to help.