ian.blair@softstuff

My technical musings

Getting OptionSet values out of CRM2015 with C#

Every so often when you are reading a record in code the integer values from the OptionSet fields arent enough because you want to display the whole of the data contained in the record and make it fit for user consumption.

Unfortunately the only way is to pull out the OptionSet values from the fields separately and then do the comparison in code to find the correct text value, but making your own interfaces multilingual or populating your own lists for record creation then becomes quite straightforward.

A function like this will pull out a List of KeyValuePair containing the numeric value of the OptionSetValue and the text value local to the user who is running the code. It is quite easy to extend this to only pull out the text values for a specific user locale.

public static List<KeyValuePair<int, string>> getOptionSetText(IOrganizationService service, string entityName, string attributeName)
{
    List<KeyValuePair<int, string>> values = new List<KeyValuePair<int, string>>();
    RetrieveAttributeRequest retrieveAttributeRequest = new RetrieveAttributeRequest();
    retrieveAttributeRequest.EntityLogicalName = entityName;
    retrieveAttributeRequest.LogicalName = attributeName;
    retrieveAttributeRequest.RetrieveAsIfPublished = false;
    RetrieveAttributeResponse retrieveAttributeResponse = (RetrieveAttributeResponse)service.Execute(retrieveAttributeRequest);
    PicklistAttributeMetadata picklistAttributeMetadata = (PicklistAttributeMetadata)retrieveAttributeResponse.AttributeMetadata;
    OptionSetMetadata optionsetMetadata = picklistAttributeMetadata.OptionSet;

    foreach (OptionMetadata optionMetadata in optionsetMetadata.Options)
    {
        if (optionMetadata.Value.HasValue)
        {
            values.Add(new KeyValuePair<int, string>(optionMetadata.Value.Value, optionMetadata.Label.UserLocalizedLabel.Label));
        }
    }
    return values;
}

To retrieve localised OptionSetValue text instead of using optionMetaData.Label.UnserLocalizedLabel.Label you could substitute it for something like

foreach (LocalizedLabel l in optionMetadata.Label.LocalizedLabels)
{
	string labelText = l.Label;
	int lang = l.LanguageCode;
}

 

This will loop around all the localised values to extract the text and the locale id code.

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.

Using Cryptographic random numbers in Windows Universal applications C#

When building Universal applications the full set of .NET libraries aren't available and one of my favourites for generating Random numbers System.Security.Cryptography isnt available anymore. Fortunately this has been replaced with the Windows.Security.Cryptography library and although the functions don't correspond with the old library there is an easy way to generate random numbers.

using Windows.Security.Cryptography;

public Unit32 RandomNumber(UInt32 min, UInt32 max)
{
return min+(CryptographicBuffer.GenerateRandomNumber() % (max min))
// find the range of numbers needed (max min) example: 10 - 30 = 20
// then mod the random number with the range example: rnd % 20 
// then add it to the minimum value example: 10 + modded rnd
}

 

This function seems to give a pretty even spread of random numbers and is called by:

UInt32 rnd=RandomNumber(1,59);

The example above will generate one of the UK Lotto numbers.

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.

Using the CLR to create a Random Number generator

Using the CLR to create a Random Number generator

 

Following on from my last post about a random number generator in SQL, another method of providing a random number generator would be to add the function using C# and the Common Language Runtime, and fortunately the tools to do this are now installed with SQL Server.

We could do this using Visual Studio but for a small function like this we can do it all with notepad and the command line compiler installed with .net.

The first thing to do is check that the compiler is accessible and that the path to the csc.exe file is included in your path statement. Open a CMD window and at the prompt type CSC. If you see a message like Microsoft visual C# compiler version x.x.xxxxx.xxxxx then all is well. If you get an error message then you need to check you path statement.

The compiler will be installed in the c:\windows\microsoft.net\framework\(version) folder where version is the version of dot net that you have installed. If you check in the framework folder you may have multiple versions, and if this is the case go for the latest version on the machine.

Next add the path to the folder into the PATH variable. This is usually located in the Environment Variables option under system properties and the exact method of accessing it will vary depending on the version of the operating system you are running.

Once you have updated your path statement go back to your CMD window and try entering the CSC command again, if all is well you will see the Microsoft visual C# compiler version x.x.xxxxx.xxxxx message, if not check the previous step and retry.

Now that the compiler is working, we can now write the code for the function we wish to create. Open a blank file in Notepad.exe and enter the following:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Security.Cryptography;

public class RandomNumberFn
{
	[SqlFunction(Name="CryptoRnd")]
	public static SqlByte CryptoRnd()
	{
		SqlByte value= new SqlByte(255);
		RNGCryptoServiceProvider rngCsp = new RNGCryptoServiceProvider();
		byte[] randomNumber = new byte[1] ;	
		while(value>250)
		{
			rngCsp.GetBytes(randomNumber);
			value = randomNumber[0];
		}
		return value;
	}
}

 

If you are familiar with C# this code should be self-explanatory, and uses the RNGCryptoServiceProvider() which is the C# implementation of the CRYPT_GEN_RANDOM() number generator we looked at in TSQL in the last entry.

Save the file somewhere simple as rnd.cs, in my case K:\ as because I was using the command line version of the compiler I wanted something easy to type in.

In the CMD window enter the command csc /target:library K:\rnd.cs

After a second or two it should return you to the command prompt with no errors.

The /target:library switch on the compiler generates a .dll file rather than a .exe. The .dll file will be created in the same directory as the source file and appropriate permissions will be required to ensure the file can be created.

Next we need to add the function into SQL server, and we can do this by opening up the SQL Management Studio and creating a new query window.

In the query window enter and execute

CREATE ASSEMBLY CryptoRnd from 'k:\rnd.dll' with PERMISSION_SET=SAFE

 

If you have an error about the CLR Integration not being enabled, run the following script:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

 

This script will only need to be done once, and you will need the ALTER SETTINGS permission for your SQL Server user.

 

Next enter this query into the query manager and execute it

Create function CLR_RandomNumber() returns tinyint

as external name CryptoRnd.RandomNumberFn.CryptoRnd

 

This will actually create the function from the CLR code. The External name is in the form <assembly>.<class>.<functionname>

 

You can now test your new function by executing this query

 

Select dbo.CLR_RandomNumber()

 

Or it can be used in place of the random functions previously as

 

Select *,dbo.CLR_RandomNumber() from mytable.