How to Prevent Duplicate Records

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.


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.

Shared with CloudApp

What's happening in the background

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).

16 Likes

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

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:

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

11 Likes

Isn’t this true for most of the Tips! :wink:

7 Likes

That’s Really a good one!

1 Like

And when you want to get fancy… partyparrot (Appsheet)

4 Likes

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.

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

4 Likes

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! :tada:

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

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.

3 Likes

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().

Is there a question or issue you need help with?

2 Likes

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?

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

1 Like

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())))
3 Likes

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

1 Like

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

muchas gracias, me fue muy util

1 Like

De nada!