Best way to join a dozen client tables into one master table?

Hey there ​@sabbry1987! Thanks for submitting such a great question! Because your question hasn't yet received an answer/solution from the Community, we've created a Support Case on your behalf with our Airtable Expert Team to get you the quickest response possible. Please check your email for follow-up and your case reference number—and continue to follow this thread as new community members tend to share answers and add knowledgeable responses regularly. Thanks!

If you’re on a Business / Enterprise plan, you can use multi source syncing for this:

https://support.airtable.com/docs/multi-source-syncing

If not, what you’ve suggested with the 48 lookups is your only option I think.  You could look into using third party tools to keep stuff in sync for you, but that’s probably more work than it’s worth, really

Hey ​@sabbry1987 do you have great reasons to have all clients set up in different tables?

Otherwise, I would suggest having just one base with 1 table for clients, 1 table for orders, 1 table for products (and probably one table for order line items?).

I might be missing smth on your specific use case.

Mike, Consultant @ Automatic Nation

Hi,
Long time ago I did something similar
I can share a simple script for that
Suppose you have like for example 15 tables in total (12 clients+ 3 other)
Put names of excluded (3 other) into script together with ‘Master’. 
Create Master table (by duplicating one of clients table without copying records).
Then remove extra fields. IMPORTANT - each of clients tabes must have all fields present in Master. Other fields will be ignored, but if any table has no any of ‘mandatory’ fields, script will fail. The list of ‘mandatory’ defined by Master table. Data copied as text, so If you have other types (like Single-select) on Master, turn them to text before running the script. After copying data you can set necessary data types.

If you have other case, like 12 clients tables and 20 other tables, so it’s too hard to exclude, especially when client tables can be filtered by a wildcard like ‘Client_1’, ‘Client_2’…..
you should change 3rd line to something like that

const xtables=base.tables.filter(t=>t.name.includes('Client_'))

So, here is a script:

const table=base.getTable('Master')
const excluded=['Master','Any other table to exclude','or several tables']
const xtables=base.tables.filter(t=>!excluded.includes(t.name))
const fld=table.fields.map(f=>f.name.toString())//Master defines fieldlist
const row=r=>({fields:Object.fromEntries(fld.map(f=>[f,r.getCellValueAsString(f)]))})
const gettab=async tab=>(await tab.selectRecordsAsync({fields:tab.fields})).records.map(row)
const data=(await Promise.all(xtables.map(gettab))).flat()
while (data.length) await table.createRecordsAsync(data.splice(0,50))

to be honest, I like many features of new Community engine, and 90% of new stuff, but there are 10% that was better in old version, and one of them is code block appearance, including colors and font style, sometimes too narrow to fit code that fits ok in Extensions Editor