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:
- Set the "On Save" feature of your table to be a new Javascript function.
- use the javascript below to convert all of the "" values of your changesetarray to be null.
- 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;