I’ve discovered that Airtable is a great way to compare and sync data from different systems. Load CSV extract from system A into one table and system B into another, then link to create a cross reference table.
Sometimes I need to cross reference on a key that’s not available in the A or B extract. So I create it (the key) in Airtable using a formula, and then convert that field to a “Link to” field.
Problem is, I want to re-run the sync periodically, loading new extracts into my already created base. If the required keys are in the extract then fine, nothing more needs to be done. But if not, I need to do a intermediate step to create the keys.
Can that intermediate step be avoided some how?
My current workaround is to load the CSV into a staging table, which applies the formula, then manually copy the whole table contents into the actual sync table where the corresponding key field is now a “link to” field. Seems a bit dumb, but is there a better way?