Executing large FetchXML with WebAPI

You can easily execute fetchxml in WebAPI using the “fetchXml” query parameter. But this “GET” method won’t work, if the fetchxml is too big. In this case, you have to use the “POST” method to execute the fetchxml.

Sample Request Header:

Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Content-Type: multipart/mixed;boundary=batch_contactfetch

Sample Request Body:

--batch_contactfetch
Content-Type: application/http
Content-Transfer-Encoding: binary

GET https://[CRM URL]/api/data/v8.2/contacts?fetchXml=<fetch count="10" ><entity name="contact" ><attribute name="fullname" /></entity></fetch> HTTP/1.1
Content-Type: application/json
OData-Version: 4.0
OData-MaxVersion: 4.0

--batch_contactfetch--

Sample Code:

var req = new XMLHttpRequest();
req.open("POST", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/$batch", true);
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "multipart/mixed;boundary=batch_contactfetch");
req.onreadystatechange = function() {
    if (this.readyState === 4) {
        req.onreadystatechange = null;
        if (this.status === 200) {
            var response = JSON.parse(this.response.substring(this.response.indexOf('{'),this.response.lastIndexOf('}')+1));
			console.log(response.value);
        } else {
            Xrm.Utility.alertDialog(this.statusText);
        }
    }
};

var body = '--batch_contactfetch\n'
body += 'Content-Type: application/http\n'
body += 'Content-Transfer-Encoding: binary\n'
body += '\n'
body += 'GET ' + Xrm.Page.context.getClientUrl()+'/api/data/v8.2/contacts?fetchXml=<fetch count="10" ><entity name="contact" ><attribute name="fullname" /></entity></fetch> HTTP/1.1\n'
body += 'Content-Type: application/json\n'
body += 'OData-Version: 4.0\n'
body += 'OData-MaxVersion: 4.0\n'
body += '\n'
body += '--batch_contactfetch--'

req.send(body);

Request Screenshot

Fetch Post Request.png

Response Screenshot

fetch-post-response

Bookmarklet: Copy and Paste Lookup

Lookups (including partylist and customer) cannot be copy pasted. I have encountered couple of scenarios where I needed this capability. I have developed these bookmarklets to solve this issue.

Copy Lookup

Select and the drag the code below to the bookmark bar.

javascript:(function(){let contentPanels=Array.from(document.querySelectorAll('iframe')).filter(function(d){return d.style.visibility!=='hidden'});if(contentPanels&&contentPanels.length>0){let Xrm=contentPanels[0].contentWindow.Xrm;let currentControl=Xrm.Page.ui.getCurrentControl();if(currentControl&¤tControl.getControlType()==='lookup'){let currentLookup=currentControl.getAttribute().getValue();if(currentLookup){let serialisedLookupValue=JSON.stringify(currentLookup.map(x=>{let c={};({id:c.id,name:c.name,type:c.type,typename:c.typename,entityType:c.entityType}=x);return c;}));sessionStorage.setItem('ryr_serialisedLookup',serialisedLookupValue);alert('Lookup copied. Ready to paste');}}else{alert('No field has been selected or the currently selected field is not a lookup');}}else{alert('Entity form not detected');}})();void 0;

Source:

(function () {
	let contentPanels = Array.from(document.querySelectorAll('iframe')).filter(function (d) {
			return d.style.visibility !== 'hidden'
		});
	if (contentPanels && contentPanels.length > 0) {
		let Xrm = contentPanels[0].contentWindow.Xrm;
		let currentControl = Xrm.Page.ui.getCurrentControl();
		if (currentControl && currentControl.getControlType() === 'lookup') {
			let currentLookup = currentControl.getAttribute().getValue();
			if (currentLookup) {
				let serialisedLookupValue = JSON.stringify(
						currentLookup.map(x =  > {
								let c = {};
								({
									id : c.id,
									name : c.name,
									type : c.type,
									typename : c.typename,
									entityType : c.entityType
								}
										 = x);
								return c;
							}));
				sessionStorage.setItem('ryr_serialisedLookup', serialisedLookupValue);
				alert('Lookup copied. Ready to paste');
			}
		} else {
			alert('No field has been selected or the currently selected field is not a lookup');
		}
	} else {
		alert('Entity form not detected');
	}
})();

Paste Lookup

Select and the drag the code below to the bookmark bar.

javascript:(function(){let contentPanels=Array.from(document.querySelectorAll('iframe')).filter(function(d){return d.style.visibility!=='hidden'});if(contentPanels&&contentPanels.length>0){let Xrm=contentPanels[0].contentWindow.Xrm;let currentControl=Xrm.Page.ui.getCurrentControl();if(currentControl&¤tControl.getControlType()==='lookup'){let currentLookup=currentControl.getAttribute();let copiedLookupValue=sessionStorage.getItem('ryr_serialisedLookup');if(copiedLookupValue){currentLookup.setValue(JSON.parse(copiedLookupValue));} else{alert('Please select a lookup to copy first before pasting');}}else{alert('No field has been selected or the currently selected field is not a lookup');}}else{alert('Entity form not detected');}})();void 0;

Source:

(function () {
	let contentPanels = Array.from(document.querySelectorAll('iframe')).filter(function (d) {
			return d.style.visibility !== 'hidden'
		});
	if (contentPanels && contentPanels.length > 0) {
		let Xrm = contentPanels[0].contentWindow.Xrm;
		let currentControl = Xrm.Page.ui.getCurrentControl();
		if (currentControl && currentControl.getControlType() === 'lookup') {
			let currentLookup = currentControl.getAttribute();
			let copiedLookupValue = sessionStorage.getItem('ryr_serialisedLookup');
			if(copiedLookupValue){
				currentLookup.setValue(JSON.parse(copiedLookupValue));
			}
			else{
				alert('Please select a lookup to copy first before pasting');
			}
		} else {
			alert('No field has been selected or the currently selected field is not a lookup');
		}
	} else {
		alert('Entity form not detected');
	}
})();

Instructions

  1. Select the lookup field to be copied. The lookup will usually be highlighted with a blue background when it is selectedlookup-selected
  2. Run the Copy Lookup bookmarklet. You will get a alert message confirming that the lookup has been copied
  3. Select the lookup field that is the target of the paste. The lookup has to be of the same type i.e. if the copied lookup was a contact lookup, the target lookup also has to be a contact lookup
  4. Run the Paste Lookup bookmarklet
  5. Save the record

I have tested these bookmarklets only in Chrome 53 and Firefox 50.

Export to Excel using Dynamics 365 SDK

With the new Dynamics 365 release, a new message has been added that making exporting FetchXML results really simple. This message is not documented, hence is technically unsupported. With that word of warning, I will show you how to utilise this new message to export data from Dynamics 365 to an Excel file.

ExportToExcel message definition

Parameter Type
View EntityReference
FetchXml string
LayoutXml string
QueryApi string
QueryParameters InputArgumentCollection

Code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.ServiceModel;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Microsoft.Xrm.Sdk;

namespace Experiments
{
    class Program
    {
        private static OrganizationService _orgService;
        static void Main(string[] args)
        {
            try
            {
                CrmConnection connection = CrmConnection.Parse(
                    ConfigurationManager.ConnectionStrings["CRMConnectionString"].ConnectionString);

                using (_orgService = new OrganizationService(connection))
                {
                    var exportToExcelRequest = new OrganizationRequest("ExportToExcel");
                    exportToExcelRequest.Parameters = new ParameterCollection();
                    //Has to be a savedquery aka "System View" or userquery aka "Saved View"
                    //The view has to exist, otherwise will error out
                    //Guid of the view has to be passed
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object> ("View",
                        new EntityReference("userquery", new Guid("{0B915102-24A7-E611-8101-1458D05B1178}"))));
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("FetchXml", @"
                    <fetch distinct='false' no-lock='false' mapping='logical' returntotalrecordcount='true'>
	                    <entity name='contact'>
		                    <attribute name='fullname' />
	                    </entity>
                    </fetch>"));
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("LayoutXml", @"
                    <grid name='resultset' object='2' jump='fullname' select='1' icon='1' preview='1'>
	                    <row name='result' id='contactid'>
		                    <cell name='fullname' width='300' />
	                    </row>
                    </grid>"));
                    //need these params to keep org service happy
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryApi", ""));
                    exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryParameters",
                        new InputArgumentCollection()));
                    var exportToExcelResponse = _orgService.Execute(exportToExcelRequest);
                    if (exportToExcelResponse.Results.Any())
                    {
                        File.WriteAllBytes("Active Contacts.xlsx", exportToExcelResponse.Results["ExcelFile"] as byte[]);
                    }
                }
            }
            catch (FaultException<OrganizationServiceFault> ex)
            {
                string message = ex.Message;
                throw;
            }
        }
    }
}

Closing Notes:

  1. “View” parameter can accept “userquery” or “savedquery”, but they have to exist i.e. you can’t pass empty Guid.
  2. The fetchxml and layoutxml can be different from what is in the “savedquery” or “userquery”. Hence, you can create a “Personal View” just so that you can use it in this message, but modify the fetchxml and layoutxml to whatever you want.
  3. The name of the tab in the Excel output file will be the name of the view specified in the “View” parameter
  4. This message can be executed from Javascript as well, but you will get a base64 string instead of a byte array in the response.

Please vote up my request on Connect (logged Feb 2015) -> https://connect.microsoft.com/site687/feedback/details/1127874/export-to-excel-sdk-message so that this message can be made available as an unbound WebAPI action.

