Saratoga Technologies Microsoft CRM Blog

FetchXML Introduction & Lookup Filtering

So what is FetchXML? What can it do? and how can i easily utilize the functionality it provides? Some of these may be the questions that you have had and if this is the case this article may provide some clues to help you in your CRM endevours.

Fetch by definition (Thanks MSDN) is a proprietary query language that is used in Microsoft CRM, and is used with the Fetch Method. What this translates to is basically a question and a response. For example you want to know how many accounts have addresses in Tennessee. You can simply form a query to the server by using fetch and get the information that you need.

Fetch is great at providing data easily for use inside crm, outside crm (such as via a webservice), and for other items like pre-filtering lookup fields in CRM. There are many ways to also utilize FetchXML so lets talk a little about the Structure of a Fetch Query.

So now that we are getting started looking at fetchxml structure you really should visit http://www.stunnware.com/crm2/topic.aspx?id=FindingData6 and click on the “Download” Area to download the FetchXML Wizard Software. You don’t have to have this by any means to write fetchxml queries, but it will definately be easier on you.

Below is an example of a fetchxml query.

<fetch mapping=”logical” distinct=”true”>
  <entity name=”email”>
    <attribute name=”from” />
    <attribute name=”ownerid” />
    <attribute name=”sender” />
    <attribute name=”subject” />
    <filter>
      <condition attribute=”ownerid” operator=”eq” value=”{AD1A55EE-D5AC-DC11-BF6D-000D60559FC9}” />
    </filter>
  </entity>
</fetch>

There are a few things to examine in the structure of this query. The first thing is to define the mapping of the query. This is the first line that defines the query, i have also specified distinct=”true” this means that i am looking for unique only records in my resultset.

<fetch mapping = “logical” distinct=”true”>

The next line:  <entity name=”email”> Defines the entity (or you can think of this as a table in the database) that we are going to query for record information. In this case it is the email entity.

Next you will see the following lines:

<attribute name=”from” />
    <attribute name=”ownerid” />
    <attribute name=”sender” />
    <attribute name=”subject” />

These are the attributes or basically the columns in the database that we are going to bring back in our query. So far we have outlined the information we want to pull out but what really specifies the criteria that this data is captured? This is where the filter element comes in. With the filter element you can specify which attributes should have specific criteria in order to create your result set.

In the above example this is specified by the following condition attribute:

<condition attribute=”ownerid” operator=”eq” value=”{AD1A55EE-D5AC-DC11-BF6D-000D60559FC9}” />

This is basically saying that the ownerid of the email is going to equal me. Just about every operator is supported so it is unlimited the types of comparisons that you can do.

That is all there is to a fetch query. With this query i can now pull all emails from our demo system here that i happen to be the owner of. There is a ton more that you can do with fetchXML so here is a quick usage for these types of queries.

This is known as lookup filtering and can be done with any supported lookup attribute in CRM. On the onload event for the account form add the folowing code

***** Beginning of Code

var accountID = crmForm.ObjectId;

crmForm.all.primarycontactid.lookupbrowse = 1;
crmForm.all.primarycontactid.additionalparams = “fetchXml=<fetch mapping=’logical’> <entity name=’contact’> <all-attributes/><filter type=’and’><condition attribute=’parentcustomerid’ operator=’eq’ value=’” + accountID + “‘/><condition attribute=’statecode’ operator=’eq’ value=’0′/></filter></entity></fetch>”;

crmForm.all.primarycontactid.additionalparams += “&selObjects=2&findValue=0″;

 ***** End of Code

The purpose of this short code is to pull the GUID of the current account that is open and then pre-filter the primarycontactid form with only available contacts that have been related within CRM to that account. This pre-filters the results and is easy to accomplish.

There are lots of interesting places in CRM you can filter to save yourself some time with searching. This is just a quick example to give you an idea of the possibilities. Until next time.

Take Care!

One Response to “FetchXML Introduction & Lookup Filtering”

1. nickotze Says:
   June 10th, 2008 at 5:51 am

   Thanks for the tip guys. I used to work for Saratoga in Cape Town and now am working for K2.com in the UK. I am doing some K2 integration work with CRM and found your advice when trying to Google how to do something. Very stoked to see Saratoga right up there!

Leave a Reply

You must be logged in to post a comment.