{"id":203,"date":"2022-01-06T17:13:35","date_gmt":"2022-01-06T17:13:35","guid":{"rendered":"https:\/\/blog.citrus-lime.com\/crmc\/?p=203"},"modified":"2022-01-06T17:13:35","modified_gmt":"2022-01-06T17:13:35","slug":"dynamics-crm-and-fetch-xml","status":"publish","type":"post","link":"https:\/\/blog.citrus-lime.com\/crmc\/dynamics-crm-and-fetch-xml\/","title":{"rendered":"Dynamics CRM and Fetch XML"},"content":{"rendered":"\n

When we want to retrieve multiple records back from CRM using the Webservice API, we use a Query Expression object in the code to define the Entity, Records and Columns we want to retrieve back from CRM.<\/p>\n\n\n\n

This Query Expression is declared like any other object in our Plugin Code or Page Code-behind, and so defined in code to build our Query \u2013 this is then passed to the Retrieve Multiple method of the CRM Webservice.<\/p>\n\n\n\n

The Webservice itself takes this object and then responds with a resulting Entity Collection object \u2013 this collection containing the records we have requested.<\/p>\n\n\n\n

Behind the scenes however, the CRM Service is translating our Query Expression into a block of XML that the Service then processes into the relevant query \u2013 the XML defining the query in a language that the CRM Webservice understands. <\/p>\n\n\n\n

This language is a format of XML known as Fetch XML<\/strong>, and is used for defining such Select Queries to CRM.<\/p>\n\n\n\n

Query Expression in C#<\/strong><\/td>Fetch XML<\/strong><\/td><\/tr>
QueryExpression q = new QueryExpression(“account”);q.ColumnSet = new ColumnSet(“name”, “accountnumber”);<\/strong>q.Criteria.AddCondition(“address1_city”, ConditionOperator.Equal, “Manchester”);<\/strong><\/td><fetch distinct=”false” no-lock=”false” mapping=”logical”><entity name=”account”><attribute name=”name” \/><\/strong><attribute name=”accountnumber” \/><\/strong><filter type=”and”><\/strong><condition attribute=”address1_city” operator=”eq” value=”Manchester” \/><\/strong><\/filter><\/entity><\/fetch><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This XML is also the language that CRM uses internally for User-built Queries via the Advanced Find.<\/p>\n\n\n\n

Fetch XML in Advanced Finds<\/h3>\n\n\n\n

When we build an Advanced Find in CRM using the standard User Interface, CRM takes the Criteria and Conditions involved and condenses into a block of Fetch XML that can then be re-used.<\/p>\n\n\n\n

Such that when a User saves the Advanced Find, CRM is saving the Fetch XML as the definition of the Find:<\/p>\n\n\n\n

\"clip_image002\"<\/figure>\n\n\n\n

Figure 1- Building and Saving the same Advanced Find within Dynamics CRM<\/strong><\/p>\n\n\n\n

When building this Advanced Find, CRM uses us an option to download the XML query behind our Advanced Find:<\/p>\n\n\n\n

\"clip_image004\"<\/figure>\n\n\n\n

This pulls down the Fetch XML so we can see how CRM has translated our Advanced Find Query:<\/p>\n\n\n\n

\"clip_image006\"<\/figure>\n\n\n\n

Why this can be useful..<\/h3>\n\n\n\n

This ability to output an Advanced Find or Query as a block of XML can be very useful when looking at Database Operations, as we can build and configure our Query in the Advanced Find, produce as a block of XML and then use this XML as the input to an automated process or procedure.<\/p>\n\n\n\n

This gives us the ability to use our Advanced Find Generated XML to drive Scheduled Tasks that run for Regular Integration or Overnight Processes, passing the block of Fetch XML into the definition of the Task to then drive which records should be processed.<\/p>\n\n\n\n

\"clip_image008\"<\/figure>\n\n\n\n

This allows us to change the input of a regular operation without rebuilding the operating code, which can give us a better degree of flexibility in its running.  Obviously writing blocks of XML is not much better than writing raw code, however the Advanced Find gives us a great tool to build our Queries in a Human Readable fashion and then simply export the XML as we need \u2013 which then makes using Fetch XML as a way of communicating our Queries a massive benefit.<\/p>\n\n\n\n

Fetch XML is also forms the basis of SSRS Reports for CRM Online, where direct access to the underlying SQL Database is not possible.<\/p>\n\n\n\n

Further Reading<\/h3>\n\n\n\n

MSDN \u2013 Use Fetch XML to Construct a Query<\/strong><\/p>

https:\/\/msdn.microsoft.com\/en-gb\/library\/gg328117.aspx<\/strong><\/a><\/p><\/blockquote>\n\n\n\n

MSDN Sample \u2013 Convert Queries between Fetch (XML) and Query Expression<\/strong><\/p>

https:\/\/msdn.microsoft.com\/en-gb\/library\/hh547457.aspx<\/strong><\/a><\/p>

Retrieve Records using Fetch XML via Javascript<\/strong><\/p>

https:\/\/arunpotti.wordpress.com\/2014\/11\/15\/retrieve-records-using-fetch-xml-java-script-in-crm-201113\/<\/strong><\/a><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

When we want to retrieve multiple records back from CRM using the Webservice API, we use a Query Expression object in the code to define the Entity, Records and Columns we want to retrieve back from CRM. This Query Expression is declared like any other object in our Plugin Code or Page Code-behind, and so<\/p>\n","protected":false},"author":43,"featured_media":40,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_price":"","_stock":"","_tribe_ticket_header":"","_tribe_default_ticket_provider":"","_tribe_ticket_capacity":"0","_ticket_start_date":"","_ticket_end_date":"","_tribe_ticket_show_description":"","_tribe_ticket_show_not_going":false,"_tribe_ticket_use_global_stock":"","_tribe_ticket_global_stock_level":"","_global_stock_mode":"","_global_stock_cap":"","_tribe_rsvp_for_event":"","_tribe_ticket_going_count":"","_tribe_ticket_not_going_count":"","_tribe_tickets_list":"[]","_tribe_ticket_has_attendee_info_fields":false,"footnotes":""},"categories":[3],"tags":[],"class_list":{"0":"post-203","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-crm"},"featured_image_src":"https:\/\/blog.citrus-lime.com\/crmc\/wp-content\/uploads\/sites\/30\/2021\/11\/Dynamics-365-Consultancy-1.jpg","author_info":{"display_name":"jadesmith","author_link":"https:\/\/blog.citrus-lime.com\/crmc\/author\/jadesmith\/"},"_links":{"self":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/comments?post=203"}],"version-history":[{"count":1,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/203\/revisions"}],"predecessor-version":[{"id":204,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/posts\/203\/revisions\/204"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media\/40"}],"wp:attachment":[{"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/media?parent=203"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/categories?post=203"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.citrus-lime.com\/crmc\/wp-json\/wp\/v2\/tags?post=203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}