Two useful LINQ Queries

In order to run these queries you’ll have to install LINQPad and LINQPad Driver for CRM.

Query 1 – Who created the entities?

It is not possible to look at an entity and find out who created it. You can however use the createdby on the SavedQuery (System View) to find out this information, as the System View is created the same time as the entity is created and also contains the createdby user information.

from q in
(from s in SavedQuerySet.AsEnumerable()
 where s.QueryType == 0
 group s by s.ReturnedTypeCode
into g
 select new
 {
	 g.Key,
	 Views = (from s in SavedQuerySet
			  where s.ReturnedTypeCode == g.Key && s.QueryType == 0
			  orderby s.CreatedOn
			  select new { s.CreatedOn, s.CreatedBy }).First()
 })
orderby q.Views.CreatedOn descending
select new { q.Key, CreatedOn = q.Views.CreatedOn.Value.ToLocalTime(), q.Views.CreatedBy.Name }

Entity creation query.png

Query 2 – All Plugins with message, stage, filtering attributes and entity

You can use this query to get a quick snapshot of all the plugins in the system.

from m in SdkMessageProcessingStepSet
join f in SdkMessageFilterSet on m.SdkMessageFilterId.Id equals f.SdkMessageFilterId
join s in SdkMessageSet on f.SdkMessageId.Id equals s.SdkMessageId
join p in PluginTypeSet on m.PluginTypeId.Id equals p.PluginTypeId
where f.IsCustomProcessingStepAllowed.Value
&& !m.IsHidden.Value
&& m.CustomizationLevel.Value == 1
select new { Message = s.Name, Rank = m.Rank.Value, Stage = m.Stage, StageName = m.FormattedValues["stage"], m.FilteringAttributes, p.AssemblyName, PluginName= p.Name, StepName = m.Name, StepDescription = m.Description, Status = m.StatusCode, StatusName = m.FormattedValues["statuscode"]}

plugins

Understanding Process Triggers and Business Rule internals

One of the less utilised/understood feature of Business Rule is Process Triggers. In this post, I will explain what a process trigger is and how you can use this in the context of business rule.

The Basics

Business Rule is basically a workflow that has a different UI compared to the standard workflow editor. You can quickly find all the business rules in your CRM instance by running this query.

Business Rules

Internals – How does Business Rules work

You can basically skip this part, if you are not interested in understanding the internals on how a business rules work. When you create a business rules you basically have all these components that make the business rules run seamlessly:

  1. The client side code that runs on the form
  2. The server side workflow defined in xaml
  3. Process trigger -> This dictates when the business rule logic should execute

When you design a business rule, it is automatically translated into a workflow xaml that executes on the server side and JavaScript code that executes on the client side.

Now, let us take a simple example of a business rule that sets the “Salutation”, when “Gender” is changed. Here is the business rule

Business Rule Definition

When you save this business rule, this is automatically translated into JavaScript, code that can run on the client side. Below is the JavaScript code that is generated by CRM, for this business rule:

function pbl_109af564df34e51180eac4346bc576e8() {
    try {
        var v0 = Xrm.Page.data.entity.attributes.get('gendercode');
        var v1 = Xrm.Page.data.entity.attributes.get('salutation');
        if (((v0) == undefined || (v0) == null || (v0) === "") || ((v1) == undefined || (v1) == null || (v1) === "")) {
            return;
        }
        var v2 = (v0) ? v0.getValue() : null ;
        if ((v2) === (1)) {
            v1.setValue('Mr');
        } else if ((v2) === (2)) {
            v1.setValue('Ms');
        }
    } catch (e) {
        Mscrm.BusinessRules.ErrorHandlerFactory.getHandler(e, arguments.callee).handleError();
    }
}

Below is the JavaScript code this calls the “pbl_109af564df34e51180eac4346bc576e8” function that contains the logic for the business rule.

Mscrm.BusinessRulesScript.Initialize = function() {
    Mscrm.BusinessRulesScript.AttributesOnChangeHandlers = {};
    Mscrm.BusinessRulesScript.ControlsOnClickHandlers = {};
    (function() {
        var onchangehandler = function() {
            pbl_109af564df34e51180eac4346bc576e8();
        }
        ;
        Mscrm.BusinessRulesScript.AttributesOnChangeHandlers['gendercode'] = onchangehandler;
        var attributeObject = Xrm.Page.data.entity.attributes.get('gendercode');
        if (attributeObject != null && attributeObject != undefined) {
            attributeObject.addOnChange(onchangehandler);
        }
    })();
    pbl_109af564df34e51180eac4346bc576e8();
};

From the above, triggering code we can see that the business rule is going to run when the form is opened, as the function “pbl_109af564df34e51180eac4346bc576e8” is called when the business rule is initiated. The function also executes when “Gender” is changed.

