Query hierarchical data

 

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

Microsoft Dynamics 365 (online & on-premises) introduces the capability to define specific self-referencing one-to-many entity relationships as hierarchical. You can write queries that return related data in these hierarchies.

You can take advantage of new query condition operators to query entities with explicit hierarchical relationships. These operators only apply for the entity relationship specifically defined as a hierarchical relationship. You can use new condition operators to retrieve this hierarchical data when you query using QueryExpression or FetchExpression.

Note

The OData endpoint hasn’t been changed to support querying hierarchical data.

Condition operators for hierarchical data

Use the following operators to set conditions when querying hierarchical data.

FetchXML	ConditionOperator	Description
above	Above	Returns all records in referenced record's hierarchical ancestry line.
eq-or-above	AboveOrEqual	Returns the referenced record and all records above it in the hierarchy.
under	Under	Returns all child records below the referenced record in the hierarchy
eq-or-under	UnderOrEqual	Returns the referenced record and all child records below it in the hierarchy
not-under	NotUnder	Returns all records not below the referenced record in the hierarchy
eq-owneduseroruserhierarchy	OwnedByMeOrMyReports	When hierarchical security models are used, Equals current user or his reporting hierarchy
eq-useroruserhierarchyandteams	OwnedByMeOrMyReportsAndTeams	When hierarchical security models are used, Equals current user and his teams or his reporting hierarchy and their teams

Recursion limits when querying hierarchical data

Because querying hierarchical data can be resource intensive, there is a default limit of 100 recursions allowed conditions for hierarchical queries using the Above, AboveOrEqual, Under, UnderOrEqual, and NotUnder condition operators.

These limits can be adjusted using Windows PowerShell commands through the deployment web service. More information:  TechNet: Administer the deployment using Windows PowerShell.

OwnedByMeOrMyReports and OwnedByMeOrMyReportsAndTeams are hierarchical security condition operators that depend on the Hierarchy Depth setting that can be found in Settings > Security > Hierarchy Security. The value of this setting is stored in the Organization.MaxDepthForHierarchicalSecurityModel attribute.

Retrieve the number of hierarchically related child records

Use the rowaggregate attribute in a FetchXML based query to retrieve the number of hierarchically related child records. When this value is set to CountChildren a value that includes the total number of child records for the record is included in the EntityCollection. For example, the following query will include an AccountChildren aggregate value representing the number of child account records in the hierarchical relationship where the {0} parameter represents the AccountId of the parent record.

<fetch distinct='false' no-lock='false' mapping='logical'>
  <entity name='account'>
    <attribute name='name' />
    <attribute name='accountid' />
    <attribute name='accountid' rowaggregate='CountChildren' alias='AccountChildren'/>
    <filter type='and'>
      <condition attribute='accountid' operator='under' value='{0}' />
    </filter>
  </entity>
</fetch>

Note

The aggregate value returned represents all the child records, including any that the user may not have access to read.

See Also

ConditionOperator
Customize entity relationship metadata
Build queries with FetchXML
Build queries with QueryExpression
FetchXML schema
Video: Hierarchy Visualization in Microsoft Dynamics CRM 2015

Microsoft Dynamics 365

© 2016 Microsoft. All rights reserved. Copyright