Hello
I was hoping for some advice on how to best approach this use-case we have. Some background:
Our marketing team manages all of their accounts through Airtable. We have a primary table called "All Locations" which is our "source of truth" for location-specific data about the retail showrooms we manage. Up until now keeping it up-to-date has been a challenge as employees don't necessarily stay on top of updating the data when changes to our locations occurs.
The true "source of truth" is a locations table in our Datamart that comes from our organization's CRM system. We want to use the core organizational source of truth to update the marketing team's locations table in Airtable.
We currently have successfully deployed Zapier and have a constantly updating synchronized table with all the location data in Airtable, but we can't work off of that table as it needs to remain untouched for the sync to work as well as the fact all of the history is attached to the records in the Airtable "All Locations" table.
With all of the groundwork now laid out here is what we would like to accomplish:
- Create a link between the Zapier table to the "All Locations" table to automatically update several fields with the values in the Zapier table. Can this be done directly from the Zapier table using a linked record with the location's key field (Location ID)?
- If an existing location record in "All Locations" has its Location ID modified, is there a way to automate a check that will reference back to the Zapier table and trigger something if it doesn't find a match?
- Create automations that identify when changes occur in the Zapier data that our team should be made aware of so it will create a deliverable or some sort of notification. We have plenty of automations setup so it's more in terms of how to best approach identifying these changes between the data in "All Locations" and the Zapier table. I assume for most cases we could just have automations set to pop when the field is modified, but there's a more complicated problem: how do we identify when there is a new location added to the Zapier table? When a location closes we will catch that based on the status changing to "closed", but when a new location is created it will have a new record in the Zapier table, but no corresponding linked record in the "All Locations" table. Structurally - what would be the best approach for this problem?
Any assistance or ideas on any of the questions is GREATLY appreciated.
Thank you
