I have a table in my app which has two columns (Key/Value), one is populated with just plain text in the JavaScript array, the other column is populated with the results of a DB query. I need the column populated by the DB query to be editable and for those results to get updated in the DB when they are saved.
I have managed to get this working for a single item but the bulk update is causing me some issues. The problem is that the text in the Key column is not the same as the name of the column in the DB, so I need some way of mapping the value in the column to the column headings in the DB.
I'm sure this sort of thing must have come up before but I am at the edge of my JavaScript, SQL and Retool knowledge. I have included a picture of the type of table I am working with, hopefully, that clarifies rather than confuses.
You can change your column name in the table to banana and it would still be able to update the database correctly. Here's an example of a mySQL bulk update query and how you could set it up. The red shows the correct column name despite being name different on the table:
Then, you'll use .recordUpdates (purple) to get the rows that have been changed in your table. And finally, you'll add an event handler (green) on the table that triggers this update query when you save changes:
Thanks for the input! Unfortunately, in my use case, it's not the column name that relates to the DB field but the value in the column., think of it like a DB table displayed vertically rather than horizontally, so the DB field names are displayed down the left-hand side of the table rather than across the top row. So in the image, I attached I have a column value that says:
What CPU
In the DB, that field name is:
what_cpu
The value I want to be able to change in that field is AMD.
So using my image as an example the update SQL statement might look like:
UPDATE components SET (what_cpu = 'Intel', qty_of_ram = '128', what_gpu = 'AMD') WHERE....;
Hopefully, that makes the use case a little clearer.
Having just thought about it writing this out, perhaps there is a way of displaying a SQL table vertically instead of horizontally in Retool?
I think that would work great, so thanks very much for sharing this solution.
In order to progress my project forward, I changed the UI in order to use these values in a normal horizontal table. I am only working on a PoC at the moment so if the UI needs fixing when it gets moved into production it can be done then.
