In This Article

    Share

    Disappearing Records when Paging Fetch XML Results

    When writing a query in dynamics CRM, you might link in data from a related child entity. For example, if you wanted to get opportunity products estimated to close in the next 10 days, you might write the query below:

    <fetch>
    <entity name=”opportunity”>
    <attribute name=”name” />
    <order attribute=”name” />
    <filter type=”and”>
    <condition attribute=”estimatedclosedate” operator=”next-x-days” value=”10″ />
    </filter>
    <link-entity name=”opportunityproduct” from=”opportunityid” to=”opportunityid” alias=”oppitem”>
    <attribute name=”productid” />
    <attribute name=”priceperunit” />
    <attribute name=”quantity” />
    <attribute name=”baseamount” />
    </link-entity>
    </entity>
    </fetch>

     

    When you get back the whole data set in a single result set, everything works great.

     

    However, if you change the query to add count=”X” to enable paging, you’ll sometimes have records disappear.

     

    The second page is missing the $400,000 sale of SKU D50229!

     

    This occurs because the paging cookie stores the id of last record of the prior result set and sees the same opportunity id at the end of the first page and eliminates the “duplicate” Q2 Product Purchase out of the results for the new page. The solution is easy, rewrite the query to put the child record in the <entity> node with a link-entity into the parent record. Best practice is to always have the child record appear in the <entity> node and link in any parent/grandparent records rather than the other way around.

    <fetch count=”3″>
    <entity name=”opportunityproduct”>
    <attribute name=”productid” />
    <attribute name=”priceperunit” />
    <attribute name=”quantity” />
    <attribute name=”baseamount” />
    <link-entity name=”opportunity” from=”opportunityid” to=”opportunityid” alias=”opp”>
    <attribute name=”name” />
    <order attribute=”name” />
    <filter type=”and”>
    <condition attribute=”estimatedclosedate” operator=”next-x-days” value=”10″ />
    </filter>
    </link-entity>
    </entity>
    </fetch>