I am trying to create a base for tracking which consultants are assigned to which projects over time. We have a Projects table, a Workers table, and a Project Assignments table linking them. (It’s actually a bit more complicated than that because there’s a Clients table that Projects relate back to, but I’ll try to simplify a bit for the description!) An employee can be split between assignments, so the Project Assignments table includes a link to the Project, a link to the Worker, a field for fraction of the worker’s time, a start date and an end date. On the Projects table, there’s a rollup for how many workers are assigned to a given project in the current week and the next week, and likewise on the Workers table, there’s a rollup for which projects are assigned to a worker in the current and next weeks.
I’d like to be able to project out further–that is, to sum up how many workers are assigned to a project each week, but I can’t figure out how to do it without creating a new field for every week! I tried creating a table of dates, but there doesn’t seem to be a way to count or sum project assignments for which that date falls between the start and end dates on the project assignments table. What I want is an ad hoc lookup based on a formula. I tried with a pivot table block, a calendar, a gantt chart–none of them seem to let me sum values based on whether they fall in a date range.
I know how I’d do this in Excel or in Quick Base or using SQL. Is there a way to contort a view to achieve something similar in Airtable?
