Inside FetchXML pt 7 – rowaggregate

If you’re using any hierarchies in your data, you’ve probably noticed the icon in your main grid views that you can click on to show the hierarchy view when records have a parent or children. It’s simple to work out if a record has a parent – just check if the parent lookup attribute contains data. But have you ever stopped to think about how it works out if a record has got any children?

Hierarchical records highlighted in grid view

It would be horribly inefficient to execute a separate query for each row to get the number of children. Instead, it will use a FetchXML feature called rowaggregate.

Similar to applying standard aggregations, this is applied to an <attribute> in your query, e.g.

<fetch>
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" rowaggregate="countchildren" alias="NumberOfChildren" />
  </entity>
</fetch>

You don’t have any control within your query of what is meant by “children” in this context. This is defined by the hierarchy configuration of the entity being queried.

Hierarchical relationship setting

You can only apply the rowaggregate option to an attribute that is the primary key in a hierarchical relationship (the “Related (One)” side in the screenshot above). You’ll receive an error if you try to apply it to any other attribute, e.g. if you try to apply it on the telephone1 attribute:

Entity: Account 'rowaggregate' is not supported on attribute, telephone1. Hierarchy relationship accountid <- parentaccountid.

You can only use rowaggregate on the top-level entity in your query. You’ll receive an unhelpful error An unexpected error occurred if you use it within a link-entity.

Leave a comment

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.