If you want to know what the generated JavaScript code for the business rule is, just get the “ClientData” field in the “Workflow” entity. You cannot get this field from Advanced Find. You can either use FetchXML Builder (a XrmToolBox tool) or LINQPad. Below is the fetchxml query, I used.

FetchXml

If you also get the “xaml” field on the workflow entity, you can see the markup server side workflow logic that will execute.

Process Trigger

Process Trigger dictates the events that will trigger the execution of business rule. There are three events:

  1. Load
  2. Change
  3. Save

“Load” and “Change” are the standard triggers when the business rule is created through the UI. “Save” is a special handler. It can only be set using the SDK and not through the UI. It behaves little differently compared to “Load”. The JavaScript code that is generated for “Save” is little different compared to the code that is generated for “Load”. Here is the LINQ query I used for getting process triggers for this workflow.

LINQPad

Now let us update the “load” trigger to “save”, so that the generated JavaScript for the business rule will run only on “Form Save” event and not on “Form Load”. Here is the simple snippet I ran to do this, after I got the ids of the process trigger in the previous LINQ Query. These process triggers are for the “load” events associated to our business rule. You have to deactivate the business rule, before you update the process trigger for the business rule.

Process Trigger Update

Now comes the important bit: Activate the business rule from the Advanced Find results (first screenshot). Don’t activate the business rule from the standard business rule window. If you activate the business rule from the standard UI, your process trigger will reset back to “load”

ActivateDont Activate

Now that the trigger is set to “Save”, lets compare the generated JavaScript code.

Compare Load and Save

As we can see, in the case of “save” process trigger, the client side business rule code runs only on “Save”. I like this, because I don’t want the business rule to run on “load” and confuse the user with “Unsaved changes” message. I will demonstrate this with a scenario.

Scenario: User opens a contact record, which doesn’t have the “Salutation” field set. Gender contains a valid value. The process trigger is “load”

Form OnLoad

As you can see from the above screenshot, the business rule ran immediately on form load, and set the Salutation to “Mr”. Hence, you have a unsaved changes message, on the bottom right. I am not very happy with this result because, it is not obvious to the user what changed and what caused the change. I want more control, so I want this rule to run only after “Save”. Now look at the same form, when the trigger is “Save”

Form OnSave

As you can see, the “Salutation” field is not set immediately. It will be set only

  1. When the form is saved OR
  2. When the “Gender” field is changed

One more thing: Every time when you deactivate and reactivate a business rule, new process triggers records are created, and so you have to get the correct ids when you update the “event” attribute.

I hope you can now understand the internals of business rule and how to use process trigger to control its behaviour.

References:

  1. TechNet: Create and edit business rules
  2. MSDN – Create or edit how business rules are initiated

 

 

Quicktip: Linq query with Optionset condition

LinqPad is a great tool to quickly query CRM and export the records you want, for further processing. LinqPad Driver for Dynamics CRM is what this post is about – specifically on how to use “FormattedValues” in your queries. There are formatted values for Money, Optionset and DateTime. I will demonstrate on how to use Optionset text values in where condition.

For example this is how you can query all the open opportunities.

from q in OpportunitySet.AsEnumerable()
where q.FormattedValues["statecode"].ToLower() == "open"
select new { q.CreatedOn, q.Id, StateCodeName = q.FormattedValues["statecode"], q.StateCode }

The critical bit here is the “AsEnumerable”. If you don’t convert your set to an Enumerable, you would get this error.

image

There is a another LinqPad Driver for CRM (https://github.com/kenakamu/CRMLinqPadDriverWebAPI), but you wont be able to run similar query as WebAPI doesn’t seem to be exposing the “FormattedValues” property for each record.

Restricting the customer lookup

First off, I have tested this only in CRMOnline on 8.1.0 and so this might not work for you if you are are in older version. You can now easily restrict the default entity of the lookup, if it links to multiple types. e.g. customer lookup. The script below is for attribute of type “customer”, but you can follow a similar approach for “partylist” as well.

These new functions are undocumented, so they are technically unsupported. So, use this at your own risk. In the script below, I am restricting a field with schemaname “customerid” and of type “customer” to “contact” entity only.

JavaScript

var lookupData = Xrm.Page.getAttribute('customerid').getLookupDataAttribute();

//check if multiple type dropdowns enabled for this lookup and it is not a partylist. For partylist we might want to select an account and a contact
if (lookupData.getSupportedLookupTypes().length > 1
	 && !lookupData.getIsPartyList()
	 && lookupData.getSingleLookupTypeId() !== 2) {
	lookupData.setLookupTypes(['contact'])
}

Screenshots

LookupLookup Search Customer

You can use this script during form load and easily restrict the lookup type.