Here’s my situation. I’m using Airtable to visualize information about my direct reports on an engineering team. It’s around a dozen people, all working on various things. I’m using Airtable to give me different views: for example, how much of their learning/development budget they’ve spent, or what projects they’re working on, etc.
I have one table for people, one table for projects, one table for tags about the projects (things like “infrastructure” or “public-facing” or “front-end”), etc etc etc.
So here’s where I’m currently stuck. Each project has a lead, which is a field in the project’s entry in the project table. What I want to do is have a field in the people table show whether that person is a lead on a project, and what project, and have that automatically populate. I don’t want to have separate data sets - I don’t want to have a field on the person that says “lead” where I have to go in and manually select the project, because that could get out of sync if I make changes to the project itself.
So for example: I add a project to the projects table, Destroy the Ring. I add a person to the people table, Frodo Baggins. I link Frodo to Destroy the Ring in the “What are they working on?” linked record field. So far so good, the two tables are linked.
But what I want is a field called “Project Lead” that automatically populates on Frodo’s record, if I assign Frodo to the “lead” field in the project record.
Does that make sense? Is what I’m looking for a rollup? Or do I do this with a formula? I know what I want, but I’m kind of stuck in how to achieve it.
(PS - definitely willing to entertain arguments about who was actually the lead on that project)
