Automate Synced Tables - Product Information Management

And I’m guessing this is likely to reach 100 bases someday, right? :expressionless: Let’s round it up to 100 for safety sake.

And before going nuts on the best implementation approach, tell me why the data needs to be in one table? Also, please reassure me that you’ve scoped the data set and photos (and other hefty binary files) to be sure Airtable can handle the aggregate content size you anticipate.

Something or someone’s are going to expect this data in a unfied table but for what purpose exactly? Is it simply a publishing process? Search? Review and pushing on to other departments? Are you going to edit the unified data?

I’m pretty sure you can’t use the new sync feature to funnel 100 separate base/tables into a single base/table. How do you feel about creating 100 Zap recipes? Yeah - I didn’t think so - that’s a bad idea anyway.

You are in a box canyon and there are rain clouds forming upstream. You need to climb out of this and I suspect the only route available - assuming all other constraints are rigid - is to unify this data set with a custom process.

Yes, it’s feasible, but it may not be practical. I’ll let you decide.

It is certainly possible to instrument every vendor table with a script action that watches for new (or changed) records and adds them automatically to the master base/table. But this requires the development of a single action script that is deployed separately into each existing vendor table. This is not ideal, of course, and will present issues with human edit failures, debugging, separate instance testing, etc.

An alternative approach -

• Single script process looks for updates based on modified record dates; the process runs hourly against all 100 vendor bases.
• As it finds updates, it syncs the data into the master base/table.

I am biased (for good reason) so I would build such a beast in Google Apps Script. The script blocks in Airtable might be able to handle this, but it would be long-running at times and script automation have a pretty short tolerance for long-running processes.

Hello @Bill.French,

Thank you very much for the detailed answer.

Yes, it might.

Yes, everything should fit within the 20GB offered by the pro plan.

I would like to centralize the data to use it for my e-commerce and to do a data merge for my print catalogs. That would also help us to manage the allergens of the products we carry.
Yes, I would like to be able to edit the data in the unified table.

Yes, that is kind of how I feel. If only Airtable allowed us to share a specific part of a base.

I am not a developper so I do not pretend I can choose the best solution for this problem. I have a few questions though:

• what happen if I need to add a new column to my tables? Would I be able to update the script?
• what happen when I add a new vendor?
• how would we proceed should we decide to hire you for that project?

Thank you again

And, when doing so, I imagine you would like those changes to flow back into their respective bases, right?

It does actually - have you looked at the idea of a single table with a view of each vendor by creating a view for each vendor and then sharing each view separately? I’m not suggesting this will satisfy all your security requirements, but it came to mind.

It’s a complete sh*tshow. You would have to add the columns separately to all 100+ tables - a really bad idea. Modifying the script is certainly possible (and required) but that’s the least of your worries when a table schema emerges across 100+ tables.

The script would [ideally] be designed to work without modifications for any vendors. There’s no point in creating a script process that is dependent on any single vendor.

It depends on what you decide to build. Given the new requirements (editing the unified table and likely expecting synchronization back to the vendor tables) you are describing a very complex model that I would have to pass on. It’s simply too risky to encourage a client to do this with Airtable and with a multi-base architecture. It can certainly be achieved, but at what cost compared to other approaches and at what level of reliable precision and performance?