Not in this row with condition >1

AppSheet AppSheet Q&A
, , , , ,
1

Hi all, I hope everything is going well

I am writing a valid if expression on [customer mobile] column that checks if mobile number exists in the customer mobile column or not.

Valid expression and msg is here:

NOT(IN([_THIS], SELECT(Customers[Mobile Virtual],true))),
-----
Valid if warning message:
"<li-emoji id="lia_warning" title=":warning:"></li-emoji>This Could Be a Repeated Customer"

The problem is when I try to edit customer info row again it gives me a warning even the mobile number only exists once. Can I add something like >1 so that if the mobile number found more than one times in the mobile column then it gives a warning.

Thanks much

2

Add [key-column]<>[_THISROW] into your SELECT where you have the “true”.

3

Hi @Marc_Dillon

Did you mean like this

NOT(IN([_THIS], SELECT(Customers[Mobile Virtual]<>[_THISROW]))),

Now it says: Cannot compare List with Text in (Customers[Mobile Virtual] <> [_THISROW])

would you please write it here.

4

For your SELECT() function, you need to update the last condition, true, to contain your filter condition:

NOT(IN([_THIS], SELECT(Customers[Mobile Virtual],[key-column]<>[_THISROW])))

Replace key-column with the name of your key from the Customers table

5

The unique key column in that table is [ID]

So it will be

NOT(IN([_THIS], SELECT(Customers[Mobile Virtual],[ID]<>[_THISROW])))

Is it correct?

6

Looks like it. Let me know if if doesn’t work

7

Please see this..

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/valid-if-Not-able-to-edit-and-save-the-form/td-p/747638