[2020-09-18 15:00 Z-7: Edited to address @Jean_Pierre_Traets’s reply]
I’m a novice Coda user. I’m creating a doc to review data that I will be regularly importing (pasting) into Coda. I’d appreciate this group’s advice on how to properly structure this to avoid problems.
For sake of discussion (and not my real situation but easier to explain), let’s say I’m a brick-and-mortar store’s supervisor and I need to review each hand-entered order, to see if any mistakes were made, like the same customer being entered with Customer Name “John Smith” in one order and “Johnny Smith” in another. Each week, I will start the review by copying the Orders table from a CSV and pasting it into Coda on top of the old data, like this:
I don’t need to edit any of the imported data in Coda, but in Coda I do need to document my review, so that next week I know I don’t need to review that particular order again, and I need to flag anything wrong with each order. Nope, I can’t shift to generating the Orders data in Coda or anything with an API, unfortunately.
A tricky aspect: Almost anything in the source table could change between imports to Coda. Luckily, the source does include date-stamp columns of Created Date and Modified Date for each order. If I already reviewed an order but the order’s Modified Date is newer than that review’s date, I need to review it again, preferably while being able to see any notes I recorded in my prior review.
My first design thought was to simply have (a) an Orders table into which I paste the entire CSV each week and (b) a view that shows this table and adds columns to record the review, like this:
But then I realized that in Coda, when I add columns to this Review view, I’m really adding columns to the underlying Orders table. That means I have to be careful, whenever pasting an updated source table into Coda, to align the rows. When news rows are added to the end of the source table, like in blue below, that’s fine. But if, in the source, a new row was inserted between rows that I had already imported, like in the light-gold row below that has an erroneous order number that duplicates another order’s number, I’ll wind up with pasted orders that don’t match the reviews.
By always sorting my source table by its Created Date before pasting it, I ought to be able to avoid that problem, but I would still worry that the source might have some unexpected change and I would ruin my Coda data.
What about having a Reviews table that is linked to the Orders table by a Coda-generated ID (because even the Order Number in the source might change or, like above, have an erroneous duplicate)? How would I then set up a view or form that would let me review all un-reviewed orders? Or is there another good way to safely handle this?