Report showing how many times a user appears in a list-user field

Question

Hi,
Apologies if this is already asked and answered but I didn't find it. I have a table called activities. Each activity in the table has a list-user field called activity team. I'd like to be able to have a report showing every user in my app and how many times they appear in the activity team field. So the output would look like user a shows up in 3 activity teams, user b shows up in 2 activity teams, user c shows up in 7 activity teams, etc. Any suggestions on how to do this?&nbsp;------------------------------Lester Bird------------------------------

markshnier__you · Answer

When you make a summary report any underlying detail record can only appear once. This applies to summary reports and charts and in fact it applies to table reports as well. A record can only appear in one place on the report.&nbsp;&nbsp;
So the low tech solution to your question is to put a Dynamic Filter on the report&nbsp; for the List User field conveniently it should let you select single individual team members and have the report instantly filter to get the answer for that one team member. But of course it does mean you have to one by one click on the different dynamic filter choices to get your totals.&nbsp;&nbsp;
The only other way to do this would be probably more work than you care to do. You would need to have a pipeline say run each night and wipe out and create a set of children for each activity team with the individual team members as child records. So if you had three users in the list user field that activity team would end up with three children. Then you could make a perfect report. But now you've got a build a pipeline so I may not be worth it.&nbsp;&nbsp;
------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

chayceduncan · Answer

Another option would be to use a table for something like 'Team Members' or 'Employees' - just a simple table with your directory of users. You can then use a formula query and search the Activities table for records where the list-user contains the employee/team member record in question. Do the Size() formula on that and you'll get the value you want in a simple table report you can run from this table.

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

lesterbird · Answer

Thank you both. I'm a noob to formula queries and I've tried to put one together.&nbsp;I've created a new table called "employees" with a user field called "team member" and a formula-numeric field called "number of teams". I'm trying to query field number 13 in my activities table which is the list-user field called "activity team". For some reason when I try this formula, it's always returning 0. Any suggestions on what I might be doing wrong?
var Text QUERY="{13.HAS.[Team Member]}";
Size(GetRecords($QUERY,[_DBID_ACTIVITIES]))------------------------------Lester Bird------------------------------

chayceduncan · Answer

Query syntax might be tough with a list-user field and user fields are always interesting. I would try the following:&nbsp;

In your activity table make a new field that does:&nbsp;
UserListToEmails([Team Members])

Then adjust your query as:&nbsp;

var Text QUERY="{'new field id of the one above'.CT.'" &amp; UserToEmail([Team Member]) &amp; "'}";
Size(GetRecords($QUERY,[_DBID_ACTIVITIES]))

I prefer this method because emails are much easier to read/see&nbsp;------------------------------Chayce Duncan------------------------------

lesterbird · Answer

Worked like a charm, thank you so so much!&nbsp;------------------------------Lester Bird------------------------------

Forum Discussion

Report showing how many times a user appears in a list-user field