Updating SQL Table With Null Dates

,

Hey everyone,

So I had a big problem that I solved and I'd like to share the fix.

When you edit a table and push the changes for the table to a SQL table, like Retool Database, usually things go fine using the table1.changesetarray and update by primary key function with a table. However, when you're changing a date in the table to be null, you get an error:

Error: retool invalid input syntax for type date: "" on SQL upsert

This is because SQL date fields cannot accept "" which is what Retool tables create for dates when you delete them from a table.

However, what you can do to go around this problem is to convert those "" into null values. Then your SQL won't have a problem.

Steps:

  1. Set the "On Save" feature of your table to be a new Javascript function.
  2. use the javascript below to convert all of the "" values of your changesetarray to be null.
  3. do the bulk upsert function using the output.

Javascript:
function convertEmptyStringsToNull(jsonArray) {
return jsonArray.map(obj => {
Object.keys(obj).forEach(key => {
if (obj[key] === "") {
obj[key] = null;
}
});
return obj;
});
}

// Example usage
let changesetArray = table1.changesetArray;
let updatedArray = convertEmptyStringsToNull(changesetArray);
return updatedArray;

6 Likes