Duplicate check for user's input (as a list of string)

[Evendo]

Hi!

I've made a screen that allow user to register data into "Company" table.

Now I want to check the "TelNo" value that user inputted on the screen is already registered or not.

In a simple case, which is just a single telephone number is allowed to input into DB, I could simply use the below ValidIf formula for "TelNo" to check the duplicate input.

COUNT(SELECT(Company[ID], AND([TelNo] = [_THIS], [ID] <> [_THISROW].[ID]))) = 0

But in a more complex case, for example, user is allowed to input and register an array/string of telephone number, split/delimiter by comma, such as "03-9999-9999,084-999-9999" then how I could check whether or not any number inputting in the screen is already registered?

For example, when there already had a record with "TelNo" is "03-9999-9999,084-999-9999" in "Company" table, then if user input "084-999-9999" or "08-9888-9999,084-999-9999", it will be validated then display error message on the screen.

I know about INTERSECT and SPLIT formula, but I don't know how use them correctly to complete my requirement as above.

Please give me some advice. Thanks so much!

[Suvrutt_Gurjar · Accepted solution]

Assuming [TelNo] is an enumlist type column, please try a valid_if expression of 

ISBLANK(
FILTER(
"Company",
ISNOTBLANK(INTERSECT([_THIS], [TelNo]))
)
- LIST([_THISROW])
)

If [TelNo] is simply a text type column and the user simply enters telephone numbers in comma separated string format, then please try a valid_if expression of 

ISBLANK(
FILTER(
"Company",
ISNOTBLANK(INTERSECT(SPLIT([_THIS],","), SPLIT([TelNo],",")))
)
- LIST([_THISROW])
)

Please refer the last part of the article below on expression for avoiding duplicates.

List expressions - AppSheet Help

Also please note that in AppSheet, any such valid_if expression based duplicates avoiding mechanism may not work in multiuser environment , if two different users try to enter the same telephone number simultaneously. Of course, practically this may seldom happen but the possibility exists.