Query hierarchical data

Retiring hierarchical data is easy, we dont need to iterate by for/while loop. just use ‘Above’ or ‘Under’ keywords, then it will serve you all parents or all child available.

in regular query, just ‘ConditionOperator.Above’ to get all parents.

in regular query, just ‘ConditionOperator.Under’ to get all child.

How to create this type of structure

 

QueryExpression

The hierarchical data retrieval in QuryExpression can be achieved using following filter condition operators

  • Above
  • AboveOrEqual
  • Under
  • UnderOrEqual
  • NotUnder
Prerequisite:
Make sure the specific relationship is hierarchy enabled.

here is sample query

QueryExpression query = new QueryExpression(TC.LogicalName);
query.ColumnSet = new ColumnSet(true);

query.Criteria.AddCondition(“soft_training_contractid”, ConditionOperator.Above, en.Id);
EntityCollection tcs = service.RetrieveMultiple(query);

or

QueryExpression query = new QueryExpression(TC.LogicalName);
query.ColumnSet = new ColumnSet(true);

query.Criteria.AddCondition(“soft_training_contractid”, ConditionOperator.Under, en.Id);
EntityCollection tcs = service.RetrieveMultiple(query);

==================

FetchXML

The hierarchical data retrieval in fetchxml can be achieved using following filter condition operators

  • above
  • eq-or-above
  • under
  • eq-or-under
  • not-under
  • eq-owneduseroruserhierarchy
  • eq-useroruserhierarchyandteams
Prerequisite:

Make sure the specific relationship is hierarchy enabled.
here is sample query

<fetch>
    <entity name="account" >
        <all-attributes/>
        <filter type="and" >
            <condition attribute="accountid" operator="above" value="b9ff53b3-e31c-e711-8108-5065f38af901" />
        </filter>
    </entity>
</fetch>

or

<fetch>
    <entity name="account" >
        <all-attributes/>
        <filter type="and" >
            <condition attribute="accountid" operator="under" value="b9ff53b3-e31c-e711-8108-5065f38af901" />
        </filter>
    </entity>
</fetch>

 

Thanks !!

ref: https://technet.microsoft.com/en-us/library/dn832068.aspx

https://community.dynamics.com/crm/b/crmtipsfromadeveloper/archive/2017/07/10/know-how-dynamics-crm-365-query-hierarchical-data-d365

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s