I am looking for a solution where I can compare two tables and update the main table with both status and also the new data entries from the second table. Appreciate if anyone can shed a light on how this could be achieved.
Basically we have a data scraping code that writes the entire dataset to a table (let’s call this secondary table) on regular basis. And we have a main table used in production. What we want to achieve is to use the secondary table to update the main one.
If the dataset does already exist in the main table and also exist during the latest script run on the secondary table (there is timestamp), then the “status” field in the main should be updated to “available”.
If the dataset does not exist in the main table but show up during the latest script run on the secondary table, then the “status” field in the main table should be “new”
If the dataset do exist in the main table but does not exist anymore during the latest script run on the secondary table, then the “status” field in the main table should be “unavailable”
I am not sure if this is too complicate for Airtable to handle as it requires sync on both tables in both ways with logic. In case someone have done anything similar, would be happy to learn how this can be achieved!
Many thanks for all of you who would like to share this knowledge.
I did something very similar for a client, just that he had the data in a google sheet, data that would be updated every so often. He needed a “tracking” mechanism in Airtable that would track each record created/updated/deleted and flag it accordingly. For that solution I used an apps script connected to a trigger that would check for any change in the google sheet and update the Airtable accordingly through an API call to Airtable.
I believe your project could be tackled in 3 different ways: