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.
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]))
Query syntax might be tough with a list-user field and user fields are always interesting. I would try the following:
In your activity table make a new field that does:
UserListToEmails([Team Members])
Then adjust your query as:
var Text QUERY="{'new field id of the one above'.CT.'" & UserToEmail([Team Member]) & "'}";
Size(GetRecords($QUERY,[_DBID_ACTIVITIES]))
I prefer this method because emails are much easier to read/see
Worked like a charm, thank you so so much!
