I’m working on something similar – It’s not fully working, but here’s what I’ve done. Would be interested in hearing more about what others are doing for this use case…
I have a Summary Table. It is populated from other tables. Summary table is locked as much as possible to prevent manual edits. Changes from automations are allowed.
In other tables, set up automations that update cells in Summary table as records are created or edited in the sub-tables. When records are created in the Summary table, I place the record ID in the sub-table so that I know which record to update when changes happen.
I don’t have a way to handle deletes yet that I like.
In a requirements development meeting a famous database design guru was once asked - how many tables does it take to build a comprehensive dashboard for 17,500 fast-food restaurants whose name rhymes with jay-eff-see? The answer was surprising - one. More on that later(I).
You are both apparently making an assumption about data model design in a physical sense; i.e., summary tables and parent tables that represent the real-time state of other tables. This is known as synchronization-by-value; literally the replication of data to create dependent views of the same information.
Replication of information is typically a bad idea because it requires concurrent processes and dependencies that will almost certainly grow in complexity as your application matures. Synchronization-by-reference eliminates the idea of data copying and is almost always a better approach.
We often resort to data replication because other features are lacking; like security. This is often the case with Airtable and I cannot safely say that replication can be avoided in every case. However, it should be avoided unless absolutely necessary. There are generally better ways to create security at the edge of your solutions without making copies of your data.
Airtable supports a few methods to create parent/summary/detail experiences with data - (i) rollups, and (ii) views. I suggest you immerse yourselves in these tutorials before concluding a good design pattern for each of your solutions.
We often jump at the chance to start building something. This is especially true with Airtable because it’s so frickin’ easy to get started and wing it as you go, right? But this lack of thought, planning, and design of your data and how it will be utilized comes at a price. Often it is a steep price filled with weekends of re-work, reshaping your approach.
I recommend you take the time to plot the data model requirements first. And don’t just focus on the data elements and tables; give deep consideration to what questions you hope your beautiful Airtable solution will solve for you. Then – AND ONLY THEN – ponder how you will implement it.
(I) The database designer did indeed deliver a single database solution for store quality across the globe. But equally importantly, the dashboard solution also included just a single page template for all 17,000+ franchise outlets. All data can be shaped and rendered in ways that result in a single source of information authority - this is true with big systems and small Airtable systems. Full disclosure - I was that famous database design guru. :winking_face:
The solution is very easy.
Just gather what is needed and provide the ability to make changes.
Problem with those views is: I have 20 bases and 10 tables within each. I need the summary at one place.
Okay - it helps to know more about the solution and especially the scale that we are dealing with. So let’s unpack this a bit more.
First and foremost, when asking for help, sharing details and design aspects as they relate to your requirements is very important if you want experts to help you. You need to provide more information concerning the requirements in cases like this.
Based on what little I know about your project, you want federated data services that fly above the base level of Airtable. Data federation is not something Airtable provides and suggesting it’s easy to do is not likely to make it so.
Second, I just want to be clear – you intentionally picked a platform (Airtable) to build a solution that requires more than a dozen bases for which you knew – or should have clearly understood – was incapable of performing cross-base roll-ups, right?
Don’t you think you (or your solution) may have expectations that are outside the realm of what Airtable designed bases to address? Is it possible you may have failed to assess the complete requirements before assuming these features would exist?
I think - as solution-builders - we all have some responsibility to fully assess the capabilities before launching the implementation strategy. Assuming you did, you must also embrace the idea that this capability …
… must be created using aftermarket tools and services. I would risk saying that this is typically done through integration logic but surely not in Script Blocks or Script Automations - the scale is simply too vast. Furthermore, the requirements and scale of the data is too vague to offer you any additional recommendations.
In my view, you need a consultant who has experience building umbrella services that span multiple bases and even multiple workspaces. I suggest posing this as a consultant-needed article complete with a process diagram and at least a requirements sketch.
This is where you’ve probably made an invalid implementation assumption. Why have you assumed that a “base” is ostensibly a “project”?
Better stated – How would you address that issue with Airtable?
I get it. It’s a wonderful day when we discover that a vendor has accurately predicted our next requirement and paved the implementation road with a fruit-basket every hundred yards. Expecting this elation and anticipation for our every need is unreasonable and irrational. As solution-builders, we must take some responsibility for employing no-code platforms.