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

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

 

<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.

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

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

Cheers,

Jon White