We are planning to import through the airtable api, to trigger different automations. We are considering to import into one main table for any new orders with details like email, order_date, phone_number, etc. Since we also want to filter automations for certain updates (eg. order cancelled), we were planning to have separate tables for important updates. Between each table order_id will be the primary key. We would like to have a new table where we join the data of select columns on order_id similar to a join in sql. Is this possible with airtable? If not are there any zapier automations for this?
I tried the lookup function, but we will be working with largish sums of data and require absolute precision, so manually updating is not an option for this use case.
I wrote about another option we are considering of importing relevant data like a csv, which might provide more context: