Accommodating tables that are regularly imported

[Wallace_White]

[2020-09-18 15:00 Z-7: Edited to address @Jean_Pierre_Traets’s reply]

Hi,

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:

CSV source:

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.

CSV source:

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?

Thanks,
Wallace

[Jean_Pierre_Traets]

Dear @Wallace_White,

I have no doubt that you know your business in detail, but hopefully you can imagine how difficult it’s to understand for an outsider based on the above explanation.

I always say to my clients, samples of your working docs and pictures/drawings of your expected outcome will make it much more easy to understand each other. You will be the only working in Coda and what do you want to do with the results of your work?

Replace sensitive info without loosing the actual intention of the underlying data

[Wallace_White]

Thanks, Jean Pierre. You might give me too much credit in thinking that I understand my problem but I do understand that my description was hard to follow without dummy Coda docs. I’ve added those to my original message above, and I’ve slightly rewritten it too.

[Federico_Stefanato]

Hi @Wallace_White,
can you give us edit permissions so that we can dig into it?

Thank you!

[Wallace_White]

Ah. OK, I’ve approved the requests from the two of you. Please let me know if I should set something differently in these docs. Thanks for your willingness to help!

[Federico_Stefanato]

Hi @Wallace_White,

I though I understood your point, but I then read carefully your description and now I’m a bit puzzled…

Could you explain the goal (not the solution) you are aiming at?
This is what I got so far, but I could be totally offroad…

1. Let’s assume you have a Master Data (CVS, Google Sheet, whatever)
2. You copy/paste this Master Data into a Coda table: Orders
   2.1 [?] Are you just appending new rows? How do you track imported data in the original source?
3. You need to amend Orders’ rows by providing review notes
   3.1 [?] Do you need to keep track of all the amendments (i.e. log them)
   3.2 [?] Is this a process involving other people? (a workflow)
4. [?] What is the ultimate expected output? What artifact you need to have in the end?

Thank you!

[Wallace_White]

Thanks, Federico, for trying to figure this out.

2.1: I would like to be able to completely replace the Orders table every time I import (paste) it, so that anything that changes will be present in Coda. I am not in control of the source. Its Order Num column is supposed to be unique to each row, but it isn’t guaranteed to be so because it is hand-entered. It’s possible that I could get the team who controls the source Orders table to add a truly unique ID to each row, which would make it easier to synchronize or align with it. Or, if I must, I could set up (in Coda or maybe Excel) a tool to compare each new import to the last import and to highlight any changes, so that I could then recreate those changes in my Coda table, inserting any rows as needed and so forth, but I’m hoping there’s a better approach.

3: I don’t need to amend any of the columns that come from the source table; I just need to make notes and such in columns that I’m adding in Coda for my review.

3.1: No, I don’t need to track my edits automatically, like an audit trail, though I thought I might use Coda’s built-inModified On property column type to keep track of the last time I reviewed each row.

3.2 Ideally, yes, one of a few supervisors could complete the review of any row, and Coda’s built-in “Modified By” property column could keep track of that, but it would be okay if this were considered a single-user scenario instead.

4: I’d like to be able to see only the rows that I need to review (i.e., filter to show only un-reviewed rows), and then once I have reviewed them, I will create other filtered and/or sorted views. I will likely end up having other columns in the review process, like “Were there any errors in this order?” and “Follow-up with customer?”, and I would like to be able to filter by those. Those things I (think I) know how to do later, once I know how to structure my review data so that it won’t get out of sync with my imported Orders data.

Thanks,
Wallace

[Jean_Pierre_Traets]

Dear @Wallace_White,

Some spontaneous questions from an other perspective:

1. Have you ever considered adding comments in the comment section as marked below?
   

   
   
   Would this be workable and have any meaning for you?

2. When you update the view of your table to “detail view” you will be able to see the history and even “close” comments in case they aren’t relevant anymore. Would this be workable and have any meaning for you?
   

   
   
   
   

3. What do you think about the following:

Create an “import table” -> here you paste the content that needs to be reviewed

Create an “reviewed table” -> all records that you have checked reviewed are listed ( with and without comments)

Create a “compared table” where the “import table” and “reviewed table” are compared and all records that are new and/or contain differences are displayed.

Of course the “compared table” will monitor only several columns that are the most relevant

At least for me, it’s often test, amend and see if you get the expected result. If not, re-think and start the process again.

You mentioned already that you do not have influence on the source documentation.
Often it’s worth to take action and to explain the provider your point of view and how that reflects all next steps, in most cases people are understanding ( because now they feel involved) and you get the buy in to receive better quality info. Especially when they understand that your work supports theirs! ( a small compliment, thanks , recognition does miracles)

It’s not always that you are able to find a better solution on spot, sometimes it takes more time to see if you can find how others have approached similar cases for inspiration.

[Wallace_White]

Hi @Jean_Pierre_Traets,

Thanks for your suggestions.

1 & 2: Good idea. Comments could be useful, yes.

3: If I understand you correctly, yes, I could make a change-tracker like that. If I noticed that a new row was inserted in the import table, I would need to manually insert the corresponding row in my “reviewed” table before copying the import to reviewed. There will be a good number of rows in this table eventually, so I was hoping not to need to do a manual adjustment like that, but maybe there’s not a better way.

Or were you suggesting a scheme in which I would only copy each row over from import to reviewed once I had reviewed it, and then the compared tracker would check for any changes to already reviewed rows?

Generally, yes, I would try to find an arrangement with the source data provider to avoid this messiness altogether, but it’s just not an option in my present situation, nor has it been in a number of other situations over the years, dealing with existing products that have fixed output formats.

Thanks again,
Wallace

[Jean_Pierre_Traets]

Dear @Wallace_White,

@Wallace_White

Or were you suggesting a scheme in which I would only copy each row over from import to reviewed once I had reviewed it, and then the compared tracker would check for any changes to already reviewed rows?

Yes, I was thinking about a way to compare the imported with existing content and creating certain filters that put in a new table only relevant for you info to put notes on.

I have to admit, that as long as the external stakeholder communication ( input/output) is not clear and agreed on, I would not put too much effort on it.

Unfortunately not everybody is open to improve workflows and stick with what they know, as they are afraid for the unknown even when it could be better for them.

Of course if you decide to take on the challenge, I will be glad to support where I am able to give added value!

[Federico_Stefanato]

Hi @Wallace_White,
my apologies for not coming back after your clarifications

My main concern (in line with what @Jean_Pierre_Traets pointed out) is that we are trying to find a solid solution to a weak process, which - usually - leads to overcomplicated assumptions/implementations that are eventually… not solid at all.

Where is the source of truth of your order lineage?
Once you have a “clean” table what are the steps to keep the system updated?