Three 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 s in SavedQuerySet.AsEnumerable()
where s.QueryType == 0 && !s.IsManaged.Value && !s.CanBeDeleted.Value
orderby s.ReturnedTypeCode
group s by new {
Entity = s.ReturnedTypeCode,
CreatedBy = s.CreatedBy.Name
} into g
select g.Key)

entity-by-user

Query 2 – Entity creation timestamp

This is basically similar to the previous query, but this displays the createdon timestamp for the specified entity.

(from s in SavedQuerySet
where s.QueryType == 0 && s.ReturnedTypeCode == "[ENTITYNAME]"
orderby s.CreatedOn
select new { CreatedBy = s.CreatedBy.Name, CreatedOn = s.CreatedOn.Value.ToLocalTime(), s.ReturnedTypeCode }).Take(1)

Entity Creation Date.png

Query 3 – 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

 

 

Issues in debugging custom workflow assemblies

Three months ago, I wrote a post about profiling workflows with custom activity step -> Debugging custom workflow assemblies

There were couple of comments in the post, about it not working as expected due to

  1. Different version of CRM and/or
  2. Different version of Plugin Registration tool

I will now give you the steps to get this working.

  1. Install LINQPad -> https://www.linqpad.net
  2. Install CRM Driver for LINQPad -> https://crmlinqpad.codeplex.com/
  3. After creating a connection to your CRM instance, run this LINQ query
    XNamespace mxswa = "{clr-namespace:Microsoft.Xrm.Sdk.Workflow.Activities;assembly=Microsoft.Xrm.Sdk.Workflow, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35}";
    var query = from w in WorkflowSet.AsEnumerable()
    where
    w.Name == "[YOUR WORKFLOW NAME WITH CUSTOM STEP]" &&
    w.FormattedValues["type"] == "Definition"
    && w.IsCrmUIWorkflow.GetValueOrDefault()
    orderby w.ModifiedOn descending
    select new {
    w.Id,
    w.Name,
    WorkflowVersion = XElement.Parse(w.Xaml).Attributes().FirstOrDefault(a => a.Name.LocalName == "mxswa").Value.Split(';')[1],
    CustomSteps = from a in XElement.Parse(w.Xaml).Descendants($"{mxswa}ActivityReference")
    			   where !a.Attribute("AssemblyQualifiedName").Value.Contains("Microsoft.Crm")
    			  select new {
    			  CustomStepName = a.Attribute("DisplayName").Value,
    			  AssemblyName = a.Attribute("AssemblyQualifiedName").Value,
    			  HasArguments = a.Descendants($"{mxswa}ActivityReference.Arguments").Any()}
    };
    query.Where(c=>c.CustomSteps.Any()).Dump();
    
  4. You’ll get something like this. Note that the workflow in my case is “Blank Workflow with Custom Step”. Change this to the one you are trying to profile.Query

Issue 1: Cannot see the workflow step.

Blank Step

If you cannot see the workflow step, that means that there is a mismatch between the Microsoft.Xrm.Sdk.Workflow.dll version and/or your custom workflow step version. Go the folder with the Plugin Registration tool and check the version of the Microsoft.Xrm.Sdk.Workflow.dll version. The important thing is if the major version or minor version of the Microsoft.Xrm.Sdk.Workflow.dll assembly in Plugin Registration tool folder, is different from the one in the Workflow XAML, you will not see the step displayed.

CRM 2016 Plugin Reg Tool

CRM 2016 Plugin Registration Tool

CRM 2016 Update 1 Plugin Reg Tool.png

CRM 2016 Update 1 Plugin Registration Tool

Compare this with the results of the LINQ query and confirm that the major version and minor version match. Do this same for your custom workflow assembly as well.

Issue 2: NullReferenceException

There is a bug in the Plugin Registration Tool, that doesn’t allow you to profile workflow steps, that doesn’t have any argument. If you try to do this, you will get this exception.

Null Reference.png

The workaround for this is to add a dummy argument, to keep the plugin profiler happy.

Workflow

Hope this helps with your debugging efforts.

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.

Bug: Entity primary field & realtime workflows

When you create a new entity, one of the fields that is generally left unchanged is the primary field for the entity. It is usually “[publisherprefix]_name”.

image

This field can be either required or left optional. This value of this field, is what shows up in the entity lookups. The value of this field is usually set automatically using JavaScript, Workflow or Plugin. If the value for this field is left null, it manifests a bug in the realtime workflow.

What is the bug?

If the value of the primary field is null on a parent entity, any child entity can’t use “if condition” on the parent relationship field. It would result in a “KeyNotFound” exception.

