Rule Application Patterns, Part II: Collection Aggregation – The Sum of Counts


Robert Eaman


About Rule Application Patterns

This blog post is the second in a series which highlight common rule authoring patterns of note. The InRule ROAD Team (Rule Oriented Application Design) has compiled a wide selection of useful rule authoring patterns which we intend to share through these blogs.

The Pattern: Nesting an Aggregate Function Inside an Aggregate Function

Our second rule authoring pattern is an intermediate-level pattern with the not-uncommon goal of obtaining the number of grandchildren collection members that match a filter. While irAuthor’s collection functions are easy to use for any collection off of a parent entity, interrogating grandchildren from this parent context (where both the child and the grandchild are collections) involves a trick.

That trick is nested collection functions. But there are some things you need to know about nesting irAuthor’s collection functions:

  • It is generally easier to create these nested collection functions in syntax and then convert them to business language once they’re complete
  • A Count() function cannot be nested within another Count() function
  • A Sum() function, however, can be nested within another Sum() function

A quick pattern summary is below.

Step 1: Create Your Schema with Collections of Children and Grandchildren

The first step in our design is to set up three entities: ParentyEntity, ChildEntity, and GrandchildEntity. Then under the ParentEntity, add a collection of ChildEntity called Children.

Under the ChildEntity, add a collection of GrandChildEntity called Grandchildren. See the picture below for a reference.

Step 2: Create the Calculated Field under the Child Entity

Create one calculated field under the ChildEntity called NumberofGrandChildren. Set the value of this calculated field to Count(GrandChildren). Using a function to evaluate collection members from that collection’s parent context—as we’re doing here—is easy.

Step 3: Create a Calculated Field under the Parent Entity

Create one calculated field under the ParentEntity called NumberofGrandChildren. Because we are now two levels above the GrandChildEntity, if we wish to interrogate that grandchild collection from the parent context, we need to nest those two functions. But we cannot nest two Count() functions, so we combine the Sum() function with an inner Count() function to achieve our intended result:

And in business language:

And We’re Done!

So the lesson here is: interrogating collections inside of collections in InRule requires the use of two or more nested collection functions.

Also Worth Knowing

  • This nested collection technique would not be necessary if the children or the grandchildren collections were instead an entity reference (a one-to-one relationship). Entity child and grandchild relationships would only require standard notation to reference those fields (ex: ChildEntity.GrandchildEntity.Gender)
  • Filters can be added to these nested collection functions to interrogate specific fields on those grandchildren. For example: Sum(Children, Count(Grandchildren, Age >= 17)).

Feel free to drop me a line with questions. Happy rule authoring.

InRule for JavaScript | inrule said: "[…] couple of weeks ago we announced availability of InRule® for JavaScript through an early adopter program. To our knowledge this is […]".
Dynamic Surveys in Dynamics CRM Part II: Managing Dependencies | inrule said: "[…] this post makes extensive references to my preceding post, Surveys in Dynamics CRM Part I: Don’t Be a Monkey With Your Survey. If you are the type of person who likes to get the full story, you should go back and read the […]".
Josh Elster said: "In the time since this post was published, I've added (via a PR) full Windows Containers example code, DOCKERFILEs and scripts. They share common ancestry with the GH Gists I posted earlier, but are more up-to-date and tested. Here's a direct link to that sub-folder: Now that our Samples repository is public, feel free to file Issues if you have problems or features you'd like to see implemented. If you have something you'd like to contribute, then by all means do so! The contributor guidelines and process are listed in the root of the repository. Enjoy! I'll update the body of the post as well with this link.".