Can you write a single FetchXML query to get 1:many relationship?

Question

Is it possible to write a single FetchXML query that gets a root entity and multiple children? All I've been able to do is 1:1.

Have you tried using Stunnware's free tools? If it can be done with FetchXML, then the Stunnware tools will let you do it, for sure. — Harper Shelby, May 20 '10 at 14:54
It's not available anymore. He moved and the new site gives you 404. Hopefully, it'll be fixed but who knows... — Konrad Viltersten, Sep 28 '12 at 07:37
You can still get it via the old link: http://www.stunnware.com/products/tools4/download.htm — James Wood, Sep 30 '12 at 18:06

score 7 · Answer 1 · edited Aug 03 '17 at 16:52

James Wood is correct. Fetch XML is recursive so by using the link entity you can get the information you want.

For example, the following is valid:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <link-entity name="contact" from="parentcustomerid" to="accountid" alias="aj">
        <attribute name="firstname" />
        <attribute name="lastname" />
        <attribute name="telephone1" />
        <link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" alias="ak">
            <attribute name="name" />
            <attribute name="address1_line1" />
            <attribute name="address1_line2" />
            <attribute name="address1_line3" />
            <filter type="and">
              <condition attribute="name" operator="not-null" />
            </filter>
        </link-entity>
    </link-entity>
  </entity>
</fetch>

If your question really is "Is it possible to write a single FetchXML query that gets a SINGLE root entity and multiple children" then the answer is unfortunately no. However if you are able to handle duplicates of root data (For example using the Grouping functionality of an SSRS report) then what you require is entirely possible

score 6 · Answer 2 · answered Sep 30 '12 at 18:31

Unless I've misunderstood the question this is very possible.

So for example you want to find all the contacts related to a given account. This is represented in Crm by the Parent Customer Lookup on the contact to the account.

enter image description here

<fetch mapping="logical" count="100" version="1.0">
    <entity name="account">
        <attribute name="name" />
        <link-entity name="contact" from="parentcustomerid" to="accountid">
            <attribute name="fullname" />
        </link-entity>
    </entity>
</fetch>

Which gives you a result set that looks like this:

<resultset morerecords="0" paging-cookie="&lt;cookie page=&quot;1&quot;&gt;&lt;accountid last=&quot;{E704FAD6-2D4B-E111-9FED-00155D828444}&quot; first=&quot;{AD912122-6B3C-E111-9B37-00155D828444}&quot; /&gt;&lt;/cookie&gt;">
    <result>
        <name>RGD Mining Inc</name>
        <accountid>{E704FAD6-2D4B-E111-9FED-00155D828444}</accountid>
        <accountid.fullname>Bill Miner</accountid.fullname>
    </result>
    <result>
        <name>RGD Mining Inc</name>
        <accountid>{E704FAD6-2D4B-E111-9FED-00155D828444}</accountid>
        <accountid.fullname>Green</accountid.fullname>
    </result>
</resultset>

score 3 · Accepted Answer · answered May 20 '10 at 16:39

3

No, it is not possible.

answered May 20 '10 at 16:39

Matt

4,656
1
22
32

I've downvoted this because it doesnt appear to be correct. I have added my own answer. – James Wood Sep 30 '12 at 18:32
For clarification, this answer is geared towards the question "Can I get a single parent record and its children." Fetch definitely provides the ability to essentially do a join where you get the parent record multiple times along with each child, you just have to do a little processing to get the data the way you want it. – Matt Aug 31 '17 at 22:34

score 0 · Answer 4 · edited Aug 03 '17 at 16:51

I'm happy to report that it is possible. I have a solution that worked well for me.

The only caveat is that if you have multiple child accounts you will get multiple results for the parent. For example:

parent 1: child 1
parent 2: child 1
parent 2: child 2

This means that you would then have to run the results through a sorting function, to get either all the children under parents in a multi dimensional array, or get all the accounts as unique entries in a flat array.

Also, this only goes down one level. If your hierarchy is multi-level, this code would need to be modified.

<fetch distinct="false" mapping="logical"> 
   <entity name="account">     
        <attribute name="name" />
        <link-entity name="account"  alias="childaccount" to="accountid" from="parentaccountid"  link-type="outer">
            <attribute name="name" alias="childname" />
        </link-entity>
    </entity>           
</fetch>

Can you write a single FetchXML query to get 1:many relationship?

4 Answers4

Linked