Error in Vaild if function when avoiding duplicate

[rishabh3vs]

I'm currently facing an issue with the Valid If condition in my AppSheet app.

Objective: I have a table in my app called "Client List," and I'm trying to enforce uniqueness for the "Contact No." column. I want to ensure that the contact number is 10 digits long and that it's unique across all rows, excluding the currently edited row.

I tried following codes

Code1

AND( LEN([Contact No.]) = 10,
         ISBLANK(
                  FILTER ("Client List", AND(
                                               [Contact No.] = [_THIS],
                                                 NOT([_ROWNUMBER] = [_THISROW].[Client Name])))))

Code 2
AND( LEN([Contact No.]) = 10,
            ISBLANK( FILTER(Client List, ([_THIS] = [Contact No.])) - LIST([_THISROW])))

Issue:  The formula seems to work initially, but I encounter an error when trying to edit an existing row. It appears that the current row is not being excluded properly during the validation check.

Additional Details:

• I do have a key column named "[Client Name]" in my "Client List" table.
• I've tested the formula, and it works as expected for new entries but not during the editing process.

 Question:

1. Can anyone spot any issues in the provided formula?
2. Is there a better approach to achieving uniqueness for the "Contact No." column while editing existing rows?

I appreciate any insights or suggestions the community can provide. Thank you in advance for your help!

[rbucsis]

If Contact No. is the key, then it should automatically validate that it is unique, and you just need the Data Validation that LEN([Contact No.]) = 10

If you really need this level of validation, then you should be able to use this

AND(
LEN([Contact No.]) = 10,
NOT(IN([_THISROW].[Contact No.], (Client List[Contact No.] - LIST([_THISROW].[Contact No.]))))
)

That will validate the length is 10 and that the Contact No. is not in the list of Contact No. while taking out the current row from that list. This would also be more performant because it's not using a select or filter statement.