Automate Synced Tables - Product Information Management

[Victor_Proust]

Hello Airtable community! As many of you, I am really excited by the new sync and automation features but I am stuck. This is my first question so I hope I will be clear enough.

I am working for a distributor and I manage 2000 product information (tech sheet, product description, photos…) from 80 different vendors. Some information can be sensitive so I cannot let my vendors access the data from other vendors.

Since it is not possible to give a specific access to the Airtable base, I looked into the ‘portals’ proposed by Stacker or MiniExtension but the features are too limited for my need.

So, I created one base per vendor so they can upload their product information and I synced them into one master base. But now, I have 80 different tables ^^’. This is why I would like to create an automation to gather all those product information within the same table.

Do you know if it is feasible with Airtable automation? That would help a lot!

Thank you,

Victor

[Bill_French]

Victor_Proust:

But now, I have 80 different tables ^^’. This is why I would like to create an automation to gather all those product information within the same table.

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.

Victor_Proust:

Do you know if it is feasible with Airtable automation?

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.

[Victor_Proust]

Hello @Bill.French,

Thank you very much for the detailed answer.

Bill.French:

And I’m guessing this is likely to reach 100 bases someday, right?

Yes, it might.

Bill.French:

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.

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

Bill.French:

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 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.

Bill.French:

You are in a box canyon and there are rain clouds forming upstream.

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

Bill.French:

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 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

[Bill_French]

Victor_Proust:

Yes, I would like to be able to edit the data in the unified table.

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

Victor_Proust:

If only Airtable allowed us to share a specific part of a base.

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.

Victor_Proust:

what happen if I need to add a new column to my tables? Would I be able to update the script?

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.

Victor_Proust:

what happen when I add a new vendor?

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.

Victor_Proust:

how would we proceed should we decide to hire you for that project?

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?