Sample validation script before writing data


I'm a new Retool User and have enjoyed learning the application over the past few weeks.

My background is in Oracle database development. Retool has been helpful in allowing me to design a database schema and create the front end.

The table display (across multiple tables), bulk updates, form inserts and editing have been great.

I do have a question about validation. Currently I have one table that is pulled from a SQL query. I've adjusted certain columns so that the fields are editable.

What would the suggested method be for validating the fields before they are updated? For example, I would like to the amount of an invoice. I would like to ensure the amount is > 0 and that the status column is in "Draft" status.

I don't think there is native validation in the Retool app to do this (yet). So would I do this via a SQL script (perhaps a cursor)? Or do I go the Javascript route? (I noticed that the Javascript option notes "trigger other queries and write back data".)

If it's Javascript, would you have any examples I could take a look at and adapt?

One more question - I noticed if I create a BulkUpdate query, one option is Bulk Update via Primary Key. Is the Primary key restricted to one column? Or can I use a composite primary key (based on two columns)?

Much appreciated!

Hi @PL13 :wave: thanks for reaching out with this!

As you noted, the best approach to implementing custom validation logic will likely involve utilizing custom JS queries before actually triggering the related database query.

Regarding the composite primary key being used in bulk updates: as you've observed, the GUI for SQL bulk updates only allows the use of a single column primary key. However, you could consider executing arbitrary SQL that utilizes a compound primary key via out editor; my colleague, Justin, outlines this approach in this related community post – do you think that that would suit your use case?

1 Like