Fetch It


My colleague Tony Clayton the other day mentioned that he'd loved to have a tool that allowed him to enter fetch xml queries to rip data out of Microsoft Dynamics CRM Online. Fetch XML is a part of the CRM technology platform that allows you to create queries using an xml language. The queries can be executed against a CRM Online data store to fetch data. See the Microsoft CRM SDK topics Using FetchXml and FetchXML Schema

Here are some examples of Fetch queries from the SDK.

1. Return all attributes from the account entity for accounts created today.

<fetch mapping='logical'> 
   <entity name='account'><all-attributes/> 
      <filter type='and'> 
         <condition attribute = 'createdon' operator='today'/> 
      </filter> 
   </entity> 
</fetch>

2. Inner Joins between Entities

<fetch mapping='logical' page='1' count='10'> 
   <entity name='invoice'> 
      <attribute name = 'name'/> 
      <link-entity name='invoicedetail' to='invoiceid' from='invoiceid'> 
      </link-entity> 
      <filter type='and'> 
         <condition attribute = 'accountid' operator='eq' 
                    
                value='{7F901912-DD67-47B9-A5B3-B702B9F84680}'/> 
      </filter> 
      <link-entity name='account' to='accountid'> 
         <filter type='and'> 
            <condition attribute = 'name' operator='like' 
                       
                value='%Account%'/> 
         </filter> 
      </link-entity> 
   </entity> 
</fetch> 

 

While building Fetch XML Queries might take some time to learn, they can be very powerful in data searching.

How can you use Fetch XML Queries ?

Typically, the fetch statements are used in custom built software to access data. I've built a tool that will allow you to run them in a small client side application and store the resulting data as an xml file. You can then use Excel 2007 to convert the xml to csv. You could also write an XSLT translation to convert the xml into the needed data format.

Since I thought it might be nice to schedule data fetches, I wrote the tool to be 'hands' free. This way you can run it automatically with the Windows Task Scheduler if you want.

The tool is pretty simple to use.

1. Unzip to a folder. Do not run out of the zip file.

2. Configure the settings

To configure the tool, you'll need to set values in thee FetchIt.exe.config file

Set the values highlighted below

  • Your OrgName is the first part of your URL.  As an example, if you url is https://acme.crm.dynamics.com than you org name is acme.
  • FileName is the name of the file that will be produced. The tool will add an .xml to the end of it and use the default directory.
  • RunUnAttended will toggle the application to automatically close when done.
  • FetchQuery is where you put the fetch statement to execute.

Make sure to replace < with &lt; , > with &gt;   ( sorry but an unfortunate necessary step )

Example -

Change:

<fetch mapping='logical'><entity name='account'><all-attributes/></entity></fetch>

To

&lt;fetch mapping='logical'&gt;&lt;entity name='account'&gt;&lt;all-attributes/&gt;&lt;/entity&gt;&lt;/fetch&gt;
 

 

<userSettings>
        <FetchIt.Properties.Settings>
            <setting name="UserName" serializeAs="String">
                <value>UserWindowsLiveID</value>
            </setting>
            <setting name="Password" serializeAs="String">
                <value>PassWord</value>
            </setting>
            <setting name="OrgName" serializeAs="String">
                <value>CRM Org Name</value>
            </setting>
            <setting name="FetchQuery" serializeAs="String">
                <value>&lt;fetch mapping='logical'&gt;&lt;entity name='account'&gt;&lt;all-attributes/&gt;&lt;/entity&gt;&lt;/fetch&gt;</value>
            </setting>
            <setting name="FileName" serializeAs="String">
                <value>FetchItResults</value>
            </setting>
            <setting name="RunUnAttended" serializeAs="String">
                <value>False</value>
            </setting>
        </FetchIt.Properties.Settings> 
    </userSettings>

The last tip is that the tool will write event messages to the Windows Event Viewer Log in a source named FetchIt.

FetchEvent

Here is a screen shot of a successful fetch with RunUnAttended = false. That way I can see the window messages.

FetchIt

You can download the tool from my Sky Drive at FetchIt .

Cheers,

Jon White

Comments (3)

  1. Jon,

    First, great post it was short and offered a insight into how the application works without going into to much detail that it pushed the reader away.  What I am posting about though is I would like to know what you where envisioning when you thought about using this tool with scheduled tasks.  I like the idea, but I am failing to see the application of this to daily uses of a production CRM environment, or any environment for that matter.

    I suppose that you could use the generated XML files with some IDE system to move data between CRM and some other system.  However, when I think about this a million products, such as Scribe, come to mind that are capable of pulling the data directly from CRM.  Thus, if you wouldn’t mind would you please elaborate on how you think this could be used.

    On a side note, I really like the fact that this creates entries in the Event log.

  2. Slava says:

    <value>UserWindowsLiveID</value>

    It’s meens FetchIt will work only with CRM Online?

  3. Todd Dickinson says:

    FetchIt looks great:

    Unfortunately, I only have access to our CRM via AD, so what values do I supply to the UID/PWD fields.

Skip to main content