Replication steps

  1. Create an entity that will be the parent entity
  2. Create an entity that will be the child. Create a relationship to the parent entity image
  3. Make the primary field on the parent entity as optional image
  4. Create a new realtime workflow on the child entity, with a condition statement that refers the parent lookup field in the child entity image
  5. Create a parent record without the name field setimage
  6. Create a child record that has the parent lookup set the record created in the previous step. The parent lookup will be displayed as “no name” as the primary field for the parent entity is not set.image
  7. Execute the realtime workflow against the child record in the previous step. A “The given key was not present in the dictionary” exception will be displayedimage

The issue is happening both in CRM 2015 and CRMOnline on version 8.1.0.362. This issue seems to be only affecting realtime workflows and actions, and not background workflows.

EDIT (24/06/2016): Issue logged on Connect -> https://connect.microsoft.com/site687/feedback/details/2851391/realtime-workflow-action-crash-display-name-if-condition-keynotfoundexception

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.

 

Bug: EntityReference, Create and latebinding

For big projects and large code sizes, it is recommended to use strongly typed classes when you are dealing with CRUD operations related to the CRM entities. You can use Early Bound Generator or “crmsvcutil” to do this. The advantages are compile type errors for mismatches types and intellisense. But if you are quickly testing out some code, latebinding is the easiest path, as it doesn’t involve the overhead of generating the classes. During this process, I discovered a bug.

Here is the code and I will explain the bug after that.

            CrmServiceClient crmSvc = new CrmServiceClient(ConfigurationManager.ConnectionStrings["CRMConnectionString"].ConnectionString);
            if (crmSvc.IsReady)
            {
                #region create contact with account entityreference

                var account = new Entity("account");
                account["name"] = "Test Account";
                account.Id = crmSvc.OrganizationServiceProxy.Create(account);
                Console.WriteLine($"Created account with id {account.Id}");

                Console.WriteLine("Creating contact with account as EntityReference");
                Console.WriteLine("----------------------------------------------------");

                var contact = new Entity("contact");
                contact["parentcustomerid"] = account.ToEntityReference();
                contact.Id = crmSvc.OrganizationServiceProxy.Create(contact);
                Console.WriteLine($"Created contact with id {contact.Id}");

                contact = crmSvc.OrganizationServiceProxy.Retrieve("contact", contact.Id, new ColumnSet("parentcustomerid"));
                Console.WriteLine($"Contact: Account Lookup {contact.GetAttributeValue<EntityReference>("parentcustomerid")?.Id}");

                #endregion

                #region update contact with account Guid

                Console.WriteLine("Updating contact with account as Guid");
                Console.WriteLine("----------------------------------------------------");
                try
                {
                    contact["parentcustomerid"] = account.Id;
                    crmSvc.OrganizationServiceProxy.Update(contact);
                    var updateContact = crmSvc.OrganizationServiceProxy.Retrieve("contact", contact.Id, new ColumnSet("parentcustomerid"));
                    Console.WriteLine($"Updated Contact: Account Lookup {updateContact.GetAttributeValue<EntityReference>("parentcustomerid")?.Id}");
                }
                catch (Exception e)
                {
                    Console.WriteLine($"Update Contact failed with Exception: {e.Message}");
                }

                crmSvc.OrganizationServiceProxy.Delete("contact", contact.Id);
                Console.WriteLine($"Deleted contact with id {contact.Id}");

                #endregion

                #region create contact with account Guid

                Console.WriteLine("\nCreating contact with account as Guid");
                Console.WriteLine("----------------------------------------------------");
                contact = new Entity("contact");
                contact["parentcustomerid"] = account.Id;
                //This should not succeed
                contact.Id = crmSvc.OrganizationServiceProxy.Create(contact);
                Console.WriteLine($"Created contact with id {contact.Id}");

                contact = crmSvc.OrganizationServiceProxy.Retrieve("contact", contact.Id, new ColumnSet("parentcustomerid"));
                Console.WriteLine($"Contact: Account Lookup {contact.GetAttributeValue<EntityReference>("parentcustomerid")?.Id}");

                crmSvc.OrganizationServiceProxy.Delete("contact", contact.Id);
                Console.WriteLine($"Deleted contact with id {contact.Id}");

                crmSvc.OrganizationServiceProxy.Delete("account", account.Id);
                Console.WriteLine($"\nDeleted account with id {account.Id}");

                #endregion

            }

Here is the result.

Guid Entityreference result

Even if you are using latebinding, CRM webservice respects type and doesn’t allow you to set an attribute to a type, that doesn’t match its definition, except in one case: You can set the EntityReference attribute to a Guid, and the code with execute without any exception. The good thing though is the fields is just set to null, and the Guid is ignored, but this a bug as you cannot do it with other types e.g. you can’t set an Optionset field to an int or a Currency field to a decimal.

I have logged this on Connect for verification. I experienced this behaviour in CRMOnline and CRM2015.

Connect issue -> https://connect.microsoft.com/site687/feedback/details/2740732/sdk-permits-setting-entityreference-attribute-to-guid-late-binding

Further reading:

MSDN: Create, retrieve, update, and delete (late bound)