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.

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!