Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Trying to re-create Select Where in AirTable...better ways to go about it?

Topic Labels: Base design Views
204 2
cancel
Showing results for 
Search instead for 
Did you mean: 
KatelynLiz
4 - Data Explorer
4 - Data Explorer

Using Airtable to do staffing plans. Basically we have [Projects] which have [Phases], which in turn have [Staffing Needs] which [People] can then be assigned to ([Assignments]). 

For reasons, the duration of a Phase can vary from a month up to a year. This means for some projects a single person may be have 12 separate assignments -- this allows us to have the specific workload/allocation someone is putting towards an assignment vary month over month (e.g. John is working on Project Airtable with 10% of his time in January, 15% in March, 5% in April, etc.)

A critical interface is a Project view where we can look at an individual project and see all the people working on it.

I have been able to do this using Timeline views easily, and also with a bit of regex magic to select a project and a specific month and get a list of individuals and their allocation for that one month. 

But what I really want is a list/grid view that is each person who will work at the project at all and list them only once along with their average allocation. 

The only way I have found to do this is, for each project, to go to the People table and add a roll up field that pulls out the unique project name only when the project name is the one I want, another two role up fields for the earliest start date (MIN({Start Date}) from the person's linked Assignments) and latest end date (MAX{End Date}) and their average capacity (AVERAGE({Capacity Provided}). 

This works, it gives me *exactly* the view I want. But it is hardcoded to a bunch of rollups for each specific project...and I have 86 projects at the moment. So I have to add 344 fields to my people table and then build a unique list view in an interface for each project

It has been awhile since I've done anything with SQL..but I'm pretty sure if I was just using that I could do a select where operation to get the right records... Select Person/Assignment where Project is some specific project.... it feels like there just must be a better way to do this kind of thing in AirTable. 

Please help, before I go create those 344 new fields!! 

2 Replies 2

What if you created a table where each record represented a single person and project?

Screenshot 2025-02-10 at 10.17.18 AM.png

And to populate the data you'd use an automation to paste '[PROJECT NAME] - [PERSON NAME]' into the linked field:

Screenshot 2025-02-10 at 10.16.56 AM.png

Screenshot 2025-02-10 at 10.17.07 AM.png

Screenshot 2025-02-10 at 10.19.53 AM.png

And I've set it up for you here

VikasVimal
6 - Interface Innovator
6 - Interface Innovator

Basically what Adam said. You need a mapping table, as that is more in line with how a database should be designed to make use of Airtable's count, Rollups, Lookups etc.