locked
How to write Nested FetchXML RRS feed

  • Question

  • I am trying to retrieve all childs and child's-child and so on for particular company. How i can write nested FetchXML query for this.


    F a i r L i g h t


    • Edited by FairLight Friday, September 7, 2012 4:23 PM
    Friday, September 7, 2012 3:34 PM

Answers

  • Hi,

    You can do this in a single fetch - something like:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="contact">
        <attribute name="fullname" />
        <attribute name="contactid" />
        <attribute name="parentcustomerid" />
        <order attribute="fullname" descending="false" />
        <link-entity name="account" from="accountid" to="parentcustomerid" alias="aa">
    <attribute name="accountid" />
    <attribute name="name" />
          <filter type="and">
            <condition attribute="name" operator="like" value="%w%" />
          </filter>
        </link-entity>
      </entity>
    </fetch>

    Theoretically - if you are just after the name attributes for the parent record, you don't need the link-entity because you can get the formatted values for these - but I've added the link to show you how to get more than just the name.

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Friday, September 7, 2012 5:15 PM
    Answerer

All replies

  • Hi,

    You can use the link-entity construct:

    <fetch mapping='logical'>
       <entity name='account'> 
          <attribute name='accountid'/> 
          <attribute name='name'/> 
          <link-entity name='systemuser' to='owninguser'> 
             <attribute name='fullname'/> 
          </link-entity> 
       </entity> 
    </fetch>  
    

    This acts as as a LEFT INNER/OUTER JOIN in T-SQL.

    hth,

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Friday, September 7, 2012 4:30 PM
    Answerer
  • I have Particular accountid. I will find it's child by comparing this accountid =parentaccountid and i need to repeat for all the childs untill i don't find any child.

    i need all this information in one table having accountid, name and parentaccountid.


    F a i r L i g h t

    Friday, September 7, 2012 4:57 PM
  • Hi,

    You can do this in a single fetch - something like:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="contact">
        <attribute name="fullname" />
        <attribute name="contactid" />
        <attribute name="parentcustomerid" />
        <order attribute="fullname" descending="false" />
        <link-entity name="account" from="accountid" to="parentcustomerid" alias="aa">
    <attribute name="accountid" />
    <attribute name="name" />
          <filter type="and">
            <condition attribute="name" operator="like" value="%w%" />
          </filter>
        </link-entity>
      </entity>
    </fetch>

    Theoretically - if you are just after the name attributes for the parent record, you don't need the link-entity because you can get the formatted values for these - but I've added the link to show you how to get more than just the name.

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Friday, September 7, 2012 5:15 PM
    Answerer