Jul 082010
 

Microsoft Dynamics CRM’s workflow designer is built right into the web-application and provides a way to conditionally perform actions in CRM. With CRM on-premise, custom workflow assemblies can be uploaded to the server and accessed through the web-interface. The CRM Workflow Manipulation Library provides additional workflow actions to perform calculations and manipulate strings.

Two of its most notable capabilities are its ability to perform mathematical equations and phonetic codification solving for duplicate matching.

Sometimes you may want to calculate a percentage based on some of the fields on an opportunity, or you may want to perform a calculation to make a decision on who should work on a particular task. The solve equation workflow allows you to pass parameters and specify a formula for it to solve.

For example:

Workflow Field Value
Formula: min(@a,@b)*@c
@a: 100
@b: 125
@c: 4
 
Result 400

The benefit is that the solver can help you calculate values to include in an email or store data back to CRM. It does more than just basic math and can handle other constants and functions. One of the constants it can handle is rand, which generates a number from 0 to 1 similar to excel, and it handles randbetween to generate a number between two values.

The Manipulation Library contains two phonetic codification systems, SoundEx and a Metaphone-like system. Both of these can be used to convert text to a phonetic codified string that can then be compared for duplicate detection. There are complete instructions on how to configure workflow to perform SoundEx or Metaphone duplicate detection in the documentation.

The CRM Workflow Manipulation Library provides Calculation Utilities, RegEx Utilities, and String Utilities. It is a feature-rich workflow extension that provides the ability to do much more with the data already in your CRM system, and is definitely worth checking out on CodePlex. There are both Microsoft Dynamics CRM 4.0 and Microsoft Dynamics CRM 2011 versions on CodePlex.

May 202010
 

Bing Maps are great. You can geocode and correct your addresses. Depending on your level of queries and types of usage you may need to purchase credits to query Bing Maps, but I thought it was pretty straight forward. If you need to get a Bing Maps account, go here.

The thing that makes this great is it is workflow. It is not tied down to an entity. You can use it on your custom entities, you can geocode your addresses, and correct them. You can do address verification and geocoding. You can control what you do using workflow.

Ok, while this isn’t anything complicated, I thought it was a good example of a custom workflow that used an external webservice. Please don’t forget to create a signed key for your project.

Workflow Class:

using System;
using System.ServiceModel;
using System.Text;
using System.Workflow.Activities;
using System.Workflow.ComponentModel;
using BingWorkflow.BingMapsGeo;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Workflow;
using Microsoft.Win32;

