How to replace empty strings with null in table.recordUpdates

If you are using editable tables in a Retool app, you might notice that when you clear a value from a table cell, Retool interprets this as an empty string '' by default. However if you want that value to be null (or something else), you'll need to write a short transformer. Here's one JS snippet (using Lodash, which we include with all Retool instances!) to find these empty strings in my table.recordUpdates array and replace them with null values. This works for editing one or many rows at a time.

let tableData = {{table1.recordUpdates}};

// iterate through each row of recordUpdates
for (let i = 0; i < tableData.length; i++) {  
  // check each value in row object
  _.forOwn(tableData[i],function(value,key) {
  // replace empty strings with `null`
    if (value == "") {
      tableData[i][key] = null
    }
  })
}

return tableData

In the case of this example, I've cleared out the name value of this record and it comes up as an empty string '' in the table1.recordUpdates array.

Shared with Zight

But after I add the transformer, it changes that empty string value to null, since this is the value that I want to write back to my database in the bulk update query. So instead of using {{table1.recordUpdates}} in my query, I'll just use {{transformer1.value}}.

5 Likes

@alex Does this transformer happen before the recordUpdates property is set on the table? The use case I am trying to solve for is when an action button is clicked on a table row, I would like to update a different column on that same row.

Hey @atorres — thanks for posting and welcome to the Retool community!

Transformers run on page load and anytime their inputs (i.e. anything referenced with {{ }}) change. You can read more information about them in our docs here.

From what I understand, I think your use case is slightly different. I would recommend adding an update query and setting that as the query action for your table action button. This update query depends on the datasource — it could be a POST request or an Update an existing record query using the GUI Mode of an SQL query.

The details of your query would be specific to your context, but I would recommend referencing the data from the selected row of the table {{ table1.selectedRow.data.columnName }} to make sure your action button is updating the right record. Here's an example using a Postgres database:

1 Like