Updating Multi-Tag Field in Table Failing

App Building
, ,
1

I am running the Web Retool on top of a MySQL 5.7.12 server.

I am using the table component to edit my data and have a specific field where I would like to have a multi-select dropdown menu so I am using the "tags" data format to provide these options.

So my problem is two-part:

  1. When only updating a single record that is a json field on the DB side, once data has been edited and entered it does not know how to read the data back into the table in order for it to show the tags and options that I selected. It consumes the data when done as a key value pair.

  2. When trying to do a bulk update and saving it the to DB the SQL query that is executed is not placing the multiple option tags in an array and breaks syntax into the MySQL DB.

Attached is a screen share of what I am seeing and my issues
https://www.loom.com/share/2c0429dd4f1d4d00a485fa8c9831e3a1

2

First problem, the data loaded back from the DB was formatted as a string instead of an array.
image
You need to convert the Column 1 data back to array.

Second problem probably have to manually format the data before doing bulk update.

3

@lenti Could you give a little bit more clarity? How do I alter the format of the data as it is pulled into Retool? Do I need to specify it in my query?

And I feel like the second problem, while I could do a workaround and manually tweak the records to bulk update, shouldn't it work out of the box with Retool? It seems like a bug to me.

4

You can make use of transformers. I have created a query that would return data similar to your DB return, and make use of the transformer to tweak the format.

2
21154×595 28.1 KB

data.map(item =>{
  item["Column 1"] = JSON.parse(item["Column 1"])
})
return data;

Not so sure about second problem being a bug or not, but yeah, you have to tweak the data before updating to database.

1 Like
5

Thanks for your solution for how to format data being passed to the table @lenti!

When looking to pass changes from the table back to the db for updates you might try tweaking the above as follows in a standalone transformer:

const data = {{ table.changesetArray }};
data.map(item =>{
  item["Column1"] = JSON.stringify(item["Column1"])
})
return data;

You can then pass {{ yourTransformerName.value }} in your update query.

If you want to skip the standalone transformer you can also try something the following directly in your update query:

{{ table2.changesetArray.map(row => Object.assign(row, {Column1: JSON.stringify(row.Column1)})) }}

1 Like