Will FetcXml always search the first 5000 records?

Question

I have a FetchXml report running on CRM Online 2013 with some 12,000 records. The data is shifted from CRM Online to SQL database through a script. I want the record that was currently modified to be shifted to SQL only. Here is my query

string fetchXml = string.Format(
    @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
        <entity name='new_studentinformation' enableprefiltering ='1' prefilterparametername='CRM_Filterednew_studentinformation'>
            <attribute name='new_studentid' />
            <attribute name='new_primaryhomeroom' />
            <attribute name='new_lastname' />
            <attribute name='new_firstname' />
            <attribute name='new_busnumber' />
            <attribute name='new_building' />
            <attribute name='new_grade' />
            <attribute name='modifiedon' />
            <attribute name='new_studentinformationid' />
            <filter type='and'>
                <condition attribute='modifiedon' value='{0}' operator='on-or-after'/>
            </filter>
            <link-entity name='annotation' from='objectid' to='new_studentinformationid' alias='Notes' link-type='outer'>
                <attribute name='documentbody'/>
            </link-entity>
        </entity>
    </fetch>",
    DateTime.Now.AddMinutes(-2).ToString());

This queries for the record that was modified in last two minutes and shifts it to SQL database. The question has two parts:

How can I change the query so it brings the latest modified record rather that the one modified in last two minutes?
Second, will Fetch XML always query the first 5,000 records? What if the change is made, lets say, at the 6,000th record, will it be queried too?

Kiran Hegde · Accepted Answer · 2014-07-10T14:34:38.060

You can order by modified date descending and then take the first record by changing the fetch xml like below. Also by adding count="" attribute you can control the number of records retrieved. I think there is max. 5000 records possible.

<fetch version='1.0' output-format='xml-platform' count="5000" page="1" no-lock="false"  mapping='logical' distinct='false'>
  <entity name='new_studentinformation' enableprefiltering ='1' prefilterparametername='CRM_Filterednew_studentinformation'>
    <attribute name='new_studentid' />
    <attribute name='new_primaryhomeroom' />
    <attribute name='new_lastname' />
    <attribute name='new_firstname' />
    <attribute name='new_busnumber' />
    <attribute name='new_building' />
    <attribute name='new_grade' />
    <attribute name='modifiedon' />
    <attribute name='new_studentinformationid' />
    <filter type='and'>
      <condition attribute='modifiedon' value='{0}' operator='on-or-after'/>
    </filter>
    <order attribute='modifiedon' descending='true' />
    <link-entity name='annotation' from='objectid' to='new_studentinformationid' alias='Notes' link-type='outer'>
      <attribute name='documentbody'/>
    </link-entity>
  </entity>
</fetch>

well that was my question will it only always query for the first 5000 records? what if the 6000th record is changed? — hkhan, Jul 10 '14 at 14:45
You are retrieving the records order by modifieddate in descending order. so the first record will be the last updated record. — Kiran Hegde, Jul 10 '14 at 14:47
oh great. let me test. if this works you just solved a huge problem — hkhan, Jul 10 '14 at 14:48

Will FetcXml always search the first 5000 records?

1 Answers1