Creating a summary report based on multiple columns

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
In our project management app, each team member can be assigned to a project as team 1 lead, team 2 lead, team 3 lead or additional team. I need to create a summary report that shows each team member and how many total projects they're assigned to. So basically "find each person and count the number of projects in which they are listed in field1, field2, field3 and field4." I have a table report that shows this per person, but I need the summary.
Photo of Jason

Jason

  • 152 Points 100 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
Do you have a Table of Team members. Ie, a table of unique employees and a relationship to the projects?

If so, you can make a summary field of the number if active projects where the Resource is the Team Lead 1. Then repeat for each position.
Photo of Jason

Jason

  • 152 Points 100 badge 2x thumb
We do not. Our assignments are just based on the users and not stored separately in a table. Three of the fields are set up like the below screenshot. The fourth is similar, but you can select more than one person.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,296 Points 50k badge 2x thumb
I think that you would need to set up a Parent table of Users with the Key field Userid and make relationships instead if the plain user field. That would cover the first 3 positions.

For the last position where you can assign multiple resources, this would need to be done with a child table of resource assignments.

1 Resource has Many Resource Assignments.
1 Project has Many Resource Assignments.

So I think that you would need to make those structural changes to the app to get that that summary. You have to decide if the “Juice is worth the squeeze”.