locked
Data Retrieval Confusion - Query/Link/Fetch ? RRS feed

  • Question

  • I'm writing a C# Applet that will be creating some new custom entity records based on results taken from other custom entity records. I'm pretty much okay with using the RetrieveSingle and RetrieveMultiple Queries to get back data from a single entity, but this is a little more involved.

    I have three entities, lets call them A, B & C. And I'm trying to create entity Z from the data held in these.

    Entity 'A' being the primary entity has the majority of information I need, so a retrieve single call would do the trick, but I also need information held in entity 'C', but this isn't referenced directly by Entity 'A' so I have to pass through Entity 'B' first.

    Now I've seen some LinkEntity examples, and I understand they can be nested.

    What I don't understand is the order you set these up in and that fact that I wan't to return data from Entity 'A', 'B' & 'C' in a single call, otherwise I might as well do three single RetrieveMulitiple calls. And what about filtering which table(s) does the filtering apply to and can you filter across all three in a single call.

    I've not used Fetch (except in javascript) so I'm unsure which is the best approach and look for a decent sample to learn from... can anybody help please?

    regards

    Steve
    Saturday, June 6, 2009 4:42 PM

Answers

  • Steve - I used a very simple example for your situation. Account->SalesOrder->SalesOrderDetail
    Now, I want to get accountnumber, name field of Account, name field of SalesOrder and productid, description and quantity fields of SalesOrderDetails. Note that I am fetching this for an account with Customer number ACCT-1491. Following is my query  -

    <fetch mapping="logical">
       <entity name="account">
          <attribute name="accountnumber" />
          <attribute name="name" />
          <filter>
             <condition attribute="accountnumber" operator="eq" value="ACCT-1491" />
          </filter>
          <link-entity name="salesorder" from="accountid" to="accountid" alias="CustomerOrders">
             <attribute name="accountid" />
             <attribute name="name" />
             <link-entity name="salesorderdetail" from="salesorderid" to="salesorderid" alias="OrderLines">
                <attribute name="productdescription" />
                <attribute name="productid" />
                <attribute name="quantity" />
             </link-entity>
          </link-entity>
       </entity>
    </fetch>
    and here is the resultset. Since there is only one order for this customer which has 2 lines, i get 2 result nodes in my resultset. All you need to do is use load this up in an XmlDocument, run your XPath queries and you are all set -

    <resultset morerecords="0" paging-cookie="&lt;cookie page=&quot;1&quot;&gt;&lt;accountid last=&quot;{090E30CC-B402-DE11-83DE-0003FFE51F61}&quot; first=&quot;{090E30CC-B402-DE11-83DE-0003FFE51F61}&quot; /&gt;&lt;/cookie&gt;">
       <result>
          <accountnumber>ACCT-1491</accountnumber>
          <name>Madrona Inc.</name>
          <accountid>{090E30CC-B402-DE11-83DE-0003FFE51F61}</accountid>
          <CustomerOrders.name>ORDER - 1500</CustomerOrders.name>
          <OrderLines.productid name="EXO Handset" dsc="0">{8001C730-0806-DE11-83DE-0003FFE51F61}</OrderLines.productid>
          <OrderLines.quantity formattedvalue="1.00000">1</OrderLines.quantity>
       </result>
       <result>
          <accountnumber>ACCT-1491</accountnumber>
          <name>Madrona Inc.</name>
          <accountid>{090E30CC-B402-DE11-83DE-0003FFE51F61}</accountid>
          <CustomerOrders.name>ORDER - 1500</CustomerOrders.name>
          <OrderLines.productid name="Flip Handset" dsc="0">{807F2E4A-0806-DE11-83DE-0003FFE51F61}</OrderLines.productid>
          <OrderLines.quantity formattedvalue="100.00000">100</OrderLines.quantity>
       </result>
    </resultset>
    let me know if this does not solve your question.

    HTH,
    Maruf
    Wednesday, June 17, 2009 1:30 PM

