Grouping Multi-select OR Filtering Linked Records. Help please!

Hello! I have what I imagine is a pretty common set-up. I have 2 tables in my base:

  1. Projects
  2. Staff

Projects includes things like name, start date, end date, complete (checkbox).

When I create a new project, I have a multi-select option to assign many staff to the project. My issue is this - I would like to view all the projects that each staff member is working on in one list (or a Gantt chart, but the same problem arises). Seems simple, but I’m running into two issues:

1 - In the Projects table, I cannot group by Staff because it groups them both as individuals and when they are in groups together.

2 - In the Staff table, it pulls over all projects that have been assigned to them, not just Active Projects.

I have also explored creating a 3rd table to track Assignments separately, but that did not solve the issue either. But I’m open to that if that’s a better option.

Seems simple, but I cannot find a way around it. I look forward to any suggestions - thanks!

Hiya. Here, could you set up the projects table to have two linked record fields – one for active projects, the other for inactive projects – linking to the staff table? Then, from the staff table, you could easily see all the active projects each staff member is working on. (Setting the Staff table’s row height to Extra tall would help as well.)

image

Would this be okay?

It sounds like you want the data about what Active projects each Staff member is on to be a field in the Staff table so you can create a filter/view based on it.
To get that info you’d need to add a Lookup field to the Staff table so you can do a field lookup that meets certain criteria. The Lookup field in the Staff table would reference the Linked Projects column, and select the Name of the Project, but would also enable the filtering option for the results (a small toggle at the bottom of the customization). That filtering should be set to only include Project table results that are active.

Adding some names to the description:
Project table has a primary field of “Name” and a “StatusActive” field checkbox that can be checked or not.
Staff table has a field “OnProject” that links to the Project table for all projects the staff member is/was a member of.

Create a new Lookup -type field in Staff named “OnActiveProject”, set it to use “OnProject” field as the “Field on this table…” setting, then “Name” as the field from the linked Project table (the second drop down). Enable the “Only include linked records from…” toggle at the bottom and set Where “Status” is checked. The result will be that your new “OnActiveProject” field contains a calculated list of only those active projects the staff member is part of.