Count List Items based on User Name

  • 31 January 2018
  • 4 replies
  • 4 views
J
Badge +2

Hi Everyone

I have a SP list with the following columns

Assigned to

Due Date

I want to query the list based on the due date then count how many items each "Assigned to" person has and send that out via email.  So if the list has the following

Due Date           Assigned To

01/01/2018         Mike

01/01/2018         Mike

01/01/2018        Charles

I want the email to show:

Due Date 01/01/2018

Mike: 2

Charles: 1

 I know that I can use the Query item and filter out each person, then do a count on the collection, but that's not really feasible as users come and go.  I tried using the "For Each" I can get the user name based on the index, but can't count based on the index, I'm stuck.   Help!

thanks

4 replies

E
Userlevel 5
Badge +14

unfortunately there is not any better way then nesting several loops, one for each aggregate category.

wouldn't it be sufficient to create a list view with grouping by dates and assinees? it would automatically calculated totals for each category.

J
Badge +2

it would be, except that a custom view will only be on sharpoint and as far as I know can't be sent through email.  This report is meant for management.

J
Badge +2

Okay, I think I've figured it out.  For those that have the same question. 

1) You'll have to build an initial query with all the filters required.  Then in that query, you'll get a collection of users.  This collection of users will contain duplicates as a person could have multiple items.

2) Use a Collection operation to remove the duplicates for users.  then store this in a new collection.  This will now contain only the unique users.

3) create a For Each operation and use the new de-duplicated users collection as a filter.  Then put the result in a text variable

4) you now need to get the userID of each person.  you'll need two steps. 

   4a) Create a text variable and then use a Set Variable operation and set this new text variable (using value) to the variable you defined in step 3

4b) Create a number variable and then use a set variable operation and set this new variable to workflow data and use the variable you defined in step 4a.  Click the three dots and select USERID(as number)

5) Create a new query and filter using the new user id variable you defined in step 4b and extract any variable you need.

6) use a Build String function to record each person

There may be a more efficient method, but it's the best I could figure out.  Hope this helps others.

E
Userlevel 5
Badge +14

yes, that's the basically proper approach.

note that it's just partial solution that sums up totals per user. if you want to break down totals by dates (as you originally asked) you will have to implement just the same procedure once again for dates, and make it as an outer loop over users.

hint: you can build a Query list filter based directly on whole collection, you need not to loop element by element (user by user).

have a look on example - https://community.nintex.com/message/71985-re-query-and-filter-list-if-criteria-is-included-in-colletion-output?commentI… 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings