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