Syncing tables across bases--help needed

[neildkane]

I have a typical CRM Airtable base that has three tables: Name, Company and Project. Records in the tables are all linked together. 

Now I want to create another base that uses (syncs) information from two of the tables: Name and Company. It's easy enough to create a new Base and sync the two tables...but when I do, the linking between the two tables goes away. In other words, in the new Base I lose the knowledge that John Doe works for XYZ company. It shows us as text, of course, the records are not linked. 

Is there any way to fix this? 

[Cobey-Wan]

The core issue is that Airtable's native syncing doesn't automatically recreate linked record relationships between bases. It syncs the data, but the links become plain text. Here's the most effective way to solve this:

1. Add a Formula Field for Record IDs:

• In the original base: In both the "Name" and "Company" tables, add a formula field.
• Formula: Use the RECORD_ID() function in the formula field. This will generate a unique ID for each record. Name these fields something like "Name Record ID" and "Company Record ID".

2. Sync the Tables (Including the ID Fields):

• Create the new base.
• Sync the "Name" and "Company" tables from the original base to the new base.
• Crucially: Make sure you include the newly created "Name Record ID" and "Company Record ID" formula fields in the synced data.

3. Create Linked Record Fields in the New Base:

• In the new base: In either the "Name" or "Company" table (depending on which way you want the link to work), create a new linked record field.
• Link to the other table: For example, in the "Name" table, create a linked record field that links to the "Company" table.

4. Create an Automation to Re-establish Links:

• In the new base: Create an Airtable automation.
• Trigger: "When a record is created" in either the "Name" or "Company" table (it doesn't matter which, but be consistent).
• Action: "Update record."
• Record to update: "The record that triggered the automation."
• Field to update: The newly created linked record field (e.g., the link from "Name" to "Company").
• Value to update: This is where the magic happens. Use a LOOKUP formula like this:
  • LOOKUP( {Company}, {Company Record ID}, RECORD_ID( FIND_RECORD( {Company}, {Company Record ID} = {Name.Company Record ID} ) ) )

🤘Airtable Rocks 🤘

[MloganAirtable · Accepted solution]

Hey Neil - 

We actually rolled out the ability to keep linked records in synced tables in August of 2024! There is a full support article here. That said, copying and pasting some of the directions from there here

1. Navigate to the base where an existing sync source view has been configured or follow the steps covered here to create a new sync.

2. Click on the Share and sync button.

3. Click the Sync data to another base option.

4. Click the Sync linked tables option. You’ll see the number of tables connected to the table/view via one or more linked record fields that you are configuring as a source sync. Additionally, there is a preview of how many of those tables contain an existing syncable share link that you can choose from in the next step.

5. The next step will depend on whether you are:

   • Choosing a syncable view already configured - Use this option when you already know of a view that contains the settings appropriate to your use case for syncing.

     1. Next to the “Default view” section click the Select a default view drop down. If you are updating a previously configured source sync, then you might see the name of the current default view to click.

     2. Choose which syncable view you would like to set as the default view. The number of syncable views will be determined by how many have already been set up in the linked table that you are connecting with.

     3. Click the arrow symbol ↗ to open a new tab in your browser if you need to make any modifications to the syncable share view that you’ve selected.

   • Creating a new syncable view - Use this option when you need to create a new syncable share view in the other table.

     1. Click the + Create new syncable view option.

     2. A view creation window will appear. Give the new view a name and toggle on the option to “Allow other bases to edit data in this created view” in order to allow two-way syncing.

     3. Click Create view

     4. The sync configuration window should update the “Default view” section with the view you created.

6. You can now share the view link with your colleagues to utilize are continue building by moving on to configure the sync in one or more destination bases.

When setting up syncs containing linked tables there will be an additional step to choose whether or not to include the additional tables required to allow for linked records to be preserved within the base where you are setting up the destination sync.

1. Follow step 4 in the “Configuring a basic sync” section.

2. When you reach the “Sync linked tables” window during the configuration process all available linked tables will be chosen by default.

   • Feel free to adjust this by clicking the check icon next to each linked table that you do not want to include in the sync.

   • Remember that not choosing to include a linked table will result in linked record fields that appear as single link text rather than linked record field types.

   Once you’ve configured the sync linked tables, click Next.

3. Configure the sync settings covered in step 5 of the “Configuring a basic sync” section. Then, click Create tables. This will create the main source table as well as any additional linked tables selected in the previous step.

[neildkane]

Thank you. I think that would work, and I had a student do something like that for us a while back, but it exceeds our ability at present!

[neildkane]

Thanks, Matt. This is definitely the problem I'm having...and it sounds like the right solution. Now I just have to see if I can follow the directions closely enough to make it work. 

[matthew_b]

Hi Neil!

Airtable's native sync feature should work for you if you follow the steps the team mentioned above.

If you do run into any trouble though, you can also use Whalesync's Airtable <> Airtable 2-way sync. It includes support for linked record fields out of the box. Here's a video on how it works as well.