How to Prevent Duplicate Records

[MultiTech]

A very common type of validation you’ll want to add to your app is the ability to restrict people from entering duplicate information.

Such as not being able to add a contact into the “Contacts” table if they already exists; same with Products, Clients, Users, VehicleIDs, What-Have-Yous.

---

Preventing duplicates in this way is very straight-forward in AppSheet; all you need is a validation formula in the Valid If space for the column you want to prevent duplicates on.

Not(In([_THIS], SELECT(Table[Column_Name_for_THIS_Column], [TableID] <> [_ThisRow].[TableID])))

• [Column_Name_for_THIS_Column] needs to be the name of the column for the one you’re trying to validate.
  • i.e. If I was trying to prevent a duplicate User_Name, I could put [User_Name] there - because that is the column that I’m putting this validation formula on.
  • If I didn’t want the same license plate number entered twice, and I was putting this formula inside the Valid If formula space for the field that records the license plate numbers, I would use [Vehicle_License_Place] or whatever the name of that field is.

What you’re really accomplishing with this formula is you’re pulling a list of all the values from [Column_Name_for_THIS_Column], then you’re seeing if the value entered by the user (that’s the [_THIS] part of the formula) is inside that list; but you’re also removing from that list the very record you’re working on.

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.

---

Example

Let’s say I have a Users table with the following properties:

• [User_Name] to collect the names
• [UserID] to generate a UNIQUEID() for each user (this is marked as the KEY for this table)

If I wanted to prevent duplicate user names, I would put the following formula inside the Valid If formula space for the User_Name column.

NOT(IN([_THIS], SELECT(Users[User_Name], [UserID] <> [_ThisRow].[UserID])))

This would prevent users from being able to enter the same name twice, but still allow them to enter it once.

What's happening in the background (click to open)

---

When this formula processes, what it does is:

• Generate that list I was talking about (of all the [User_Name] values)
  • while excluding the record that we’ve got open in the form.
• It then see’s if the value entered by the user is inside that list
• Then we reverse that logic
  • because we want things to pass if the answer is FALSE to the IN() formula
    • meaning that the value entered by the user is NOT inside that list
  • the answer to that actual formula will be false - but we need it to produce a true value, in order to pass validation - that’s why we put the NOT() on there.

If I were to give you an example of this (continuing from the GIF above), the list looks like this:

Matt V, MultiTech Visions

If I came into the form and tried to enter “Matt V” again:

• the system sees we’ve already got that inside the list (producing a TRUE value for the IN() statement);
  • but since we need a false value in this instance, we’ve wrapped the whole thing in a NOT() (reversing the logic).

---

[GreenFlux]

MultiTech_Visions:

Preventing duplicates in this way is very straight-forward in AppSheet;

Great tip, but I have to disagree with this part!
If it were straight-forward, we wouldn’t need this post.

This is a very common validation type that users want, but it is not straight-forward for new users. There should be a simple on/off toggle to enable unique validation on any column.

I submitted a feature request for this a while ago:

Column Valid_If Toggle Button for 'Require Unique Value' Feature Requests

It’s a fairly common need for developers to validate user input as a unique value for a given column. This is a little tricky in AppSheet, but @Suvrutt_Gurjar has posted a great solution: https://community.appsheet.com/t/valid-if-what-formula-to-use-to-only-allow-unique-values/12995/4 However, that’s not the easiest thing for a new user to figure out. Could we get a toggle button to require unique values? [Screen Shot 2020-04-30 at 7.47.51 AM] I’ve found myself going back to the same post …

Definitely a helpful tip until there’s a built-in feature for this, though. Thanks, @MultiTech_Visions!

[WillowMobileSys]

GreenFlux:

Definitely a helpful tip until there’s a built-in feature for this, though.

Isn’t this true for most of the Tips!

[Rifad]

That’s Really a good one!

[elco]

GreenFlux:

This is a very common validation type that users want, but it is not straight-forward for new users. There should be a simple on/off toggle to enable unique validation on any column.

Yes please, this would be so easy to make. It would take ages to add this formula for each unique constraint column in your app.

Still thanks for sharing this @MultiTech_Visions so we don’t need to figure it out!

[MultiTech]

And when you want to get fancy…

How To Show a Custom Valid If Error Message Tips & Tricks 💡

You’ve been working long and hard to get your Valid If formula correct, and finally it’s doing what it’s supposed to - preventing duplicates, wrong info, bad dates, etc. from being entered by your users. But now you want to go a step further and provide the user with some info about WHY the data they entered is invalid. This is where the “Invalid value error” field comes into play. [Shared with CloudApp] Utilizing this features is extremely easy, due to the fact that most the hard work has …

[sunil_prajapati]

how I can rewrite this expression for no duplicate value in the current date. I can prevent this with a unique id and concatenate[userid]&[date] but I want a dropdown of the remain list that will be best to see how many users remain in the current date by dropdown list.

[Jake_Naude]

MultiTech_Visions:

Preventing duplicates in this way is very straight-forward in AppSheet

Thanks @MultiTech_Visions… Great tip… Saved to favourites… Cheers…

