ian.blair@softstuff

My technical musings

Reading marketing list members from CRM

Marketing lists come in two flavours, static which are a simple list of contacts, accounts or leads and dynamic which are represented by a single query rather than by attaching each record to the list separately. This means if you need to access this information programmatically you have to include an extra step to make sure you get the data out regardless of the list type.

The code to do it is relatively simple and the function below is provided as a starting point.

public EntityCollection GetListMembers(Guid listId, IOrganizationService service)
{
    // listId is the Guid of the marketing list you want to get the members for
    Entity maillist = (Entity)service.Retrieve("list", listId, new ColumnSet(new string[] { "type", "query" }));
    if ((bool)maillist["type"]) // type will be true if it is a dynamic list
    {
        if (maillist.Attributes.Contains("query")) // make sure a query is present
        {
            // query is stored as a FETCHXML query rather than a list of records
            string _FetchQuery = (string)maillist["query"];
            var fetch = new FetchExpression(_FetchQuery);
            // get the results
            EntityCollection er = (EntityCollection)service.RetrieveMultiple(fetch);        
            return er; // return the results
        }
    }
    else
    {
        // start of code to handle static mailing list items
        var query = new QueryExpression("listmember"); // query listmember
        query.ColumnSet.AddColumns(new ColumnSet(new string[] { "entityid", "entitytype" })); // set the columns you want returned
        query.Criteria.AddCondition("listid", ConditionOperator.Equal, listId); // add the listid of the marketing list
        EntityCollection er = (EntityCollection)service.RetrieveMultiple(query);
        return er; // return the results
    }
}

 

The first thing the code does is check the Type field in the list entity. If the value is true then it is a dynamic list, otherwise it is a simple static list.

For a dynamic list the query is held in the query field as a FetchXml query. It is a simple matter you retrieve this and return the results of the query. The query results will contain all the basic information of each entity.

For a static list the links to each record are stored in a N:N relationship entity called listmember and you will be able to return a list of entity types and entity id that you will have to link to the actual entities if you want anything more than a basic list.

Retrieving dates and times from MS CRM in code

One of the things that often catch the unwary especially when they develop in the UK during the winter (i.e. not when daylight saving time is in operation) is that the dates look fine that the retrieve in code from CRM, but suddenly when the clocks go forward their users report issues that appointments are an hour out. The reason for this is that CRM stores all date/times internally in UTC format, and whenever you store/retrieve a date you should perform a conversion to the users time zone.

The advantage of the system storing time/dates that way is that if you put an appointment for a conference call for 5pm and you are in London, the other parties who may be in San Francisco can check their diaries and will see it correctly scheduled for 9am their time.

To use an example here is the code for a simple plugin that takes a date and returns it in formatted text.

protected override void Execute(CodeActivityContext executionContext)
{
    IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();
    IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
    IOrganizationService service = serviceFactory.CreateOrganizationService(context.InitiatingUserId);

    DateTime dt = Date.Get<DateTime>(executionContext);
    dt = RetrieveLocalTimeFromUTCTime(dt, service, context.InitiatingUserId);
    this.DateOut.Set(executionContext, dt.ToString("dd/mm/yyyy"));
}

[Input("Date")]
public InArgument<DateTime> Date { get; set; }


[Output("DateString")]
public OutArgument<string> DateOut { get; set; }

 

The code above is pretty straightforward, but the real work is done in the function RetrieveLocalTimeFromUTCTime.

private DateTime RetrieveLocalTimeFromUTCTime(DateTime utcTime, IOrganizationService service, Guid userid)
{
    // query the timezone code ffrom the CRM users table
    var currentUserSettings = service.RetrieveMultiple(new QueryExpression("usersettings")
    {
        ColumnSet = new ColumnSet("localeid", "timezonecode"),
        Criteria = new FilterExpression
        {
            Conditions = { new ConditionExpression("systemuserid", ConditionOperator.Equal,userid) }
        }
    }).Entities[0].ToEntity<Entity>();
    int? timeZoneCode = (int?)currentUserSettings.Attributes["timezonecode"];
    if (!timeZoneCode.HasValue) throw new Exception("Can't find time zone code");
    // then convert the time from UTC to thelocal time as specified by the timezonecode
    var request = new LocalTimeFromUtcTimeRequest
    {
        TimeZoneCode = timeZoneCode.Value,
        UtcTime = utcTime.ToUniversalTime()
    };
    var response = (LocalTimeFromUtcTimeResponse)service.Execute(request);
    return response.LocalTime;
}

 

