Hi, so I have an excel file from a supplier that is being sent over every week or so. Most things will be the same, sometimes there are updates to prices, maybe 1 or 2 new products, and maybe 1 or 2 discontinued products.
Is there a way to do it in Coda tables so that i can easily check what has been changed or is new or is deleted?
Hi @yscias
Let’s assume your data are like this, with a current table, and a future updated table (I consider of course that the header will be the same)
You want to track news rows and modified rows. I assume you have an existing table in coda, and that you will have a “comparison table” where you will copy/paste updated table for comparison check
Assuming you dont check a lot of parameters, I created checkboxes column to compare updated table to existing table
Check if a product already exist
Check if the project/price of an existing product is the same as current value
Then, you can make some digest of modifications per rows, and summarize it
I also assume that the product are unique then it will be better to have a unique key (as reference number) to check if a product already exist in the master table.
Anyway, please let me know what you think about that before I go any further, depending on your exact use case ???
Yes your idea sounds great! Each product will indeed have a unique ID, which will make comparison easier.
So with this, you can track new rows and modified rows. But how about for rows that were removed? That will have to be done on the old table i guess?
Exactly, you can have the same kind of calculation directly in hour Master table, that will check the updated table to see if a row in the master table is not in the updated table !!! Let me put that in embed doc !
Thank you so much! It mostly works, just one problem that i have run into is for the price change. When i use the filter formula for price change, it also checks the boxes for new items. I tried to include a { and [New Product]=false } clause in the filter formula, but that doesnt seem to work. Formula for the Price Change column in the updated table is like this:
[Original Table].Filter(ItemID=thisRow.ItemID AND thisRow.[New Product]=false).Price.ToText()!=thisRow.Price
For the New Product column, i used the find() formula as you suggested, but the Price Change column still checks all the new items in that updated database. Any idea how to fix this?
Edit: I guess i have to use an if() formula here right?
Do you work on my dummy document or in your own ? In mine, I think it works good…if you want to share the doc (public or in DM if you prefer) so that I can directly check your formulas ?
You can take a look at this:
OK @yscias I see, I updated your formula to this one (directly in your doc). Indeed you just want the formula to apply to old products, then I used (for example) SwitchIf() on NewProduct = False
Then, is this solution OK for you ?
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
