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!

Set customer rating by workflow

Have you ever been tasked with implementing a system for rating the customer acccounts in MS CRM? Many clients have different rules for rating their MS CRM accounts. The concept is basically that the account is given a rating in different disciplines, and the total determines the general standing of the account.

This is often accomplished in JavaScript, but in my opinion this is a rather poor approach. First of all because I hate JavaScript, but more important because JavaScript only fires when a user opens the account form. There's a lot of scenarios where this isn't sufficient:

• You're importing data from other sources and wants to use them in a campaign without actually open them
• You have lot's and lot's of accounts in your database, but they are seldom opened - in that case you should probably consider some sort of maintenance function
• Your system's integrated with other systems, who might change information without human intervention
• Etc

The code is pretty simple. This example uses hard-coded values of A, B and C, and requieres drop down lists with those values. Perhaps not the most elegant solution - I should probably have read out the values from a settings entity or something in order to avoid the hard coding. On the other hand I have never come across a client who was very inventive in their ratings

Anyway, the following code will accomplish the task:

[CrmWorkflowActivity("Rating")] public class Rating : SequenceActivity { protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext) { IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService)); IWorkflowContext context = contextService.Context; ICrmService crmService = context.CreateCrmService(); //Possible ratings char[] ratings = new char[] { 'A', 'B', 'C' }; //Combinations: string rating = this.CriteriaOne.ToUpper () + this.CriteriaOne.ToUpper () + this.CriteriaOne.ToUpper (); //A equals 1, B equals 2 and C equals 3. Max 9, min 3. 3-5 equals a total A, 6-7 equals B 8-9 equals C //Alternately a single A criteria can generate a total rating of A if this is specified int i = 0; bool A = false; foreach (char c in rating) { if (c == 'A') { i = i + 1; A = true; } if (c == 'B') { i = i + 2; } if (c == 'C') { i = i + 3; } } if(i<6) totalrating = "A"> 5) && (i< totalrating = "B"> 7) { this.TotalRating = "C"; } if (this.ARating.Value) { if (A) { this.TotalRating = "A"; } } return ActivityExecutionStatus.Closed; } public static DependencyProperty CriteriaOneProperty = DependencyProperty.Register ("CriteriaOne", typeof (System.String), typeof (Rating)); [CrmInput("Criteria One")] public string CriteriaOne { get { return base.GetValue (CriteriaOneProperty).ToString (); } set { base.SetValue (CriteriaOneProperty, value); } } public static DependencyProperty CriteriaTwoProperty = DependencyProperty.Register ("CriteriaTwo", typeof (System.String), typeof (Rating)); [CrmInput ("Criteria Two")] public string CriteriaTwo { get { return base.GetValue (CriteriaTwoProperty).ToString (); } set { base.SetValue (CriteriaTwoProperty, value); } } public static DependencyProperty CriteriaThreeProperty = DependencyProperty.Register ("CriteriaThree", typeof (System.String), typeof (Rating)); [CrmInput ("Criteria Three")] public string CriteriaThree { get { return base.GetValue (CriteriaThreeProperty).ToString (); } set { base.SetValue (CriteriaThreeProperty, value); } } public static DependencyProperty ARatingProperty = DependencyProperty.Register ("ARating", typeof (CrmBoolean), typeof (Rating)); [CrmInput ("Return always A if one criteria is A)] public CrmBoolean ARating { get { return (CrmBoolean)base.GetValue (ARatingProperty); } set { base.SetValue (ARatingProperty, value); } } public static DependencyProperty TotalRatingProperty = DependencyProperty.Register ("TotalRating", typeof (System.String), typeof (Rating)); [CrmOutput ("Total Rating")] public string TotalRating { get { return this.GetValue (TotalRatingProperty).ToString (); } set { this.SetValue (TotalRatingProperty, value); } } }

The code reads three different criterias from picklists, evaluates them and returns the average which can be used to generate a total rating. Some clients would like an 'A'-rating in one criteria to override the others, so this is a configurable option.

If you need more criterias than three, just add more properties and assign them other values.

Writing to batch files with workflow

A quite common customer request is the ability to write information to batchfiles for later processing. Integration with print systems are a typical task that usually is done with batch files. Other scenarios might be the necessity to transfer some customer information outside the system to external processing, such as event management and follow-up (If you opt to not use the excellent accelerator) Larger clients often print out hundreds or thousands of identical letters to their clients, and it's quite possible to manage that from MS CRM

Print systems usually use some sort of XML file and/or markup to generate the letter and the information, and you need to adjust the workflow to fit the layout of your print file in order to get this to work. But the concept of writing to a batch file is quite straight forward. This example will write the email address for a contact to a xml file for processing by other systems.

Fire up Visual Studio, create a new class library and add references to all the usual stuff. when you're done, create a class called WriteToEmailAddress. Remember to set the [PersistOnClose]-attribute as we want to make an additional try if this fails. We need to properties, one to hold the contact and one for the filepath.

