Saturday, 16 April 2016

Left Outer Join In FetchXML - FetchXML Query

A left outer join returns each row that satisfies the join of the first input with the second input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values.
In the below example, the FetchXML finds all accounts that have no leads
  1. <fetch mapping='logical'>
  2. <entity name='account'>
  3. <attribute name='name' />
  4. <link-entity name='lead' from='leadid' to='originatingleadid' link-type='outer' />
  5. <filter type='and'>
  6. <condition entityname='lead' attribute='leadid' operator='null' />
  7. </filter>
  8. </entity>
  9. </fetch>
Explanation
You can perform a left outer join in FetchXML by using the entityname attribute as a condition operator. The entityname attribute is valid in conditions, filters, and nested filters.
  • To perform left outer join in FetchXML query set following attributes in link-entity clause.
    • Set name attribute to logical name of related entity.
    • Set to attribute to linking attribute in primary entity.
    • Set from attribute to linking attribute in related entity.
    • Set link-type attribute to outer.
  • In filter clause specify the condition with following attrbutes
    • Set entityname attribute to logical name of related entity.
    • Set attribute to linking attribute in related entity
    • Set operator to valid FetchXML operator(null in this case).
- See more at: http://msxrmtools.com/fetchxml/reference

No comments:

Post a Comment