All replies

  • Since your need is to pull data from multiple entities, using fetch is your best bet. You can use Crm Advanced Find to generate the Fetch Query for you. MVP Ronald Lemmen has posted an article about it  -  
    http://ronaldlemmen.blogspot.com/2006/11/using-advanced-find-for-fetchxml.html

    1. You can create your query in Advanced Find. Make sure Crm is not in application mode (i.e. you can see the IE Address Bar)
    2. Make sure it returns desired result.
    3. Type this following in address bar to get the fetchXml - javascript:alert(resultRender.FetchXml.value);
    4. On the alert messagebox, press Ctrl+C to copy the alert message to clipboard.
    5. Paste in notepad and use this in your C# code.
    6. Note that the result would be an string which can be loaded into an XmlDocument and parsed to get the values.
    //Code Sample from Crm Sdk
    //--------------------------------------------------
    // Retrieve the name and account ID for all accounts where
    // the account owner's last name is not Cannon.
    string fetch2 = @"<fetch mapping=""logical"">
                      <entity name=""account"">
                         <attribute name=""accountid""/>
                         <attribute name=""name""/>
                         <link-entity name=""systemuser"" to=""owninguser"">
                            <filter type=""and"">
                               <condition attribute=""lastname"" operator=""ne"" value=""Cannon""/>
                            </filter>
                         </link-entity>
                      </entity>
                   </fetch>";
    
    // Fetch the results.
    String result2 = service.Fetch(fetch2);


    HTH,
    Maruf
    Saturday, June 6, 2009 5:17 PM
  • Hi Maruf

    The advance find will not solve this problem as you are still not able to drill into entity c via entity b if you start form entity a.

    It seems you can only see the current entity and the ones its related to rather than those its related to.

    regards

    Steve
    Friday, June 12, 2009 9:04 AM
  • Steve - I used a very simple example for your situation. Account->SalesOrder->SalesOrderDetail
    Now, I want to get accountnumber, name field of Account, name field of SalesOrder and productid, description and quantity fields of SalesOrderDetails. Note that I am fetching this for an account with Customer number ACCT-1491. Following is my query  -

    <fetch mapping="logical">
       <entity name="account">
          <attribute name="accountnumber" />
          <attribute name="name" />
          <filter>
             <condition attribute="accountnumber" operator="eq" value="ACCT-1491" />
          </filter>
          <link-entity name="salesorder" from="accountid" to="accountid" alias="CustomerOrders">
             <attribute name="accountid" />
             <attribute name="name" />
             <link-entity name="salesorderdetail" from="salesorderid" to="salesorderid" alias="OrderLines">
                <attribute name="productdescription" />
                <attribute name="productid" />
                <attribute name="quantity" />
             </link-entity>
          </link-entity>
       </entity>
    </fetch>
    and here is the resultset. Since there is only one order for this customer which has 2 lines, i get 2 result nodes in my resultset. All you need to do is use load this up in an XmlDocument, run your XPath queries and you are all set -

    <resultset morerecords="0" paging-cookie="&lt;cookie page=&quot;1&quot;&gt;&lt;accountid last=&quot;{090E30CC-B402-DE11-83DE-0003FFE51F61}&quot; first=&quot;{090E30CC-B402-DE11-83DE-0003FFE51F61}&quot; /&gt;&lt;/cookie&gt;">
       <result>
          <accountnumber>ACCT-1491</accountnumber>
          <name>Madrona Inc.</name>
          <accountid>{090E30CC-B402-DE11-83DE-0003FFE51F61}</accountid>
          <CustomerOrders.name>ORDER - 1500</CustomerOrders.name>
          <OrderLines.productid name="EXO Handset" dsc="0">{8001C730-0806-DE11-83DE-0003FFE51F61}</OrderLines.productid>
          <OrderLines.quantity formattedvalue="1.00000">1</OrderLines.quantity>
       </result>
       <result>
          <accountnumber>ACCT-1491</accountnumber>
          <name>Madrona Inc.</name>
          <accountid>{090E30CC-B402-DE11-83DE-0003FFE51F61}</accountid>
          <CustomerOrders.name>ORDER - 1500</CustomerOrders.name>
          <OrderLines.productid name="Flip Handset" dsc="0">{807F2E4A-0806-DE11-83DE-0003FFE51F61}</OrderLines.productid>
          <OrderLines.quantity formattedvalue="100.00000">100</OrderLines.quantity>
       </result>
    </resultset>
    let me know if this does not solve your question.

    HTH,
    Maruf
    Wednesday, June 17, 2009 1:30 PM