Validate by repeated Serial Number

[Marcos1]

Hello Comunity
It’s there a way to prevent a user from adding an item with a Serial Number that already exists in another row?
The idea is when adding a new item to an existing list, in a specified column, verified it that number or text or serial number already exists and show an Information that “It already exists, please edit that one”

Thank you so much

[Suvrutt_Gurjar]

Hi @Marcos,

Are you essentially looking at avoiding duplicates for various fields? If so , then please look at the following article under section " Preventing Duplicate Field Values"

help.appsheet.com

List Expressions and Aggregates

List expressions use operators that result in a list or numeric value.

[Aleksi]

Please also remember that if two or more users are using the app at the same time, duplicates will happen.

[Marcos1]

Hi @Suvrutt_Gurjar. I’m only looking to avoid duplicates in a specified Column.
Hi @Aleksi. I have 8 users using the App at the same time, but im using a unike id per row, created by Appsheet to prevent this at 100%

[Aleksi]

It doesn’t matter if you want to avoid either duplicate record or duplicate value in a specific list, situation is the same because the data will be the same for both users.

[LeventK]

You can only avoid duplicates against the existing records in the table already. You cannot avoid duplicates when multiple users are trying the create records at the same time as they haven’t synced their data in the back-end yet.

[Marcos1]

Ok @LeventK, it makes all the sense. So how can I avoid duplicates in a specified Column that already exist in the sheet?

[Suvrutt_Gurjar]

Hi @Marcos,

Did you get a chance to take a look at the referred article earlier and the section " Preventing Duplicate Field Values" therein.

The article also gives an example of how to use the given expression for a column called [State ]to be unique.

[Marcos1]

Suvrutt_Gurjar:

Preventing Duplicate Field Values

Yes, but cant understand. I Have a column named SERIAL NUMBER.
I have to go to de DATA VALIDITY, and in the filed VALID IF and insert [_THISROW].[CustomerId]?

[Suvrutt_Gurjar · Accepted solution]

Hi @Marcos,

Yes the expression needs to be put in the Valid_if section of the column that you do not wish to be duplicated.

In general, the expression should look like below

NOT(IN([_THIS], SELECT(Your Table Name[SERIAL NUMBER], NOT(IN([Key column name of your table], LIST([_THISROW].[Key column name of your table])))))),

[Marcos1]

Also tried:
[_THISROW].[serial number]
NOT(IN([_THIS], SELECT(inventory[serial number], NOT(IN([serial number], LIST([_THISROW].[serial number]))))))

I have a Table called INVENTORY and a Column named SERIAL NUMBER

[Marcos1]

Should be like this then:
NOT(IN([_THIS], SELECT(inventory[SERIAL NUMBER], NOT(IN([UNIKE KEY], LIST([_THISROW].[UNIKE KEY])))))),

but its returning this error
This statement is false:
…(The value of column ‘SERIAL NUMBER’) is one of the values in the list (The list of values of column ‘SERIAL NUMBER’
…from rows of table ‘INVENTORY’
…where this condition is true: (This statement is false:
…(The value of column ‘UNIKE KEY’) is one of the values in the list (LIST(
…The value of ‘UNIKE KEY’ from the row referenced by ‘UNIKE KEY’))))

[Suvrutt_Gurjar]

Hi @Marcos

Sounds like the app editor has evaluated that expression and it is Ok. The expression will evaluate to false or true.
Do you see the tick mark in green circle in expression evaluation, then in general your expression at least syntactically is OK.

[Marcos1]

Yes, it returns a Green tick and its working fine!
Thank you so much @Suvrutt_Gurjar
And thanks to the entire App sheet community !

[Aleksi]

Just want to be sure you have understood this correctly. The formula Suvrutt proposed, will work in generally. But if two users are opening the form at the same time, the validation will give the same result TRUE if that number is not in the list. They can save the record and then you will have the same number twice. If this is critical in your app, you should think what other possibilities you could have.

The reason is quite simple… both users have the same data in their device when opening the form view.

[Suvrutt_Gurjar]

You are very right @Aleksi. After your and @LeventK 's guidance on practical limitations of use of formula in multi user environment, @Marcos mentioned that “it make sense”. So I presume @Marcos has understood the limitations and continued the thread. You are right in reiterating the limitations, as impact can be substantial.

Hi @Marcos, please use the formula keeping limitations in mind as mentioned by senior community colleagues @Aleksi and @LeventK.

[Aleksi]

@Suvrutt_Gurjar Just wanted to be 100% sure

[Suvrutt_Gurjar]

Hi @Aleksi, I absolutely understand. Your judgement and guidance has always been invaluable in the community as we have experienced it innumerable times.

[Marcos1]

I understand perfectly your concerns that make perfect sense, but I don’t think that I’m going to have that Kind of problems in the future.
There’s no way of 2 different persons add the same serial number at the same time in the App.

Thank you so much for your attention to detail and feedback Guys
I’m amazed for the way this community “spreads” knowledge!

[Aleksi]

You’re welcome