Import existing Contacts or Accounts from Excel into a Marketing List as Members

Every now and then a customer asks me weather it’s possible to use an Excel sheet with existing Contacts or Accounts to create Marketing List. Although you should think Microsoft would have thought about that functionality, it just doesn’t exist. So as Consultants, we should figure out a way for our customers to provide them with this functionality. There are already several options available out there on the web, but I think there is a more easy way to get to this point.

A couple of options out there

There are several ways to achieve to get existing records from Excel into a Marketing List.

First of all, there is the option of using the Connections entity. This option is explained for you by ClickDimensions in this blogpost. However to my opinion, this option means creating unnecessary records in your database. Also, when you are actively using your Connections entity, users will see this records in their lists.

Second option is by using a tool called Manage NN Relationships, hosted by XrmToolbox (love this tool!!). This is by far the most clean way to import your Marketing List Members. However, I mostly find that users are having trouble with using this tool as it requires quite some technical knowledge of Dynamics 365.

A more simple option

So how do we make is as easy as possible for the users? During implementation of Dynamics 365, I make sure there is a field on the Account, Contact and Lead entity that points to the Marketing Lists (a Lookup). Now the user is able to export a list of let say Contacts with the Marketing List added to the Excel sheet. Using Excel, they can now start to pre-fill the Marketing List column in Excel with the proper Marketing List. After import, this Marketing List field is provided and all the user has to do now is create an Advanced Find and select all the records that need to be in the new Marketing List. Don’t know how to do a proper export and import? Check out this link to the Dynamics 365 Customer Center.

Error deactivating Business Process Flow after upgrade from CRM 2015 to Dyn365

Lately I had a couple of customers with an upgrade from CRM 2015 to 2016 and eventually to Dynamics 365. After this upgrade, there is a problem deactivating already activated Business Process Flows. You’ll receive an error like down here. When you open the log file, the error message says Microsoft Dynamics CRM has experienced an error. As this is not a very helpful message, I started to investigate on a solution.

Please keep in mind that this problem is already fixed for Online, but still exists for on-premise. For the on-premise I figured out that there is one difference between the Business Process Flows that cannot be deactivated and the ones that can. In the SQL database, go to the WorkflowBase table. You’ll find the field BusinessProcessType with a value of NULL. However, the upgrade should have set this to the value 0 (zero).

The fix

Now for the fix. If you are not used to change things in SQL, please find someone who is. Also always make a backup of your database before you start changing values. Changing values directly in the database of Dynamics CRM is not supported by Microsoft, so we’ll have to be very carefull.

First we need to find the ID that belongs to the Business Process Flow. You can find it by opening the Business Process Flow and look at the URL. The ID is situated between ?id=%7b and %7d. Now copy the ID as you need it to change the database. The ID should look like this 516b5f3-c18c-e611-813a-005056117dd9.

For the last part open SQL Server Management Studio and start a new query on your CRM database. Run the query as you see it down here (please replace the ID with the ID of your own Business Process Flow). You can now immediately deactivate your Business Process Flow.

update dbo.WorkflowBase
set BusinessProcessType = 0
where WorkflowId = '516b5f3-c18c-e611-813a-005056117dd9'

Using global OptionSets with Dynamics 365 Dialogs

Today I was facing a challenge with using global OptionSets in Dynamics 365 Dialogs. One of the companies I work for has a extreme large list of Price Lists (> 250) in CRM due to different combinations of product available. In order to improve usability for their users, I created a Dialog with several questions so the users can ultimately choose from a small selection of Price Lists. However, for one of the selections I used a global OptionSet. This is where the fun starts.

The problem

This company has different prices for the same product, based on the yearly revenue of the customer. They call this the company size and define three categories: Small, Medium, Large. For this selection I created a global OptionSet field on the Price List. Next, I meant to use this global OptionSet in the Dialog to make a selection of Price List applicable. To bad!!! Global OptionSets are not available in Dialogs. 🙁

The resolution

Now for the resolution I ‘recreated’ the OptionSet field in the Dialog, as you can see in this image. Make sure the Label from the options are the same as the global OptionSet labels. What you use for the Value is not important.

