Newbie stuck in excel thinking

Ask the community Base design
#1

Hi folks, I’m hoping someone can take pity on me because I’m struggling to get my head round this as I’m an old school spreadsheet geek! Airtable would be ideal for how we want to work and would stream line things rather than the excel documents we use just now, but I’m lost without a vlookup :joy:

I have a “master” table with 5000ish records, each with 25 fields. The Primary field is an account number.

I have lots of other tables, each one is a particular clients own table. These contain between 100-200 records, with the same account numbers as from the master table as the primary field, and some notes against each account number. They might add or delete Account numbers from their own table, but they will always be contained in the master table.
Is there a way to look up the primary field in the clients table and pull the 25 fields from the master tables corresponding primary field through automatically without having to manually select it through the lookup function each time? I would use a vlookup here if it was a spreadsheet.
Or do I simply need a master table, the clients own table, and then cross reference the fields on a third to get the desired effect?

I think I need more experience with relational databases…

many thanks in advance!

#2

I’ll say yes but, since my bases/tables have been stable for awhile, I don’t trust my memory on what’s possible when adding linked fields.

So I would tell you to add a linked field in your client table to the master table. When you do this, you will be prompted to select from all of the fields in master table those that you want to add to the client table as lookups. It’s really quick.

Then, copy paste your account number column (client table still) into the linked field column (in same client table) and all the lookups you selected should magically populate for you.

Hope that works. It only costs you a few seconds if it doesn’t. I apologize if I have misunderstood your question.

#3

Thanks for this! That definitely works, its just a manual process I was hoping to not have to do and for the database to be live and update accordingly when a client adds or removes account numbers from their table. will keep tinkering.

#4

You could use automations to move data from one field to another field.

#5

That is a good idea, thanks! I also found a work around by using account references in the primary field, which means accounts now have a number and a reference, but hey, it works!