[CrmWorkflowActivity("Write E-mail address To Batch File" )] [PersistOnClose] public class WriteEmailAddressToBatchFile : Activity { public static DependencyProperty ContactProperty = DependencyProperty.Register("Contact", typeof(Lookup), typeof(WriteEmailAddressToBatchFile)); public static DependencyProperty FilepathProperty = DependencyProperty.Register("Filepath", typeof(string), typeof(WriteEmailAddressToBatchFile)); //Define the properties [CrmInput("Recipient")] [ValidationOption(ValidationOption.Required)] [CrmReferenceTarget("contact")] public Lookup Contact { get { return (Lookup)base.GetValue(ContactProperty); } set { //Validate the argument if (value == null || (value.IsNullSpecified && value.IsNull)) { throw new ArgumentNullException("Contact Lookup cannot be null or have IsNullSpecified = true", ""); } else if (value.type != null && value.type != "contact") { throw new ArgumentNullException("Contact Lookup must be a contact entity", ""); } else if (value.Value == Guid.Empty) { throw new ArgumentException("Contact Lookup must contain a valid Guid", ""); } base.SetValue(ContactProperty, value); } } public string Filepath { get { return base.GetValue(FilepathProperty).ToString(); } set { base.SetValue(FilepathProperty, value); } }

What you need to do now is to do a query in the workflow's execute method in order to retrieve the fields you need to write to the file, and then write it. After this we'll add a couple of method to increase the robustness of the code, such as generation of an xml file if it doesn't exist and some error handling procedure and you're almost there.

protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext) { // Get the context service. IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService)); IWorkflowContext context = contextService.Context; // Use the context service to create an instance of CrmService. ICrmService crmService = context.CreateCrmService(true); try { XmlDocument xmldoc = WriteToFile.LoadFile(this.Filepath, FileType.PrintFile); // Create a new XML-element XmlElement newemail = xmldoc.CreateElement("Email"); // Attribute for ID XmlAttribute emailid = xmldoc.CreateAttribute("ID"); // Generates a new Guid emailid.Value = Guid.NewGuid().ToString(); //attach the attribute to the parent element newemail.SetAttributeNode(emailid); // New subelements XmlElement emailaddress = xmldoc.CreateElement("EmailAddress"); XmlElement date = xmldoc.CreateElement("Date"); // Assigns values to the subelements string _contactid = ""; if (this.Contact != null) { _contactid = this.Contact.Value.ToString(); } else { _contactid = "00000000-0000-0000-0000-000000000000"; } //retrieves values based on GUID BusinessEntityCollection bec = new BusinessEntityCollection(); QueryExpression query = new QueryExpression(); query.EntityName = "contact"; ColumnSet columns = new ColumnSet(); columns.Attributes.Add("emailaddress1"); query.ColumnSet = columns; query.Criteria = new FilterExpression(); query.Criteria.FilterOperator = LogicalOperator.And; ConditionExpression condition1 = new ConditionExpression(); condition1.AttributeName = "contactid"; condition1.Operator = ConditionOperator.Equal; condition1.Values = new object[] { _contactid }; query.Criteria.Conditions.Add(condition1); bec = crmService.RetrieveMultiple(query); contact con = new contact(); con.emailaddress1 = ""; if (bec.BusinessEntities.Count > 0) { con = (contact)bec.BusinessEntities[0]; } emailaddress.InnerText = con.emailaddress1; con = null; date.InnerText = System.DateTime.Now.Date.ToLongDateString(); // attach the child elements to the parent newemail.AppendChild(emailaddress); newemail.AppendChild(date); // Adds the new element to the end of the xml file xmldoc.DocumentElement.InsertAfter(newemail, xmldoc.DocumentElement.LastChild); //Saves the file WriteToFile.SaveFile(xmldoc, this.Filepath); xmldoc = null; } catch (Exception ex) { //Handle errors here; } return base.Execute(executionContext); } }

The last thing we need to do is to add methods to handle xml file loading and saving. If the xml file doesn't exists it will be created.

public class WriteToFile { public static XmlDocument LoadFile(string path) { XmlDocument xmldoc = new XmlDocument(); try { if (!File.Exists(path))//if the file doesnt exists we'll create it at the specified location. { xmldoc = CreateXmlFile(path); } using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { xmldoc.Load(fs); } } catch (Exception ex) { //Handle errors here; } return xmldoc; } private static XmlDocument CreateXmlFile(string path) { XmlDocument xmldoc = new XmlDocument(); try { //let's add the XML declaration section XmlNode xmlnode = xmldoc.CreateNode(XmlNodeType.XmlDeclaration, "", ""); xmldoc.AppendChild(xmlnode); //let's add the root element string rootElementName = "EmailRecipient"; XmlElement xmlelem = xmldoc.CreateElement("", rootElementName, ""); xmldoc.AppendChild(xmlelem); xmldoc.Save(path); System.Diagnostics.EventLog appLog = new System.Diagnostics.EventLog(); appLog.Source = "MS CRM Custom Workflow Activity"; System.Text.StringBuilder sb = new StringBuilder(); sb.AppendLine("Message: A new Batch File was created"); appLog.WriteEntry(sb.ToString(), System.Diagnostics.EventLogEntryType.Information); } catch (Exception ex) { //Handle errors here; } return xmldoc; } public static void SaveFile(XmlDocument xmldoc, string path) { try { using (FileStream fsxml = new FileStream(path, FileMode.Truncate, FileAccess.Write, FileShare.ReadWrite)) { xmldoc.Save(fsxml); } } catch (Exception ex) { //Handle errors here; } } }

That's it! Build the project, register it with the pluginregistration tool, and your workflow is ready to use. You might tailor it so it's executed each time a letter is created for instance, and the contents and address information is written instead

Another thing: Remember that the workflow needs succifient rights on the file it's supposed to write to. Make sure that the Network Service account has sufficient rights to write to that file!