How to check or ensure that a text field is unique within the column.
I have text field that user need to keyin a value , id like to prevent user to enter an existing value from that column.
Something like ;
SELECT CASE WHEN count(distinct col1)= count(col1)
THEN 'column values are unique' ELSE 'column values are NOT unique' END
FROM tbl_name;
Thanks!
Glide doesn’t provide any native mechanism for enforcing uniqueness. To do so, you need to create your own logic in the data editor.
The general approach is to use a multiple relation that matches the column in question to itself, and then check if the relation is empty or not.
The below (copyable) concept app demonstrates how this can be done
Thanks again Darren for quick respond and guidance, I was thinking that if Im just missing something basic functionality within glide. Will do make some internal cross validation for desired output.
Cheers!
i cant copy your app to see how its done, can you explain it more? all the question about this topic always linked to your answer but theres nothing to see
Probably it’s because Classic Apps are deprecated now. Can you let us know what questions you have?
im making a survey app with 100 people using it as a surveyor… they will collect citizenship ID that unique for each citizen. I wanna make sure that my team dont double collect the data. thats why i need to know how to enforce column value is unique.
A simple description of a custom form is that it’s just a single row working table, a detail screen with entry components to fill it with data, and a button to take thows values and write them to a new row in a different table.
Saying that, to check for duplicates you just need a relation or query in the working table that takes the user input and looks for matches in another table. If the relation finds a match, then it would be a duplicate if you add another row with the same values.
is there any template app i can use as example? or any youtube video explaining it?
It’s pretty easy to set up, so nobody has probably created an updated template recently.
At a very basic level, those are the fundamentals of a custom form.
To prevent duplicates, and in your case if you acquiring a citizen ID, you would have an entry component for the user to type that ID. The ID is immediately written to the user specific column in the working table. Create a relation linking the ID to the ID is the other table that contains existing citizen data. Add a visibility condition to the button to only display if the relation is empty.
That’s your custom form. There’s more to it, such as clearing values after submitting the form, adding other verifications and warnings and such, but that should get you started.
i still dont get the gits of it, i comes from appsheet so i am still new with all glide function.
If you follow the 4 bullet points above, where do you start to lose understanding?
ive done it and it works… the only thing know is i cant get the notification work when there is a duplicate input. i just can make the button goes hide. is there a solution for this?
You could add a Hint component that displays with a warning when the relation is not empty.
i found another problem using this method… the user still can input duplicate data when other user has been input the same citizen ID, This method just prevent the same user input the same data
NIK = Citizen ID
Looks like you have row owners in a table that should not have row owners, or you are not granting access to the rows that a user should have access to.
Notice the greyed out row in your table. That’s because the current user does not have access to that row, so they can’t properly check for duplicates.
i make row owner in user email so other surveyor cant see, edit or deleted others data. is there any solutions u can provide?
If you want to prevent a surveyor from entering a duplicate that another surveyor already entered, they need access to all rows. Otherwise the relation won’t know that they are entering a duplicate because their device doesn’t know the other row exists.
Are you using Row Owners to provide data security, or are you just using row owners to prevent one surveyor from touching another surveyor’s data? You can still restrict access to data with filters and conditions on the ability to edit or delete rows. You can do that without using row owners. It’s not as secure, but it would still work prevent users from viewing and touching data that is not theirs.
it was for security and speed, ive read that just using filter mean that every user will having all the other user data in theri phone, my app will collect around 20k of citizen data… isnt using filter will slow down the app in the future?
Yes, it’s true that if you don’t use row owners, that means more data will be downloaded to the device, so it could slow things down.
Computed columns, such as a Relation run locally on the device. It can only work with the data that’s available to it on the device.
I don’t know if I have a great solution. I think your options would be to do something with an API to run an external process that checks for duplicates and returns a value if it is. Might be slow though. Another option would be a process the does some cleanup after data has been entered and deletes any duplicates.
is theres a way to make cleaning duplicate value more manageable? it will take forever if i have to do it manually. im using only glide table in my app, because it eat all the updates if im using googlesheets… even it will make it more easy to clean up the data.