Tip #738: Use top in fetchxml

Tip top enrichedI’ll be first to admit: I was not aware that fetchxml supports top attribute (as it turns out, I’m not the only one hence the tip). It does not seem to be documented anywhere but, according to the schema, has been around since CRM 2011.

Consider this fetchxml

<fetch top="5" >
  <entity name="contact" >
    <attribute name="contactid" />
    <attribute name="fullname" />
  </entity>
</fetch>

It will emit the following SQL:

select top 5 
  "contact0".ContactId as "contactid",
  "contact0".FullName as "fullname" 
from
  ContactBase as "contact0"

And the output will be very concise, without any paging cookies and with morerecords=”0″ indicator.

Modify fetchxml to use count

<fetch count="5">

And sql becomes

select top 6 
  "contact0".ContactId as "contactid",
  "contact0".FullName as "fullname" 
from
  ContactBase as "contact0"
order by
 "contact0".ContactId asc

See? Count implies pagination and, to provide one, output needs to be sorted and one extra record is required (to be a marker for the next page). (Generally speaking, restricting results without a sort order is not very useful anywhere except testing but it’s a discussion for another day).

The difference is subtle but important: top means “give me X records and that’s it”, while page/count combination tells CRM “give me page Y of the data where page size is X records [and tell me how to get to the next page]”.

I would recommend using top if all you care about is a fixed top number of records (e.g. five most profitable accounts) and page/count when you do need to paginate over the resultset.

4 thoughts on “Tip #738: Use top in fetchxml

  1. Stuart Fawcett says:

    So if i wanted the highest number of a number field could i do this Top 1 and ordering?

    Thanks

  2. Joanny Santiago says:

    What if you want the top 2 notes from all activities completed today. So if i have 3 activities completed, i see 6 notes…

  3. Michael Blackburn says:

    Top appears to have a bug, at least when ordering by a link-entity field. I’m querying queues, and want the “top” queueitem, but “top” is really specified not by queue entry date, but by a field on the target record.
    So my query is Queue, linked to queueItem linked (inner) to my business records (let’s call them “cases”). The sort is happening on fields in “cases”. In this case, “top=’1′” does not give you the first item, but all matching items. However, “count=’1′” DOES give you the first item in the list. Unfortunately, a QueryExpression with the parameter TopCount=1 emits a fetch with “top=’1′” instead of “count=’1′”.

Leave a Reply

Your email address will not be published. Required fields are marked *