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

11 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!

7 Likes

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

6 Likes

That’s Really a good one!

1 Like

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

3 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