Saturday, 16 April 2016

Group by with linked entity - FetchXML Query

The following FetchXML returns opportunities grouped by opportunity owner’s managers.
  1. <fetch distinct='false' mapping='logical' aggregate='true'>
  2. <entity name='opportunity'>
  3. <attribute name='name' alias='opportunity_count' aggregate='countcolumn' />
  4. <link-entity name='systemuser' from='systemuserid' to='ownerid'>
  5. <attribute name='parentsystemuserid' alias='managerid' groupby='true' />
  6. </link-entity>
  7. </entity>
  8. </fetch>
Explanation
To create group by with linked entity FetchXML
  • Set aggregate to true in fetch node.
    <fetch distinct='false' mapping='logical' aggregate='true'>
    
  • Add required aggregate attribute in entity clause.
    <attribute name='name' alias='opportunity_count' aggregate='countcolumn' />
    
  • Add link-entity clause in entity clause.
    <link-entity name='systemuser' from='systemuserid' to='ownerid'>
    
  • Add group by attribute in link-entity clause and set keyword groupby to true.
    <attribute name='parentsystemuserid' alias='managerid' groupby='true' />
- See more at: http://msxrmtools.com/fetchxml/reference

No comments:

Post a Comment