namespace BingWorkflow
{
    [CrmWorkflowActivity("Bing Maps", "Geocode")]
    public class VerifyAddressUsingBingMapsActivity :
        SequenceActivity
    {
        // Activity code goes here. 

        public static DependencyProperty AddressCityProperty = DependencyProperty.Register(
            "AddressCity", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty AddressCountryProperty = DependencyProperty.Register(
            "AddressCountry", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty AddressLine1Property = DependencyProperty.Register(
            "AddressLine1", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty AddressPostalCodeProperty = DependencyProperty.Register(
            "AddressPostalCode", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty AddressStateProperty = DependencyProperty.Register(
            "AddressState", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty BingKeyProperty = DependencyProperty.Register(
            "BingKey", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputCityProperty = DependencyProperty.Register(
            "OutputCity", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputCountryProperty = DependencyProperty.Register(
            "OutputCountry", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputLatitudeProperty = DependencyProperty.Register(
            "OutputLatitude", typeof (CrmFloat), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputLine1Property = DependencyProperty.Register(
            "OutputLine1", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputLongitudeProperty = DependencyProperty.Register(
            "OutputLongitude", typeof (CrmFloat), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputMultipleFoundProperty = DependencyProperty.Register(
            "OutputMultipleFound", typeof (CrmBoolean), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputPostalCodeProperty = DependencyProperty.Register(
            "OutputPostalCode", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        public static DependencyProperty OutputStateProperty = DependencyProperty.Register(
            "OutputState", typeof (string), typeof (VerifyAddressUsingBingMapsActivity));

        [CrmInput("Address City")]
        public string AddressCity
        {
            get { return (string)GetValue(AddressCityProperty); }
            set { SetValue(AddressCityProperty, value); }
        }

        [CrmInput("Address Country")]
        public string AddressCountry
        {
            get { return (string)GetValue(AddressCountryProperty); }
            set { SetValue(AddressCountryProperty, value); }
        }

        [CrmInput("Address Line 1")]
        public string AddressLine1
        {
            get { return (string)GetValue(AddressLine1Property); }
            set { SetValue(AddressLine1Property, value); }
        }

        [CrmInput("Address Postal Code")]
        public string AddressPostalCode
        {
            get { return (string)GetValue(AddressPostalCodeProperty); }
            set { SetValue(AddressPostalCodeProperty, value); }
        }

        [CrmInput("Address State Or Province")]
        public string AddressState
        {
            get { return (string)GetValue(AddressStateProperty); }
            set { SetValue(AddressStateProperty, value); }
        }

        [CrmInput("Bing API Key")]
        public string BingKey
        {
            get { return (string)GetValue(BingKeyProperty); }
            set { SetValue(BingKeyProperty, value); }
        }

        [CrmOutput("City")]
        public string OutputCity
        {
            get { return (string)GetValue(OutputCityProperty); }
            set { SetValue(OutputCityProperty, value); }
        }

        [CrmOutput("Country Or Region")]
        public string OutputCountry
        {
            get { return (string)GetValue(OutputCountryProperty); }
            set { SetValue(OutputCountryProperty, value); }
        }

        [CrmOutput("Latitude")]
        public CrmFloat OutputLatitude
        {
            get { return (CrmFloat)GetValue(OutputLatitudeProperty); }
            set { SetValue(OutputLatitudeProperty, value); }
        }

        [CrmOutput("Line 1")]
        public string OutputLine1
        {
            get { return (string)GetValue(OutputLine1Property); }
            set { SetValue(OutputLine1Property, value); }
        }

        [CrmOutput("Longitude")]
        public CrmFloat OutputLongitude
        {
            get { return (CrmFloat)GetValue(OutputLongitudeProperty); }
            set { SetValue(OutputLongitudeProperty, value); }
        }

        [CrmOutput("Multiple Address Found")]
        public CrmBoolean OutputMultipleFound
        {
            get { return (CrmBoolean)GetValue(OutputMultipleFoundProperty); }
            set { SetValue(OutputMultipleFoundProperty, value); }
        }

        [CrmOutput("Postal Code")]
        public string OutputPostalCode
        {
            get { return (string)GetValue(OutputPostalCodeProperty); }
            set { SetValue(OutputPostalCodeProperty, value); }
        }

        [CrmOutput("State Or Province")]
        public string OutputState
        {
            get { return (string)GetValue(OutputStateProperty); }
            set { SetValue(OutputStateProperty, value); }
        }

        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
        {
            // Reset Outputs
            OutputLatitude = new CrmFloat();
            OutputLongitude = new CrmFloat();
            OutputLine1 = String.Empty;
            OutputCity = String.Empty;
            OutputState = String.Empty;
            OutputPostalCode = String.Empty;
            OutputMultipleFound = new CrmBoolean {Value = false};

            var key = BingKey;
            if (String.IsNullOrEmpty(key) || key.ToLower() == "registry")
            {
                key = GetAPIKeyFromRegistry();
            }

            // Create Bing Maps Request
            var bingRequest = new GeocodeRequest
                              {
                                  Credentials = new Credentials {ApplicationId = key},
                                  Query = AddressLine1 + ", " +
                                          AddressCity + ", " +
                                          AddressState + ", " +
                                          AddressPostalCode + ", " +
                                          AddressCountry
                              };

            // Filter for High Confidence
            var filters = new ConfidenceFilter[1];
            filters[0] = new ConfidenceFilter {MinimumConfidence = Confidence.High};
            bingRequest.Options = new GeocodeOptions {Filters = filters};

            // Create Client
            GeocodeServiceClient bingClient = null;
            try
            {
                var geoBinding = new BasicHttpBinding(BasicHttpSecurityMode.None)
                                 {
                                     Name = "BasicHttpBinding_IGeocodeService",
                                     CloseTimeout = new TimeSpan(0, 1, 0),
                                     OpenTimeout = new TimeSpan(0, 1, 0),
                                     ReceiveTimeout = new TimeSpan(0, 10, 0),
                                     SendTimeout = new TimeSpan(0, 1, 0),
                                     AllowCookies = false,
                                     BypassProxyOnLocal = false,
                                     HostNameComparisonMode = HostNameComparisonMode.StrongWildcard,
                                     MaxBufferSize = 65536,
                                     MaxBufferPoolSize = 524288,
                                     MaxReceivedMessageSize = 65536,
                                     MessageEncoding = WSMessageEncoding.Text,
                                     TextEncoding = Encoding.UTF8,
                                     TransferMode = TransferMode.Buffered,
                                     UseDefaultWebProxy = true
                                 };

                var geoEA =
                    new EndpointAddress("http://dev.virtualearth.net/webservices/v1/geocodeservice/GeocodeService.svc");

                bingClient = new GeocodeServiceClient(geoBinding, geoEA);

                var bingResponse = bingClient.Geocode(bingRequest);

                if (bingResponse.Results != null && bingResponse.Results.Length > 0)
                {
                    if (bingResponse.Results.Length > 1)
                    {
                        OutputMultipleFound.Value = true;
                    }

                    var bingResult = bingResponse.Results[0];

                    if (bingResult.Locations != null && bingResult.Locations.Length > 0)
                    {
                        var bingLocation = bingResult.Locations[0];
                        OutputLatitude.Value = bingLocation.Latitude;
                        OutputLongitude.Value = bingLocation.Longitude;
                    }

                    var bingAddress = bingResult.Address;

                    OutputLine1 = bingAddress.AddressLine;
                    OutputPostalCode = bingAddress.PostalCode;
                    OutputCountry = bingAddress.CountryRegion;

                    OutputCity = String.IsNullOrEmpty(bingAddress.PostalTown)
                                     ? bingAddress.Locality
                                     : bingAddress.PostalTown;
                    OutputState = String.IsNullOrEmpty(bingAddress.AdminDistrict)
                                      ? bingAddress.District
                                      : bingAddress.AdminDistrict;
                }
            }
            catch (Exception ex)
            {
                throw new WorkflowTerminatedException(
                    "There was an error openning the connection to Bing Maps.", ex);
            }
            finally
            {
                // Close the client
                if (bingClient != null)
                {
                    bingClient.Close();
                }
            }


            return base.Execute(executionContext);
        }

        private static string GetAPIKeyFromRegistry()
        {
            // Opening the registry key
            var key = Registry.LocalMachine;
            if (key != null)
            {
                key = key.OpenSubKey("Software\\Microsoft Geocoder");
            }

            return key != null ? key.GetValue("APIKey").ToString() : null;
        }
    }
}
Feb 122009
 

CRM 4.0has an endless amounts of system job logs. We perform automatic actions on a lot of entities, but we don’t need the log of those automatic actions beyond the end of that working day if it was Canceled or Completed Successfully. Unfortunately, setting up a bulk delete task for system job logs is not as easy as setting up other bulk delete jobs. I modified the code from Sean McNellis’ blog entry on leveraging bulk delete jobs to manage system job log records.

Below is my code (which is a modified version of the runBulkDelete from the Microsoft Bulk Delete Sample for deleting successfully completed or canceled jobs:

        static void runBulkDeleteAsyncoperations(CrmService service)
        {
            // Create a query expression using the QueryExpressionHelper
            QueryExpressionHelper expression = new QueryExpressionHelper("asyncoperation");
            expression.Columns.AddColumn("asyncoperationid"); 
            expression.Criteria.Conditions.AddCondition("statecode", 
                        ConditionOperator.Equal, 
                        (int)AsyncOperationState.Completed);
            expression.Criteria.Conditions.AddCondition("statuscode", 
                        ConditionOperator.Equal, 
                        new object[]{(int)AsyncOperationStatus.Canceled,(int)AsyncOperationStatus.Succeeded});
            Guid[] emptyRecipients = new Guid[0];
            BulkDeleteRequest request = new BulkDeleteRequest();
            request.JobName = "Bulk Delete Successfully Completed or Canceled Asyncoperations and Workflow Logs";
            request.QuerySet = new QueryBase[] { expression.Query };
            request.ToRecipients = emptyRecipients;
            request.CCRecipients = emptyRecipients;
            request.SendEmailNotification = false;
            request.RecurrencePattern = "FREQ=DAILY;INTERVAL=1;";
            request.StartDateTime = CrmDateTime.Now;

            BulkDeleteResponse response = (BulkDeleteResponse)service.Execute(request);

            Console.WriteLine("Bulk delete job with id: {0} has been created", response.JobId);
        }

Their code examples use a month time span, and if you are trying to delete everything older than 3 days, then you need to build an executable and schedule it to run daily to create the bulk deletion job. You will also need the following filter to be added to the code, and set the RecurrencePattern equal to string.empty.

expression.Criteria.Conditions.AddCondition("completedon", ConditionOperator.OnOrBefore, DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd"));

If by some chance you are curious about creating Bulk Delete Jobs in general, check out msdn blog entry.