How I got here:
I am processing reports that arrive in excel via a Retool Workflow. That workflow data is then loaded into a table in my app for further updates / processing. Related Topic
Goal:
Save all data from the app table and any edits / updates made by the user into a retool database table using the IDs for tag (mapped) columns instead of the labels that the user sees.
Steps:
User selects an Excel file to import / process
The file is uploaded to Retool storage
My workflow parses the data in the spreadsheet, returns it, and it is loaded into a table in my app
User updates fields in the table
User clicks a Save button and ALL rows in the table with their changes are saved to a retool database table using the IDs for the various tag formatted and mapped columns
Details:
There are a couple things about this that are a little unique (or maybe not? ). First, I need every row in the table to get added to the retool database table, regardless of whether or not anything has changed. If any column HAS changed, I need the updated value inserted.
I am very familiar with writing data to tables from Retool but where I'm a bit stumped is how to get the IDs for the tag columns and not the labels (my retool database table has the ID columns). I am pretty sure this is going to take a transformer / custom mapping via JS type of solution to manage both the original data and the changesetarray but I'm just not having any luck getting that working on my own. I am open to whatever suggestions you might have; including breaking this up into a couple steps if necessary.
Screenshots:
The table in question with a single row updated
The tag mapping for the updated column (Product Type). I have the id I need in the caption field because that is the only way the column will reliably match against the data from the workflow. I have several columns in this table setup the same way. This is a data issue, not a Retool issue; this has worked for me before:
I would create a transformer to format the data. Add a similar JavaScript transformer in Retool to generate the final dataset before inserting into the database.
const originalData = table.data; // Original table data
const changes = table.changesetArray || []; // Only contains edited rows
// Convert changeset into an object for quick lookup
const changesMap = changes.reduce((acc, row) => {
acc[row.id] = row; // Using the row ID as the key
return acc;
}, {});
// Process all rows, replacing tag-mapped columns with their ID values
const finalData = originalData.map(row => {
const updatedRow = changesMap[row.id] || row; // Use edited row if available
return {
id: updatedRow.id, // Keep the existing row ID
period: updatedRow.period,
product_type_id: updatedRow.productType?.caption || null, // Use ID from caption
model: updatedRow.model,
brand: updatedRow.brand,
licensed_odm: updatedRow.licensedODM,
licensed_ic_distributor: updatedRow.licensedICDistributor,
ic_sku: updatedRow.icSKU,
decode_cert: updatedRow.decodeCert
};
});
// Return the final transformed array
return finalData;
Then you just need to have an upsert query linked to your table and the transformer data.
One thing I can never keep straight is when I need handlbars and when I don't. In your example, you don't have them around the initial const statements but until I add them I don't get anything back in the preview. I'm working my way through the mapped columns to be sure I've got all the right values lined up. Dealing with spaces in column names is also fun... Thanks again for the help; I'll report back here once I've had a chance run this in full.
Yes! The famous curly braces {{}}. You are correct, I should have used them
eg const originalData = {{ table1.data}} as it is a Transformer. I believe in general we use them outside of JS Queries and Event Handlers when we choose Runs script.
In a Transformer: const originalData = {{ table1.data}}
In JS areas such as JS Queries and Event Handlers const originalData = table.data
Also in any component fields such as a table you can use JS when you wrap it in {{}}.
Hmm, for column names and table names (when you can control this) it is a best practice to use snake_case for Retool which is PostgreSQL under the hood. Unless you just mean the labels though I think Retool by default does a good job converting your table snake-case columns to Capitalize Each Word.
So of course removing the spaces from column names has turned into a whole other rat's nest of data cleanup. le sigh. I am still working on this; will report back once I have the data formatted correctly.
I'm getting some different behavior now but it's inconsistent and I'm sure something I'm missing in the JS function. Sometimes, the data comes back from the function as undefined:
Sometimes it will come back from the function with the right text values, but not the mapped IDs (captions) for the columns:
I did go through and fix all my columns to remove spaces, etc. It is matching values for the mapped columns in the table appropriately. Here is the current javascript function
const originalData = {{tblRptLines.data}}; // Original table data
const changes = {{tblRptLines.changesetArray}}|| []; // Only contains edited rows
// Convert changeset into an object for quick lookup
const changesMap = changes.reduce((acc, row) => {
acc[row.id] = row; // Using the row ID as the key
return acc;
}, {});
// Process all rows, replacing tag-mapped columns with their ID values
const finalData = originalData.map(row => {
const updatedRow = changesMap[row.id] || row; // Use edited row if available
return {
id: updatedRow.id, // Keep the existing row ID
period: updatedRow.period,
product_type_id: updatedRow.product_type?.caption || null, // Use ID from caption
model_number: updatedRow.model_number?.caption,
model_name: updatedRow.model_name,
brand: updatedRow.brand?.caption,
company_odm: updatedRow.company_odm,
component_sku: updatedRow.component_sku,
company_ic: updatedRow.company_ic,
ic_sku: updatedRow.ic_sku,
decode_cert: updatedRow.decode_cert,
company_shipto: updatedRow.company_shipto,
country_shipto: updatedRow.country_shipto,
quantity: updatedRow.qty
};
});
// Return the final transformed array
return finalData;
). First, I need every row in the table to get added to the retool database table, regardless of whether or not anything has changed. If any column HAS changed, I need the updated value inserted.
In your example, you don't have them around the initial 