[Skip2MiLu]

Hi @MultiTech_Visions

I have been searching in the community for a way to exclude a value from a drop down if it was already added to the table…and came across this post.

It does not solve my problem, but it is pretty close.

Any direction on how I can exclude a reference value from a drop down list, if it was already added.

Please take a look at my post below.

Any feedback would be much appreciated

Sequential numbering based on the "uniqueness" of two columns in the row Questions

@Marc_Dillon I wondering if you can help me on another expression I struggling with. So I have the following expression used for a virtual column that I set as a key column. This is to ensure a user does not accidentally add the same exercise to the same workout. [image] [image] So a user will get the following error message that will inhibite duplicates as shown in the below example where the user selects “Bench Pushups” again under the “2.Chest” Workout Plan [image] I am however wa…

[WillowMobileSys]

My understanding is that you do not want a value to show in a dropdown list if it has already been used.

There is a standard pattern in the Valid_If you can follow to achieve this.

In english, “Select the ref value if it is NOT in the used list”

In sudo-expression format it would be something like this:

SELECT(Ref Table[Ref ID], NOT(IN([Ref ID, SELECT(List Table[Ref ID], TRUE))))

Depending on your use case, you may need to add to the selection criteria but the basic format should remain the same.

[Auditor_La_Conq]

muchas gracias, me fue muy util

[WillowMobileSys]

De nada!

[yumei]

The formula works great. I'd like to know if it is possible to allow users to overwrite it and enter the duplicate entry anyway--how do I do it?

[Steve]

It is not possible for a user to force the creation of a new row with a duplicate key column value using a form view, since the system itself prohibits the duplicate key. An action, however, can add a new row with a duplicate key column value. Both rows will exist until a sync occurs, at which point the original will be removed.

[lala_land]

Hi, I am doing a Check In App for staff. I want to prevent the user from submitting the form more than once a day. Logically, the staff can only check in once a day.
I have the following columns: Date, Staff ID, Name, Staff Email Address, Check In Status in that particular Check In view.
However, the Name, Staff ID are populated based on the useremail().

[WillowMobileSys]

Is there a question or issue you need help with?

[lala_land]

Yes, currently I have a Check In view (view type: form). I want to prevent my user from checking in more than one time in a day.
Based on the sharing above, I have tried to write some expression under Valid If.

However, when I tried to check in multiple times, the field for Staff ID did not turn invalid. It supposed to prompt me saying that the Staff ID field is invalid when I tried to submit the form.
Can you please advise me on how I can change my expression to make that happen?

[WillowMobileSys]

I think the negative logic (NOT(IN)) is tripping you up. Your Select statement will never return the current rows’ Staff ID because of the “Staff ID <>” part so the end result is the Staff ID will never be in the returned list to test if it is valid or not. And because of that, it will always be Valid - i.e. not in the returned list.

Basically, you want ALL Staff ID’s returned for TODAY so you can properly test if the current ID is present or not.

Adjust your statement by removing the Staff ID criteria like so:

NOT(IN([_THIS], SELECT(Staff Attendance[Staff ID], [Date] = TODAY())))

[lala_land]

Thank you so much. It is working well now. You are right about the negative logic.

[wsaico]

Friend @WillowMobileSystems , your contribution helped me a lot, I take the opportunity to consult you, how would be the formula to edit and save again with this restriction?

I am using this formula but when I want to edit it no longer allows me to save the form

[AGairAdmin]

@wsaico  id you figure it our or get any help with this?   I have run into the same issue and posted about it but no response. 

[Lynn]

Hi @lala_land
What do you see when you click on the “Test” button, what are the results?

[lala_land]

This is the result that I got with the unworkable expression that I have wrote.

[poltergeist]

sorry for my English.

The formula works great but ......
What to do when I want to edit existing data? When I want to save the edited data, I get information that "data exists".

This is my formula:

NOT(IN([_THIS],SELECT(skazani[sygnatura], [_THISROW].[sygnatura]=[sygnatura])))

Hope this is understandable 🙂

[dbaum]

Your expression’s SELECT function returns the current row, which of course includes that row’s value. See the original post’s criteria within the SELECT function that use the following, which returns all the rows except the current row:

• the table’s key (ID) column
• the “does not equal” (<>) operator

[matt_dev]

I'm making a smartphone stock control app, they are identified by the IMEI number which is a unique number, I need to validate an output to avoid duplicate output. To register the output of these smartphones I use two tables, one that registers the output and one that registers the smartphone, that way I can output multiple smartphones in a single output record.

That's where my problem begins, I can't find or develop an expression that can prevent a duplicate item, I even used the expression:

NOT(IN([_THIS], SELECT(ESTOQUE 9000_SAIDAS_ITENS[IMEI 1], [ITEM ID] <> [_ThisRow].[ITEM ID])))

, but in this way it is not possible to output again on the smartphone, I need that for [SAIDA ID] the [IMEI 1] is always different.

[MultiTech]

@matt_dev

There's a bug with this validation formula when processed through the app editor currently.

• The validation formula works in the actual app, just inside the app editor's app preview does the validation formula not work.

Take Heart
  - Your formula works... it's just a bug

[matt_dev]

It works however not as I would like, in this formula NOT(IN([_THIS], SELECT(ESTOQUE 9000_SAIDAS_ITENS[IMEI 1], [ITEM ID] <> [_ThisRow].[ITEM ID]))) the Item represented by IMEI 1 never more can be entered, so I tried the formula: NOT(IN([_THIS], SELECT(STOCK 9000_OUTPUT_ITENS[IMEI 1], [OUTPUT ID] <> [_ThisRow].[OUTPUT ID]))) which to my surprise worked exactly like the first one, I don't know where I'm going wrong.

I need the value of IMEI 1 not to be repeated for SAIDA ID

[Jared12345]

@MultiTech  Hey Matt, unfortunately, this expression isn't working in my desktop version of the app either.

Can you check and see if you are getting the same issue?

<Image with PII is removed by staff>

[jagan999]

This would work for enforcing duplicate checks for a single column. What if I want to enforce uniqueness across a combination of columns? In my case, I have a unique transaction ID for every transaction but want to enforce uniqueness across Transaction date, amount, account name and expense category, in order to ensure users do not enter the same combination again. Any tips for this, please?

[fvision]

Explain    [tableid] if the name of the table dont work??

[dbaum]

What's meant by [TableID] is the column that is designated as the table's key.

[Sirfyaad]

Hi Everyone @MultiTech @dbaum 

My expression is not working here. I want that if [get entry no] equals then [get entry no] then no entry. But when I edit it, it gets edited. 

NOT(IN([_THIS], SELECT(SCAN SHEET [GATE ENTRY NO], [GATE ENTRY NO] <> [_THISROW].[GATE ENTRY NO])))

Valid if Error Message

IF(NOT(IN([_THIS], SELECT(SCAN SHEET [GATE ENTRY NO], [GATE ENTRY NO] <> [_THISROW].[GATE ENTRY NO]))), "Duplicate gate entry number found", "")

[MultiTech]

---

@Sirfyaad wrote:

NOT(IN([_THIS], SELECT(SCAN SHEET [GATE ENTRY NO], [GATE ENTRY NO] <> [_THISROW].[GATE ENTRY NO])))

---

There's a problem with the logic here, you're essentially saying in your SELECT():

• Go to the SCAN SHEET and get me all the "Numbers" where
• the Number doesn't not equal the number I've selected

So no matter what... you're never going to get your number.

• Go to the sheet and get all the numbers, where if the number is 5 (because that's the number I've selected in my form) then don't show me that number.

------------------------------------------------------------------------------------------

The solution would be to separate out the "Number" we're looking for, and the "ID" of the records.

• Create a column in your "Scan Sheet" table to ID each record
  • Initial value: UniqueID()
  • Visibility: hidden
• Then your updated formula would be more like what's presented in this thread

NOT(IN(
  [_THIS], 
  SELECT(SCAN SHEET[GATE ENTRY NO], 
    [Scan_Sheet_ID] <> [_THISROW].[Scan_Sheet_ID]
  )
))

[nicolascabello]

please help, i did precisely what is shown here but it doesnt work. This is my table called Listado: 

ID_Concursante	Nombres	Apellido Paterno	Apellido Materno	RUT
2fa26875	segewg	gweg	wegewg	12123123-4

I have this valid if in RUT column: Not(In([_THIS], SELECT(Listado[RUT], [ID_Concursante] <> [_ThisRow].[ID_Concursante])))

But still i am able to enter the same RUT value 12123123-4 

[Tiger1]

So I just used this and it worked for me:

NOT(IN([Inventory_Item_ID], SELECT(Pump AI TABLE[Inventory ID],[_THISROW].[Inventory_Item_ID] <> [Inventory ID])))

OR like below with your info/data

NOT(IN([Item in Table A], SELECT(Table B[Item to check table B],[_THISROW].[Item in Table A] <> [Item in Table B])))

[nicolascabello]

dumb mistake, i had filter out all existing rows, sos they were non existent 

[gqueiroz]

Boa Noite Pessoal tenho apenas um campo no meu banco de dados no googlesheets que consta o contrato gostaria de impedir somente o lançamento duplicado referente a este campo como ficaria a formula e onde devo inclui-la

[kunasorn]

well I tried just below formula and it seems to be working.

NOT(IN([_THIS],thistable[thiscolumn]))

Would there be any problem if I'm using this?

[Saddam]

@MultiTech @kunasorn 

Yes! there will be a problem. You can't edit/update this this entry. 

so this is not correct formula. I wrote below the correct formula:

ISBLANK(SELECT(Assets[ID],
AND(
[_RowNumber]<[_THISROW].[_RowNumber],
[ID]=[_THISROW].[ID]
)))

or 

IN([_THISROW].[ID],(SELECT(Assets[ID],
AND(
[_RowNumber]<[_THISROW].[_RowNumber],
[ID]=[_THISROW].[ID]
)))=FALSE

Thanks

Saddam