We have a column in a Postgres database table that can hold one of three statuses:
"Approved"
"Rejected"
"Pending"
Records in the table are created outside of Retool and by default have the status of "Pending". Our Retool users can then update these to "Approved" or "Rejected", but we don't want them to be able to change an "Approved" or "Rejected" record back to "Pending".
In Retool, we show this data within an editable table column. If I remove "Pending" from the column's option list, then all items that have the value of "Pending" are changed to "select option" in the table.
Is there a way I can keep displaying the "Pending" records correctly without having "Pending" on the option list?
Great question! There is not a built-in way but we can totally do this with some JS!
From reading your question, it looks like you already have the editable column, and the query to update the row set up and working. I'll focus on creating a validation for the selection.
Your editable column probably looks something like this:
Here are my current settings for that column:
Note: We'll work on that event handler that I added to it.
For this example, we'll create a validation using a JS query to not allow the edit to make a user an Admin, but we'll allow updates for Viewer and Editor.
Here is the JS query:
Note: if a user selects Admin, we are raising a notification with the error message. In addition, we are refreshing the table to prevent our users from selecting more columns after the wrong selection. We are also refreshing the table to bring the value back to what it was before the attempt.
Finally, we can attach this JS query as an event handler for "Change cell" to the column that we would like to validate:
When our users try to select Admin for the role now, they'll see this error message:
Thanks for the reply and solution. That's mostly worked well for me.
The one thing I changed was not to automatically refresh the table upon the validation failing, because I don't want the user to lose any other valid unsaved changes they might have made to the table. Instead, I'll run this validation again upon the user clicking save, and then only allow the save action to run if the validation passes.
