Aug 27, 2021 07:17 AM
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?
Aug 27, 2021 09:12 AM
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.