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.
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.
- because we want things to pass if the answer is FALSE to the IN() formula
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).
