FetchXML Happiness in CRM 4.0

One of the best things that happened at Convergence 2011 (besides winning the Xbox Kinect, of course), was learning that you can get the FetchXML from an Advanced Find very easily via the CRM 2011 interface.

However, I still have to work with CRM 4.0, where the FetchXML is completely inaccessible...or is it?

I came across a wonderful post about retrieving the FetchXML from an Advanced Find in CRM 4.0:http://ronaldlemmen.blogspot.com/2006/11/using-advanced-find-for-fetchxml.html

It's an old post, from 2006, but still as useful today as it was then.

The long and short of it is that after you hit the Find button on the Advanced Find dialog, just hit F11 to full-screen the dialog, which gives you an address bar, and then paste the following Javascript into that address bar:

javascript:prompt("my query:", resultRender.FetchXml.value);

It will bring up a dialog with the FetchXML in it, that you can grab and do with what you will. e.g.:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname"/>
    <attribute name="telephone1"/>
    <attribute name="new_jobtitle"/>
    <attribute name="parentcustomerid"/>
    <attribute name="ownerid"/>
    <attribute name="jobtitle"/>
    <attribute name="emailaddress1"/>
    <attribute name="contactid"/>
    <order attribute="fullname" descending="false"/>
    <filter type="and">
      <condition attribute="firstname" operator="eq" value="Bob"/>
    </filter>
  </entity>
</fetch>

In my case (I was writing functional tests for a plugin), I needed to limit the number of results returned by the query, which you normally can't do via the CRM 4.0 interface. I also didn't need all the fields that would normally be returned.

However, it's very easy to add edit to the XML to bring back what you need and limit the number of records returned. I removed everything but the contactid, and added an attribute to the root element to only bring back the first record. The final FetchXML I used looked something like this (though I wasn't looking for guys named "Bob", my query was a little more complex):


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" count="1">
  <entity name="contact">
    <attribute name="contactid"/>
    <filter type="and">
      <condition attribute="firstname" operator="eq" value="Bob"/>
    </filter>
  </entity>
</fetch>

This gave me exactly what I needed , the first contactid to match my criteria, thanks to some simple tricks to get access to the FetchXML.