Building Summary Report for List User Field

Question

Hi! I recently added a List User field ("Estimator(s)") to my table where the person adding a new Project record can indicate one or more individuals involved in quoting a project. I would like to create a summary report that shows how many projects each person is quoting.&nbsp;
I created a Summary Bar Chart showing "Estimator(s)" on the X axis, and&nbsp;# of Projects on the Y Axis. X axis is Grouped by "Equal Values."&nbsp;
The problem I am running into is let's say Mark Smith is quoting 10 projects alone, and 6 other projects with various other individuals. It's difficult to see the full volume of Mark's work since now I have 6 other bars on my chart showing Mark's name along with the other person he is quoting with. I would need to scan the report to find his name across all the bars and manually total them.&nbsp;
I realize the report is doing what it is told by seeing these combinations as unique values. However, is there a way to show all projects where Mark's name is included in the Estimator's field under Mark's name - even if another person is also listed, then show the next name and all their projects, etc.?&nbsp;
Basically, I want a single bar for each individual user - not a bar with every possible combination with multiple user names are included.&nbsp;

Thank you
Sherri&nbsp;------------------------------Sherri Large------------------------------

chayceduncan · Answer

Unfortunately since the data you're reporting on is a list-user field you're going to have to report on this in another way. My best recommendation if you're able to swing it - is to try and report via some kind of 'Employees' table where you have a dataset of the actual team members. In that table, there are lots of ways you could do it but the easiest might be a formula query that counts the number of records where the user in question is listed in the list-user field and then you can build your report from that value in the Employees table.

------------------------------
Chayce Duncan
------------------------------

markshnier__you · Answer

It's in case this is helpful, it is possible to create a dynamic filter field which would list the individual estimators uniquely and only once. You could then cycle through the various dynamic filters for each estimator to see what their chart looks like. But I recognize that could mean a lot of mousing and clicking as you choose different estimators.

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

sherrilarge · Answer

Thank you, Chayce. That is an interesting solution. I can create an employees table. How would I create the link between that table and the User-List field? I know how to create a table relationship, if needed. Would the "Estimators" field need to feed off the employee table, rather than my User list, or would I need to make the connection between the Estimators Field and the Employee table in my report?

------------------------------
Sherri Large
------------------------------

sherrilarge · Answer

Thank you, Mark. I think you're right that it would be a bit cumbersome to do it that way since I really need our managers to see the project count by person all at once so they can assess workload.

------------------------------
Sherri Large
------------------------------

chayceduncan · Answer

Since the List-User can contain multiple users you can't establish a direct relationship - hence the idea of the formula queries. In this case you would tag the user or the email of the Employee in their record in this new table, and then use formula queries to do a count of the records in your quoting table where the list-user field contains the employee in question using their user/email.&nbsp;
------------------------------Chayce Duncan------------------------------

Forum Discussion

Building Summary Report for List User Field

5 Replies