Search Linked Field other than on primary key

[Giles_Croft]

Hi there,

I’m an enthusiastic Airtable newbie… please forgive the probably daft question.

I’m trying to link tables by people’s names, in the primary field, and I don’t know how.

This is about maintaining a database of contacts, some of whom will be clients, and others who’ll be part of my network.

I want to have a “Master” table, where I’d enter the basic demographics for people I come into contact with, e.g. their name, an email address and a phone number.

The “Clients” table needs this demographic data, linked, and client-specific fields, let’s say “Current employer” and “Career goals”

The “Network” table needs this demographic data, linked, and networking-specific fields, let’s say “Industry” and “Hourly rate”

I’d imagine my first port of call when I meet a contact, would be to put their demographic details in the “Master” table, with Name as their Primary field. (What I want is for their name to be the primary field across all three tables, linked from this one, to avoid potential error, but I understand I can’t do this in Airtable.)

So, let’s say I’ve decided they’re a potential client, rather than a networking contact. I’d like to go to the “Clients” table and add a new record, with their Primary field either as their name (linked; can’t do) or as a linked field of their Demographic data from the “Master” table, so that what I see under each Client is a card, with their name and the first couple of demographic items from the “Master” table. I don’t want to have to enter their name again, but I do want their name to be the first thing I see.

I understand I also can’t do that in Airtable. I’ve implemented a workaround, by using an Auto-number in the Primary field of “Clients” and then linking to the “Master” table records in my second field. But now I have a useless, meaningless number appearing as the Primary field against all my Clients.

What am I doing wrong? How do I link across tables, using the “Master” contact name to show up as the linking field across these three tables?

Second, related question… is there a role for a unique ID at all in this model? I thought that was a fundamental aspect of databases!

Sorry for long winded question and thanks for any advice!

[Ptt_Pch]

I’m not entirely sure to follow you but…

I can say that when you link 2 tables (or 3, etc…) the Linked record field always shows whatever you put in the primary field as the « name » of the linked record. (Not sure it’s very clear but I can’t find another way to say this right now :confused: )

So if, you enter the name of a person in your [Master] table by creating a new record, once you’ve linked that record to your [Clients] table, the name should appear in the Linked to record field in your [Clients] table.

If you don’t want to retype the name of the person in the primvary field of your [Clients] table (or give this « newly created linked record » another name in your [Clients] table), there’s a simple solution for that by using a Formula in your primary field in your [Clients] table with simply the name you gave to your Linked record field in your [Clients] table as formula.
(This create a « one-to-one » link (if I’m not mistaken) between the Linked records field and the primary field still in your [Clients] table)

You can do that with your other tables too.

When you create a new record in your [Master] table, if the person is not already in your [Clients] table you can just hit the + add a new record button when linking this person to the [Clients] table and the name of the person will appear in the primary field in that table.

To get the other info you need in your [Clients] table coming from your [Master] table, you then can use either Lookup fields or Rollup fields, depending on what you need and how you need these info to appear

I’ve put up a tiny base for your base simply showing how the name in the primary field can work.
(You can copy that base to your workspace to see it in details :winking_face: )

Airtable

Clients - Airtable

Explore the "Clients" base on Airtable.

I hope this will help :slightly_smiling_face:

[Giles_Croft]

Ooh, I think that’s it, yes! As I’m new to databases, it’s making my brain hurt a bit, but playing around with my own tables, I can see that I could get this to work how I want it to.

Thank you!

I will have to read some more about the Lookup and Rollup fields to get my head round that next.

So… to answer my own second question: I don’t really need a “Unique ID” field anywhere to link records across all tables? (I don’t suppose it would hurt to give every new contact in the “Master” table a unique ID though, would it?)

[Ptt_Pch]

Lol, no worries, I’m fairly new to Airtable too :winking_face: and I do understand brain can suffer with databases :yum:

But with time and a little bit of practice you’ll see your bases evolving, becoming more precise, as you’ll be learning more and more on how all of this works :winking_face: .

Reading and playing around with « Test bases » (if you’re not sure what you’re looking for or what you want to do exactly) is a great way to progress :winking_face: . (Well, it works pretty for me at least :yum: )

Lookup and Rollup fields each have their advantages and their inconveniences, often depending of the results you want :winking_face:

Concerning the « unique ID », well I can’t really help as I’ve not yet had the use for it…
But I effectively don’t see why not ?
It could be useful to differentiate, at some point, 2 persons with the same name :woman_shrugging:

And don’t hesitate to ask if you’ve got a problem :winking_face:
The community here is very helpful and there are some pretty good experts always glad to help when they can :winking_face: