Update database fields/columns based on the entered value by the users for other fields/columns

Queries and Resources
1

I am presenting my users with a table that displays some columns of the database.
I have editable status field that I want the users to be able to update. The status field can be "Active", "New", "In-Progress", "Completed" ..etc

For each of these statuses, we have a selected on datetime field in the database, for example "status_active_selected_on", "status_new_selected_on" and so on.

When the user selects a status, I want to update the status in the database and set/update the status selected on field accordingly. For example, If they selected "In-Progress", I wanna update the "status" field to "In-Progress", as well as set the current date as the value of "status_in_progress_selected_on" and so on. Updating the status is not the issue, updating the "status_selected_on" is.

The list of the statuses is longer than what I provided in this example, about 13 statuses, so 13 "status_{status}_selected_on". And since we can't write raw SQL queries to write to the database, what is the easiest way to do this?

Thanks!

2

Hi, welcome to the forums.

Broadly speaking when you design these kind of audit columns the "field X changed on" value is usually best handled at the database level with a trigger.

It is possible to do what you're asking in Retool but it will be cumbersome as the SQL injection protection that Retool provides won't easily allow you to dynamically name the columns you want to update.

What you may want to consider is adding the database trigger, or, having a separate audit table that has columns for "record id changed, status changed to, status changed on, status changed by" and inserting that data from Retool when they make a change. Your select query could join this information so it's still visible in your table etc.

1 Like
3

thanks! @dcartlidge