Forum Discussion

LesterBird's avatar
LesterBird
Qrew Member
4 months ago

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

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? 



------------------------------
Lester Bird
------------------------------

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    4 months ago

    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.  

    So the low tech solution to your question is to put a Dynamic Filter on the report  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.  

    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.  



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • ChayceDuncan's avatar
      ChayceDuncan
      Qrew Captain
      4 months ago

      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's avatar
        LesterBird
        Qrew Member
        4 months ago

        Thank you both. I'm a noob to formula queries and I've tried to put one together. 

        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
        ------------------------------