- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 08, 2025 03:42 PM
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!!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 09, 2025 06:21 PM
What if you created a table where each record represented a single person and project?
And to populate the data you'd use an automation to paste '[PROJECT NAME] - [PERSON NAME]' into the linked field:
And I've set it up for you here
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 11:48 AM
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.