OptionSet in Dynamics 365 Dialogs

The next step would be to create a query. This is still correct, but instead of an equal-statement, you use a contains-statement. That way it Dynamics 365 compares the Labels and the selection is made.

One disadvantage

When the global OptionSet changes, you need to change the OptionSet in the Dialog as well…

Workaround for double update on statecode change via WebAPI

The new WebApi of Dynamics CRM 2016 has some strange behavior when you only update the state code. You would expect that there would be one update to the record, but when you inspect the audit logs, there are two updates to the record. First there’s an update to set the state of the record the the state it already has. The second update to the record changes it to the actual state. This is both at on-premise and Online versions of Dynamics CRM.

A detailed post on this issue can be found at this blog post of my colleague Martijn Eikelenboom. As this issue is a real pain with one of my own implementations, I created a ticket with Microsoft to find if this is a bug or expected behavior. Because my customer couldn’t wait for a bugfix, I have created a workaround.

The code that activates the double update

At first, I wrote code to update the statecode and statuscode on a record. See the code below.

function setStatus(statusReasonValue, stateValue) {
    var entitdyId =;
    var entity = {};
    entity.statuscode = statusReasonValue;
    entity.statecode = stateValue;

        type: "PATCH",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: Xrm.Page.context.getClientUrl() + "/api/data/v8.0/mg_orders(" + entitdyId.substring(1,37) + ")",
        data: JSON.stringify(entity),
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("OData-MaxVersion", "4.0");
            XMLHttpRequest.setRequestHeader("OData-Version", "4.0");
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        async: false,
        success: function (data, textStatus, xhr) {
            //Success - No Return Data - Do Something
        error: function (xhr, textStatus, errorThrown) {
            alert(textStatus + " " + errorThrown);

The workaround

I needed an other way to change the status of the record and thought of workflow. When changing the status by using workflow, there is only one update instead of two. Therefor, at first my workaround was to create an Action process with the statuscode as an incoming variable. Second I have changed the javascript to call the Action with the proper statuscode and third: the Action does the trick! Also I used Process 2.0 as JavaScript library. You’ll find what you need down here. Good luck updating the statuscode!

Screenshot of the Action


The new JavaScript

function setStatus(statusReasonValue, stateValue) {
        key: "Target",
        type: Process.Type.EntityReference,
        value: new Process.EntityReference("mg_order",
        key: "StatusCode",
        type: Process.Type.Int,
        value: statusReasonValue
    function (params) {
        // Success
    function (e, t) {
        // Error

        // Write the trace log to the dev console
        if (window.console && console.error) {
            console.error(e + "\n" + t);

Iconing in Dynamics CRM

Using Dynamics CRM, I create new entities with every project. But how to create the right icons for my newly created enties?

For iconing, I use a freeware tool called Metro Studio. It’s easy to use and provides you a wide range of icons and possibilities to change the occurrence of the icons you need. In order to download it, you’ll have to register before you can download it, but I’ve never had an email from them. So it’s safe to leave your email :-).

If you create a new entity, you need two sizes of the icon: one 16 x 16 pixels (will be showed with lookups) and one 32 x 32 pixels (for the menu bar). In this case, I need an icon for my newly created ‘Country’ entity. First, I search Metro Studio for ‘globe’ and I’m able to choose from a wide variety of globes. Second, when editing this icon I want the icons to look like the style of CRM itself. Therefor, I have some default settings for both icons.

16 x 16 pixels

For the small version of the icon, used in lookups, I have these settings:


  • With 16 pixels
  • Padding 0 pixels
  • Background Shapes, the most right one as you want your icon to have no background
  • Icon Color #FF656565 (the default CRM icon color)


32 x 32 pixels

For the larger version of the icon, used in the menu bar, I have some other settings:


  • With 32 pixels
  • Padding 3 pixels. CRM menu icons are all using 3 pixels padding
  • Background Shapes, again the most right one for the same reason as above
  • Icon Color #FFFFFFFF (white, as the default CRM menu icons)


If you want to know more about customizing the menu bar, read my post about customizing the menu bar 🙂