Editing records in Multi-layer Synced Tables
Ever run into an issue where you need to edit a record in a synced table, but the origin table is also a synced table?
Well, here's a workaround solution. Let's start out with the specific use case I ran into...
I was working with a table (Table A) in a large base (Main Base) that was receiving it's initial data from an Email sync. That data was being accessed by several Teams and each team had their own workflows in Team Bases. Each Team had specific data points they needed to edit in the Main Table, based on data in their Team Base. They needed a way to utilize their Team Base data to edit the Main Table.
Original Solution:
Each Team had a synced table (Table B) of the Table A in their Team Base. They had created a field (Field A) in Table A as well as a "duplicate" editable field (Field B) in the Table B.
A new synced table (Table C) in the Main Base was created, synced from Table B. Finally an Automation would run to edit Field A in Table A with the new data in Field B in Table C. Thus being able to edit a multi-layer synced table.
Issues:
There were several issues that came up with this solution. Two that I aimed to resolve:
- Record limits in the Main Base were being hit because of the multiple teams using this method and there was no filters for the records that had been updated
- Updates on text fields were only updating a portion of the text since tables were being synced while writing in a text field.
My Solution:
I decided to approach the issues with a delta update system (only new changes).
I added a formula field (Match Check) in Table B that checked if Field A directly matched Field B. The formula* also accounts for delay of 2 minutes to allow for text to be fully entered.
IF(Name=Status,"MATCH",IF(AND(Name!=Status,DATEADD(LAST_MODIFIED_TIME(Name),2,'minutes')>=NOW()),"NO MATCH",""))
*NOTE: Airtable formula calculations may not accurately update the formulated field within the 2 minutes, but it does put a delay on that field so it's not changed before at least 2 minutes.
I added a new view (Updates) into Table B that filtered on Records with "NO MATCH" in the Match Check field.
I kept the structure of Table A > Table B > Table C > Table A. However, Table C was now synced from the Updates view.
Once a record entered that view, it would trigger the Automation to update Table A with new data from Table C. Once Table C synced back to Table B in the Team Base, it would be removed from Table C*
*NOTE: Synced tables have an option to delete records if they are no longer in the original View they are synced from. This does not mean the original record in the original base is deleted, it's only deleted from the synced table in the destination base.
Outcome:
By switching to a delta update system, I was able to significantly reduce the number of records in the Main Base while also maintaining the original workflow. As well, I fixed the issue with text only partially populating in Field A.
-------
This solution was designed to my specific use case but I've definitely run into this issue before, especially working in the enterprise/corporate space. Would love to thoughts on it and if there's a better/re-defined version of this solution.
-James
