I currently have a similar need to validate a user entry with an arbitrary restriction such as duplication. What you could do is do the validation within the spreadsheet and once validated let the user “push” the validated username to the username field.
Unfortunately this would generally rule out using a form, as form values are not written to the spreadsheet until submitted and only write to new rows. However, what you could do is have a 2 step process:
User opens Change Username Page and is presented with a “Validate New Username” form button
User clicks the button and the form opens. The user enters their desired username into the only text field and hits submit
Their New Username is then logged in the “Username Request” sheet
An Excel formula then populates a few fields in the users ownered row (Latest New Name, Valid status)
User is automatically returned to Change Username Page (normal for forms)
Seconds later a component appears with the results of their validation a yes or a no and their chosen name
If yes, another new field will also be available: a choice component with only one possible value: the recently validated name (visibility based on the valid status)
The user now clicks the choice component and selects their validated name, which moves the validated name into the name column
Let me know if you would like more details as I have built and tested the above.
Would this method encounter a lag when you have to submit it back to the Sheets though? If you are using a form to validate it, then I would rather do a relation in that same “Validation” sheet as you have stated, to know if it matches an existing record or not. I think pushing it back to the Sheets is not ideal.
Furthermore, I think it will eat up a lot of rows for the validation and free users won’t like that. Kudos to the idea for the choice component.
The reason I pushed it back to the sheet for validation is that it offers more options. You can do any sort of validation you like.
The delay is there of course, anything that goes back to the sheet will have it, but its maybe 2sec, not too bad
There are no extra rows used for validation, only for the request submission, and if you are familiar with AppScripts, you could arguably delete the top X number of rows anytime the number of rows reaches some threshold. Also the form ensures that the value, once validated, cannot be changed by the user. This ensures the validation remains true.
The issue is that at any time, if the user is free to enter a value in a free text field, you cannot validate it. Using a pre validated value that is simply selected from a list, ensures the value is valid.
I will admit its not a perfect solution, but it is functional and fairly straight forward.
I think that is achievable with a user-specific column. Using your same method above, it would go like this:
User enters their new name using that user-specific entry.
A relation validates if that entry is not a duplication of any existing entries.
If the relation is empty, then show the choice component to let the user change it, with the only value being the value in the user-specific column. Otherwise don’t show the choice component and show a text that the name is not available.
In my experience, pushing it back to the sheets require more than 2 seconds to bring back the value to the Glide editor and then sync it to the device of the user, so many of us here have always tried to find some ways to do it inside the Editor instead, lagging troubleshoot is a concerned issue we have discussed many times here.
All in all, nice workaround using the Sheets, and if Raph wants to try the steps I listed in this comment, feel free to do so and let us know if you succeed.
If it’s possible I agree it could be quicker, however I also like to camel case their name and in general if this is to be used for any complex validation, the editor will likely not suffice. Though if duplication is the only criteria and things like first letter capitalisation are not required, then keeping it in the editor may also do the trick!
I agree with that, if we need further adjustments on the value, like what you say, camel case the name then we must bring it back to the Sheets.
It also brings me to the question that whether my relation goes the way of exact match or not (i.e Camel is not the same as cAmEl and won’t bring back a relation value ).
Hi, I’m struggling with exactly this right now! - I have such a relation column set up and it works fine if I directly enter the value in the glide table. But, naturally that’s not how the app will work - I want my ‘primary’ user to be able to add new (secondary) users - is there any way that I can assign the new user’s ID to a temporary variable? I have a column for the temporary variable which what the relationship checks the list of user IDs against. If I use the action ‘set value’ for some reason the value from the form isn’t getting captured in the table at all… what am I doing wrong?
If this helps, this is my form:
I thought I’d use the clunky manual check button to force a check that the ID is unique before proceeding to fill the whole form. But set column value > this row isn’t adding the value to the admin user’s column.
And, the second weird thing is that while my user ID column is user-specific, I can apparently still have a duplicate entry!
Also, you may be misunderstanding user specific columns. They don’t ensure that every value in the column is unique. What they do is hold a value uniquely, for each user that’s signed in. You see numbers in that column now, but if a different user signed in, then that entire ID column would be empty.
The purpose of a user specific column is so that multiple users can store a value in one single cell that’s unique to them. Nobody else will see what they entered.
A probably daft question, but can you please tell me how you neatly clubbed and labelled columns?! that’s so handy, I’d love to do that… is it a paid feature?
- I have such a relation column set up and it works fine if I directly enter the value in the glide table. But, naturally that’s not how the app will work - I want my ‘primary’ user to be able to add new (secondary) users - is there any way that I can assign the new user’s ID to a temporary variable? I have a column for the temporary variable which what the relationship checks the list of user IDs against. If I use the action ‘set value’ for some reason the value from the form isn’t getting captured in the table at all… what am I doing wrong?
