Execute Query - Activity in MS CRM Workflow - yes, it's possible!

One thing lacking from the standard set of workflow activities is a Query Activity. Especially if I have to work with a heavily customized data model, retrieving values can often be quite a pain. Often I need to include or work on such values in a business process, and I prefer to use workflow for just that. Another limitation is that there's just a subset of the relations that's available for a given record when that record is used in a workflow.

Consider the following example: A CRM user creates a new case on a customer account. The client has requested that whenever a case is created, a search for certain information should be conducted. That could be something completely unrelated to the case, say a custom entity that holds information about projects the client has done for this customer.

How do you facilitate that? The case is related to the customer account, and the customer account is related to the custom project entity, but there's no way that custom entity is accessible to the workflow if the workflow is triggered by the creation of the case. You'll only get the customer account information, nothing deeper than that.

So you have to write some sort of plugin business logic that executes a query and updates some information. Developing for MS CRM means writing a lot of boring queries, so you're probably used to this. But it'll mean several hours spent testing, debugging and rewriting that plugin - hours you could spend on something else.

How? Just write a simple Query Activity to use with your workflow. It's quite simple, and wont solve every need, but I guess it'll take you quite far in eliminating the need to write all those boring little queries.

The consept is to use a workflow with four input properties. These four properties will take information about your query, execute it, and return an output string property with the result. The main limitations are that you can only retrieve a single value for an attribute, and there's no support for a linked entity (Though it would be quite easy to implement it). Furthermore, you can't specify the output type (But since it's string you can reuse it in other queries).

The custom workflow activity utilizes the DynamicEntity-y class so you can use this on any entity you want. It will also do a cast based on the property type of the result, and return the text for Picklist and Status, and a Guid-string for Key and Owner. The rest of the types vil just convert the value to a string.

In order to make this custom workflow activity, start up Visual Studio, create a new class library, reference the dll's and the System.Workflow - namespaces, and paste in the following code:

[CrmWorkflowActivity("Query Activity")]
public class WorkflowQuery: Activity
{
public static DependencyProperty entitynameProperty = DependencyProperty.Register("entityname", typeof(string), typeof(WorkflowQuery));
[CrmInput("Entity Name")]
[CrmDefault("account")]
public string entityname
{
get {return base.GetValue(entitynameProperty).ToString(); }
set { base.SetValue(entitynameProperty, value); }
}
public static DependencyProperty attributenameProperty = DependencyProperty.Register("attributename", typeof(string), typeof(WorkflowQuery));
[CrmInput("Attribute To Retrieve")]
[CrmDefault("statuscode")]
public string attributename
{
get { return base.GetValue(attributenameProperty).ToString(); }
set { base.SetValue(attributenameProperty, value); }
}
public static DependencyProperty conditionattributenameProperty = DependencyProperty.Register("conditionattributename", typeof(string), typeof(WorkflowQuery));
[CrmInput("Condition attribute")]
[CrmDefault("name")]
public string conditionattributename
{
get { return base.GetValue(conditionattributenameProperty).ToString(); }
set { base.SetValue(conditionattributenameProperty, value); }
}
public static DependencyProperty conditionvalueProperty = DependencyProperty.Register("conditionvalue", typeof(string), typeof(WorkflowQuery));
[CrmInput("Condition value")]
[CrmDefault("Example account name")]
public string conditionvalue
{
get { return base.GetValue(conditionvalueProperty).ToString(); }
set { base.SetValue(conditionvalueProperty, value); }
}
public static DependencyProperty resultProperty = DependencyProperty.Register("result", typeof(string), typeof(WorkflowQuery));
[CrmOutput("Result")]
[CrmDefault("")]
public string result
{
get { return base.GetValue(resultProperty).ToString(); }
set { base.SetValue(resultProperty, value); }
}
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService));
IWorkflowContext context = contextService.Context;

ICrmService crmService = context.CreateCrmService();

QueryExpression query = new QueryExpression();

query.EntityName = this.entityname;

ColumnSet columns = new ColumnSet();
columns.Attributes.Add(this.attributename);
query.ColumnSet = columns;


query.Criteria = new FilterExpression();
query.Criteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = this.conditionattributename;
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new object[] { this.conditionvalue};

query.Criteria.Conditions.Add(condition1);

RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();
retrieve.Query = query;
retrieve.ReturnDynamicEntities = true;
RetrieveMultipleResponse retrieved = (RetrieveMultipleResponse)crmService.Execute(retrieve);

DynamicEntity entity = (DynamicEntity)retrieved.BusinessEntityCollection.BusinessEntities[0];

if (entity.Properties[this.attributename] is Status)
{
Status prop = (Status)entity.Properties[this.attributename];
this.result = prop.name.ToString();
}
else if (entity.Properties[this.attributename] is CrmNumber)
{
CrmNumber prop = (CrmNumber)entity.Properties[this.attributename];
this.result = prop.Value.ToString();
}
else if (entity.Properties[this.attributename] is CrmDateTime)
{
CrmDateTime prop = (CrmDateTime)entity.Properties[this.attributename];
this.result = prop.Value;
}
else if (entity.Properties[this.attributename] is CrmMoney)
{
CrmMoney prop = (CrmMoney)entity.Properties[this.attributename];
this.result = prop.Value.ToString();
}
else if (entity.Properties[this.attributename] is Key)
{
Key prop = (Key)entity.Properties[this.attributename];
this.result = prop.Value.ToString();
}
else if (entity.Properties[this.attributename] is Picklist)
{
Picklist prop = (Picklist)entity.Properties[this.attributename];
this.result = prop.name;
}
else if (entity.Properties[this.attributename] is Owner)
{
Owner prop = (Owner)entity.Properties[this.attributename];
this.result = prop.Value.ToString();
}
else //stringproperty
{
String prop = (String)entity.Properties[this.attributename];
this.result = prop;
}
return ActivityExecutionStatus.Closed;

}
}

This code will first build your query by setting the name of the entity to be queried, and the attribute you wish to retrieve. Then you have to specify a condition attribute and a value for that.

The activity will the use this information to generate a query object, which in turn is queried and the results retrieved as dynamic entitties. The attribute of the entity you wish to retrieve is the checked for type and cast appropriatly in order to set the value for the output property, result.

You can use this activity in your workflow, and incorporate the result in other workflow activities.

A potential development of this activity could be support for linked entities, ability to specify the type of the output property and reference target (Such as lookup for account, for instance), and perhaps a special variety for those dreadful N:N - relationships.

Deactivate accounts with no activity for the last two years

Sometimes you need to write maintenance operations that operates on the entire database, not just a single record. MS CRM does not support such operations out-of-the-box, but it's quite easy to accomplish anyway.

Some MS CRM customers operate with very large datasets with lots and lots of accounts. Most of those accounts will be inactive, meaning that there's been no activity with them for a given amount of time.

In those cases the client might want to differentiate between those accounts who have an active relation with the client, and those who hasn't. This makes it easy to maintain the overview over the clients actual accounts, and also conduct datamining and filtering on the others in order to do antichurn campaigns, prospecting, etc.

To achieve this it might be feasible with some sort of mechanism that deactivates an account if there has been no activity for a given amount of time. In my other posting I explained how to set up a recurring workflow for maintenance, and this custom workflow activity could easily be integrated in such an workflow.

The workflow is quite simple. It's basically just a query that retrieves all customer accounts where the related activities haven't been modified for the last 24 months. This is just a sample criteria, you could just as easily used the modifiedon-attribute on the account entity or some other sort of criteria.

After all the accounts that meet this criteria are retrieved, the businessentitycollection that contains those accounts are enumerated and the statecode is set to inactive. Each account is then updated:

[CrmWorkflowActivity("Account maintenance")] public class AccountMaintenanceWorkflow: Activity { protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext) { IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService)); IWorkflowContext context = contextService.Context; ICrmService crmService = context.CreateCrmService(); QueryExpression query = new QueryExpression(); query.EntityName = "account"; ColumnSet columns = new ColumnSet(); columns.Attributes.Add("statecode"); query.ColumnSet = columns; LinkEntity linkEntity1 = new LinkEntity(); linkEntity1.JoinOperator = JoinOperator.Natural; linkEntity1.LinkFromEntityName = "account"; linkEntity1.LinkFromAttributeName = "accountid"; linkEntity1.LinkToEntityName = "activitypointer"; linkEntity1.LinkToAttributeName = "regardingobjectid"; linkEntity1.LinkCriteria = new FilterExpression(); linkEntity1.LinkCriteria.FilterOperator = LogicalOperator.And; ConditionExpression condition1 = new ConditionExpression(); condition1.AttributeName = "modifiedon"; condition1.Operator = ConditionOperator.OnOrBefore; condition1.Values = new object[] { System.DateTime.Today.AddYears(-2).ToString() }; linkEntity1.LinkCriteria.Conditions.Add(condition1); query.LinkEntities.Add(linkEntity1); query.LinkEntities.Add(linkEntity1); AccountStateInfo accStateInfo = new AccountStateInfo(); accStateInfo.Value = AccountState.Inactive; foreach (account acc in crmService.RetrieveMultiple(query).BusinessEntities) { acc.statecode = accStateInfo; crmService.Update(acc); } return ActivityExecutionStatus.Closed; } }

Just create the usual class library, reference all the usual stuff, and paste in the class. When you're done, compile and register with pluginregistration.exe.

When you've registered, you can incorporate this custom workflow activity with the recurring workflow example.

By the way, this activity will typically take some time to complete since it involves potentially a lot of updates. Make sure it only runs at night!

How to schedule maintenance jobs with MS CRM

Quite often a need to do regular maintenance jobs on the data in MS CRM arises. It might be that the client requests some sort of regular data processing or that customer accounts should be evaluated each month. For instance one might want to deactivate customer accounts who hasn't prchased anything the last 24 months or so. You might handle this with a filter, but it's quite nice to have the ability to automatically deactive records based on explicit criterias.

MS CRM does not support such monitoring and scheduling jobs out-of-the-box, but it is however quite easy to do this through customization

Creating the maintenance job entity
First, you need a new entity to hold information on the maintenance jobs. Create an new entity, scope it to organization, and name it "maintenance jobs". Add the following attributes:

• A picklist attribute called jobtype - this will hold the different types of jobs you need to do. Add at least one value called "Deactivate accounts older than 24 months". This attribute is required
  
• A dateTime attribute called startdate. If you want to be able to run the job at specific times, choose "date and time" - format. Required
  
• A dateTime attribute called enddate. Optional
  
• A picklist attribute called recurrence - assign the values daily, weekly and monthly or other values you might need. Required
  
• A nText attribute called description in case you need to describe each job
• Keep the name attribute as it is

When you're done. the new entity should look like this:












Create the self-referencing workflows
The next step is to create workflows that is triggered by this entity, and which is able to call each other recurrently. You'll need two workflows for that job. One that's triggered by the creation, and another that is started by the first one which main task is to wait until the next recurrence and trigger the first one.

Be aware that there's a continous loop detection in MS CRM which restrains workflows from triggering themselves more than 7 times per hour or so

Create the first workflow, call it WF_MaintenanceJobStarter or something like that. Scope it to organisation, make sure it triggers on create for the maintenancejob entity and that it is also available to run as a child workflow

The workflow must contain the following activities

1. A Wait activity that waits until it's on or after staring time for the maintenance job
2. A Check Condition activity that checks the job type of the maintenance job
3. A Start Child Workflow activity that starts the looping activity. This isn't defined yet, so just leave this empty
4. A check Condtion activity that checks if the maintenancejob has an end date
5. If it has an end date a Wait activity will wait until that day and stop the workflow

This image shows the different activities that the workflow must have. Note that it calls the other workflow that isn't defined yet - you must do this in to steps. First you must define and save both workflows, then you must have them call each other



















The second workflows main task is to wait out the appropriate time established by the recurrence attribute before it calls the first workflow. You might have other recurrence patterns than those below, but remember that a workflow can't call itself more than 7 times per hour

You'll need to define the following workflow:

• Name: WF_ MaintenanceJobLooper. Scope: Organisation, Entity: maintenance job
• Make sure that it is available as a child workflow
• Define a Check Condition activity with three branches, one for each type of recurrence. Dail, weekly or monthly
• Underneath each Check Condition activity add a Wait Activity that waits a day, a week or a month after workflow execution time
• When the waiting activity is done, a Start child Workflow Activity is fired. This activity starts WF_MaintenanceJobStarter

When you're done defining this workflow you might need to add the workflow to point 3 of WF_MaintenanceJobStarter. The finished workflow will look like this:























Now you have defined a workflow that calls it self recurrently based on the intervals you define in the maintenance job records. But it doesn't actually do anything. You need to add activities that will do the necessary maintenance for you at these intervals. Usually you'll need to code these as custom workflow activities, especially if you want to do maintenance on the entire database.

Another neat thing to remember is that when you add those activities make sure to call the looping workflow before that activity is executed. That way the maintenance job want halt if something goes wrong with your activity

You'll find a suitable custom workflow activity for this kind of job in this post.