Skip to main content

The customer > order > product linking example use case is clear to me.

What’s less clear is having 12 tables of similar data for 12 clients and how to link them into a master table.

The 12 clients share 8 core fields including a unique identifier for records. Each client has 4-6 fields unique to their business. Those 48+ unique fields make one table cluttered, and in my opinion, necessitate 12 tables linked into a master table. That way, each client can have their particularities, but I can have a master table, master calendar, insight into potential staffing issues, etc.

How do I set up a master table to pull all records (and 8 core fields) from 12 tables that all share the same unique identifier format?

 

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=a'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=>tf,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 
 

Terms & Conditions