Hello, I'm new to Retool & SQL as a whole. I'd like to implement a simple username validation rule on a form. I have a retool database that has a column containing usernames. I also have a query that returns all distinct usernames from the column.
I can't seem to figure out what I need to put in the form field's Validation Rules>Custom Rule section that checks if the value entered already exists in the database/query. Below is what I had in mind but could not get working.
Yes I know not to store sensitive information in the Retool Database, I'm just trying to learn the program here and thought this would be easier than it has been! Any help is appreciated.
You can use the includes function which checks if self.value is in query4.data.name list. If it is, then it will return null (Which is a success for validation) or throw an "incorrrect" error message. @Dirtbag_Data
@Haseeb1399 Thank you for the help! I am still getting a ReferenceError saying that my queryname is not defined. I've double checked that I'm using the proper name. Do I need to adjust any settings on the Query or within the Form to access my queries?
Could you share the existing user ids? I think using the list method is incorrect. Try data or value instead.
Well prior to implementing either method I get the ReferenceError. It's not recognizing my Query object or the Retool Database either.
Please share how you are retrieving the data from retool database? If you could share a json of the app I can help you better.
I apologize, I am very new to this so I may not be fully understanding of what I'm doing. The first screenshot shows the query user_ids. It pulls from the table public_database in my Retool Database.
The second image here is what I can see when attempting to edit the form. Of note, this is not an "app", it was made using the "form" option if that makes any difference. I do not have any custom scripting going on other than the query shown above and the attempted custom rule shown below. You can see in red at the very bottom the ReferenceError I am getting.
It seems to me like the codespace for the custom rules does not have access to my database or query.
Ah, I just checked and I couldn't find the resource to import queries from in forms. It seems that queries can't be run on forms, (Which makes sense to me, you are collecting data, validation comes afterwards).
You could alternatively setup a workflow that:
Takes input --> Triggers a workflow that checks for consistency --> Notifies you on slack of any error.
1 Like
Thank you, I'm glad it wasn't just me struggling to find this information in the docs. I assume this also means that you can't reference the Retool Database from a form's custom rules area?
As I understand this, yes. I might be wrong since I haven't worked with forms that much but the variables we use to refer to the data storage are supposed to within the "app scope". So a query or trigger must be there to fetch the data before using it.
1 Like
Thank you for all the help.
