Help

Use formula to auto populate field in main table based on data from second table

Topic Labels: Formulas
2065 1
cancel
Showing results for 
Search instead for 
Did you mean: 
EvanB
4 - Data Explorer
4 - Data Explorer

Kind of new to using Airtable.

Let’s say I have two tables. The first table is a master table with a whole bunch of information. For purposes of this ask, I am condensing to exactly what I need to know, but ideally this table will have a lot more info based on a client. The second table is a list of people involved and their role and what clients they have.

A client can have 1 manager. But a manager can have multiple clients. So, what I want to do, is have a formula (or some other function) set up so that the Table1:Manager field auto-populates the Table2:ManagerName based on if the Table1:ClientCode value exists in Table2:Clients. I want it this way so that if clients get moved around managers, then it can update easily on my master table without as much manual intervention.

Basically:
Grab Table2:ManagerName where Table1:Clients is in Table2:Clients and insert it into Table1:Manager for Table1:ClientCode.

Table1
Name | ClientCode | Manager
GoKart | GK | ??
RollerSkate | RS | ??
FunZone | FZ | ??

Table2
ManagerName | Role | Clients
John Doe | PM | GK, FZ
Jane Doe | PM | RS

Is this possible?

1 Reply 1

You need to link your records together so you can take advantage of Lookup fields.

Seems like the {Clients} field in Table 2 should be a Link to Another Record field pointed at Table 1’s {Name} field. You could then add a lookup field to Table 2 to pull in the client’s {ClientCode}. The process of linking two tables together automatically adds a corresponding field in the other table, so when you look back at Table 2, there should be a link field already created for you for {Manager} (it will be named whatever Table 2 is named) that fills in automatically whenever you link a Manager to a Client in Table 2. Since links are reciprocal, you could also link Clients to Managers from within Table 1.