In the shipping schedule app I am building, it is sometimes necessary to change a shipping date. This was handled by putting a note in the original record that the shipping date was being moved and then adding another record which is a copy of the original record with the new shipping date.
When a change is made to the table and the "save changes" button is clicked, is there a way to tell if the shipping date has been changed and if so, is there a way to update the current row with an automatic note saying the shipping date has been changed, and add a new record that has the new shipping date?
The .changeSet property on your table contains only those cells which actually have a change so I imagine that's a good place to start. At the moment you write back to your DB you could check if that property exists for any rows and then modify them/add new rows.
Out of curiosity:
Do you already have a workflow in mind for how you'd create the new row?
Would the old row still get the other updates besides being marked as having a new shipping date?
Alright, I think the basis of this is that you'll want an update query and a query that appends new rows. Roughly, you can write all the handling of how you want to recognize the new shipping date in a JS query with something like this:
const changeSet = yourTable.changeSet;
const currentData = yourTable.data;
const rowUpdates = [];
const newRows = [];
// the .changeSet property is indexed with i in a way that matches your underlying table data
// so you can pull i from changeSet and access the corresponding row with yourTable.data[i]
for(let i in changeSet){
if(changeSet[i].shippingDate){
//overwrite column that indicates new shipping date
const updatedRow = {...currentData[i], hasNewShippingDate: true};
//generate a new id, using the uuid library here but it should depend on how you're currently generating ids
const id = uuid.v4();
//build new row with full changeset
const newRow = {...currentData[i], ...changeSet[i], id};
//add both an update and a new row
rowUpdates.push(updatedRow);
newRows.push(newRow);
} else {
//build updated row with full changeset
const updatedRow = {...currentData[i], ...changeSet[i]};
//only add an update
rowUpdates.push(updatedRow);
}
}
//two separate queries - one to handle updates and one to handle inserts since sheets doesn't support upserts
bulkUpdateQuery.trigger({additionalScope: {rowUpdates}});
appendRowsQuery.trigger({additionalScope: {newRows}});
Let me know if that helps or brings up any questions!