Counting Records

Process Pupil
0 1 1,344

I wanted to be able to do a Group Count on a set of items in a library (I actually wanted to save the values and counts to a list so that I could attach a chart control – but that’s another story).

There is no way of doing a Group By in the Query List task, so I had to come up with this.

Retrieve all my key values into a collection, then create a list of those unique key values

Iterate through the unique values and remove them from the original list – the number of items matching the key value is the number of items removed from the collection – the difference between the counts before and after the deletion.

In pseudocode, the process is this

QueryList : get all ‘key values’ in range into collection AllKeys

Collection: Remove duplicates from AllKeys into collection UniqueKeys

ForEach string Key in UniqueKeys

Collection: Count AllKeys into number Total

Collection: Remove by value Key from AllKeys

Collection: Count AllKeys into number Count

Math: Total Count into number KeyCount

{do something with Key and KeyCount}


1 Comment
Workflow Veteran

If you're using list lookup values, there's actually an easier way to count used items in another list. To do so, in your keywords list lookup add an extra lookup column, which gets its lookup column from the list where it is used. You will see that the used lookup has the suffix (Count Related) option becomes available. Selecting this will, after saving, give you the use count of a keyword in the referenced list/library.

For example: you have a Countries lookup list which is used in your Customers list with a Country column. In Countries you now add a lookup column to the Customers list and use the Country (Count Related) column. On saving you'll see a that it shows the number of times a country has been used in the Customers list. Which can be used in charts.

NB: this does not work for list lookup columns allowing multiple values or which are located on another site.