This function takes the InitiatingUserId from the execution context of the plugin, and then performs a search in the systemuser table for the time zone for that user. Then it makes a call using the LocalTimeFromUTCTimeRequest/LocalTimeFromUTCResponse function pair to return the correct time. The Guid could come from other sources depending on where the code is to be used.

Forgetting to do this when you write code can lead to some rather embarrassing conversations with your users when the clocks go forward.

A small CRM2015 plugin to create a relationship

Recently needing to provide the ability to create a N:N relationship in a workflow process provided an excuse to create a quick workflow plugin as unfortunately it isn't currently possible to do it out-of-the-box if you have defined a relationship rather than using an intersect table.

Fortunately a plugin to do this is a very simple affair:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Workflow;
using System;
using System.Activities;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
  
namespace Softstuff.Workflow.Relationships
{
    public class CreateManyToManyLink : CodeActivity
    {
        protected override void Execute(CodeActivityContext executionContext)
        {
            IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();
            IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.InitiatingUserId);
 
            EntityReference account = Record1.Get<EntityReference>(executionContext);
            EntityReference new_testentity = Record2.Get<EntityReference>(executionContext);
            EntityReferenceCollection relatedEntities = new EntityReferenceCollection();
 
            // Add the related entity
            relatedEntities.Add(account);
 
            // Add the relationship schema name
            Relationship relationship = new Relationship("new_account_new_testentity");
 
            // Associate the account record to new_testentity
            service.Associate(new_testentity.LogicalName, new_testentity.Id, relationship, relatedEntities);
 
        }
 
        [Input("Account")]
        [ReferenceTarget("account")]
        public InArgument<EntityReference> Record1 { get; set; }
 
        [Input("New_TestEntity")]
        [ReferenceTarget("new_testentity")]
        public InArgument<EntityReference> Record2 { get; set; }
    } 
}

 

The plugin takes two parameters both EntityReferencetypes, and although I had hoped to be able to create a universal plugin that would work for all entities, but unfortunately you have to include the [ReferenceTarget(<entity>)] clause that limits theEntityReference to a single entity type.

For this example I have created a N:N relationship between account and new_testentity and the relationship is called new_account_new_testentity.

The lines in the plugin that actually do the work are:

relatedEntities.Add(account);   // the account entityreference is added to the EntityReferenceCollection.
Relationship relationship = new Relationship(<name of the relationship>);  // define the relationship
service.Associate(EntityReference,LogicalName,EntityReference.Id, relationship, relatedEntities);  // actually create the link

The only other thing to remember before you compile and deploy the plugin is to sign the assembly.

Deploy the assembly using the plugin registration tool and it should appear as a custom workflow step.

Building a plugin for CRM 2015 Won or Lost Opportunities

Recently I had to develop a plugin for a CRM 2015 system that triggered each time the opportunity was modified. This worked fine for the normal day to day edits of the opportunity but it failed to catch when the opportunity was closed as Won or Lost.

Normally I would hook the plugin to the Update message on opportunity with the Plugin Registration Tool but when I tested it I wasn't getting the results I wanted. After some digging around I discovered 2 more messages for opportunities, and these are Win and Lose.

On the face of it this seemed quite simple, just hook the plugin to these messages and its job done. Unfortunately its not quite as simple as that as the messages themselves don't actually pass the opportunity entity when they fire, they actually pass the opportunityclose entity which I have to be honest I am not incredibly familiar with. The good news is I didn't really have to worry about it as one of the attributes of the entity is the opportunityid, so all I had to do was get the passed opportunityclose entity and read the opportunityid attribute then search for the opportunity record with a matching id and then use those values. In fact it sounds more difficult than it actually is. 

Here is some sample code to catch the Win message

public void Execute(IServiceProviderserviceProvider)
{
    Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
    IOrganizationServiceFactoryserviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
    IOrganizationService _service = serviceFactory.CreateOrganizationService(context.UserId);
    if (context.InputParameters.Contains("OpportunityClose"))
    {
        Entity oppCloseEnt = (Entity)context.InputParameters["OpportunityClose"];
        if (oppCloseEnt.Contains("opportunityid"))
        {
            EntityReference er = (EntityReference)oppCloseEnt["opportunityid"];
            // do stuff here
        }
    }
}

 

The good news is that the code is the same for the Lose message.