Combining standard records & synchronized ones in one table

Hey @Nicolas_Caplat!

You’ve encountered a rather frustrating design decision that has bugged me for the past year or so.

The quick answer to your question is no. You cannot add records to a synced table.

However…

There are a few possible workarounds. They work, but they’re not the prettiest thing in the world

The one I recommend the most is just to use automations.

Build an automation that looks for new records in your synced table.

• When new records come in, the automation will create a new record in another table that is not synced.

In its simplest form, you’ll be provided with the initial data from the sync.

However, this method alone will not give you data parity if there are updates in your sync source.

If you need to maintain data parity, you can build a second automation to handle updates. It would look something like this:

There are a few risks that come with this method.

First, unless you tweak the automation a bit, the automation will overwrite changes you make in a field.

• e.g. Field 1 came from the sync.
  
  You made a change in Field 1.
  
  The source record is updated, and the automation runs to update the record.
  
  The automation updates Field 1 with the new data but overwrites the changes you previously made.

The second risk is that you can pretty quickly burn through automation runs depending on how frequently there are changes in your source record.

This isn’t a problem for enterprise accounts. For lower tiers, you’re playing with fire if you build automations with a possibility of an endless loop.

I’m probably overthinking for your use case, but that’s just where my mind goes.

Edit/Addition:

A quick bit of a side note about the automation risks.

Depending on how many records you’re dealing with, running any automations that trigger off of updates to records from external sources is a harsh lesson I’ve endured many times.

Since syncs are generally run in batches, a slight change in a value in a watched field will trigger an automation.

Even if you’re only dealing with 100 synced records, if there’s a batch update that provides a tiny change, you’re about to incur 100 automation runs regardless of whether or not there’s even anything to update.

It’s particularly harsh because Airtable doesn’t have strong automation failsafe measures or in-depth monitoring/troubleshooting.

Wow thanks a lot @Ben.Young for this detailed explanations.

While I love AirTable, I’m sometimes p****d off by its “limitations”. I’m considering moving to multiple syncs in multiple tables, but not sure yet. Dealing with databases isn’t my core skill for sure, so … it’s mostly about trial and errors I’m afraid !

Anyway, thanks a lot for you help.

Regards.

Nicolas Caplat

Hey Ben

Would you be able to show how you achieved the second stage of automation here? I can’t seem to get it right, I just want it so that anytime a synced record is updated or deleted, that the change is also made to the copied record. Therefore completely mirroring the synced data.

Thank you !

I experimented with this today, where by “standard records” I have 2 bases with 2 tables, each with records containing Status, Start and End dates - and set out to create a no-code solution.

Using a mix of Sync’d tables from multiple bases, Lookups, Formula Fields, and finally automations - where the objective was to have multiple tables from multiple bases in one calendar - I concluded that it is possible - but just how stable, or useful is arguable.

Most striking, there are obvious limitations;

• It’s for viewing only - so staff can’t interact with the Fully detailed schedule from the combined calendar.


• If I recall correctly, sync’d records count towards a base record count. So for 100 records to show in a calendar, that costs 200 records to execute.


• Automations are required to patch in date changes, or other sync’d field changes. 1 to create a record, another to update either the date or the status upon a change.

The origin Base(s) data looks like this.

Base 1 “Calendar A”

Base 2 “Calendar B”

Calculated Fields to drive Automation;

Automations for this example;

Each record in the merged table has its Sync’d record stamped in an associated linked field;

And then the Lookup fields to drive no-code Automations;

The Merged Base calendar, 4 sync’d tables and one “combined table”;

I think the Timeline is more useful due to the ability to Group;

One immediate thought comes to mind; Airtable need to seriously consider a “Workspace” interface - that would allow users to overlay bases to create multi-base Calendar/Timeline element - and importantly, edit those records just like a we can normally in a calendar/timeline within a singe base table view.

Alternatively to this idea, would be automation scripts that send JSON data back and forth between base - if I have time on the weekend I’ll code that up and see how it plays.

I don’t know. This data model is an absolute mess both in theory and in practice.

It would be poor practice to scale a solution built on the theoretical model I originally described back in July.

That’s the point behind the Connected Apps platform product roadmap and initiatives.

The thought of having all of this data fractured between bases and tables makes me visibly anxious.

It should all live in the same place and be tailored to user needs and requirements through interfaces.

There are zero reasons to have this data fractured and siloed.

It comes down to a clean implementation of an interface or two, and you’re golden.

It also just incurs an enormous amount of tech debt and makes everything way more complicated than it needs to be.