Using Dynamics 365 CRM lookups and alternate keys in Microsoft Flow

I’ve written a blog a while ago on using alternate keys with the Dynamics 365 / CRM WebApi. You can read the blog here. The post guides you in creating an alternate key and identifies some usage scenario’s.

Lately I’ve been working on an integration between Eventbrite and Dynamics 365 / CRM with Microsoft Flow. Currently I’m going back and forth between enthusiasm and disappointment. Sometimes it does more than you would expect and sometimes you’re missing the details you are looking for.


Most often you would want to set a lookup value in a record you are creating and updating. With flow this might be a bit hard as looping is cumbersome, or even impossible, to implement (e.g. nested loops are not supported).

The first thing you could do it set hard coded values. E.g. when creating a product in CRM it’s sometimes feasible to hard code the unit of measure. Simply pasting in the Guids works fine in such case:

Alternate key

Other time you want want to set a lookup to a record you are also integrating. E.g. with Evenbrite you have the ‘entity’ Events that I integrate with a custom entity Events. Event tickets I’m integrating with a custom entity Event tickets. On that event ticket entity I need to set the lookup to the related event. One option would be query for the event in CRM using Flow and setting the value.

However, to simplify things, I’ve created an alternate key on the Event entity in CRM, using the EventId from Eventbrite. Now setting the lookup is fairly easy, and we don’t have to query CRM to fetch the record. This sample works perfectly:


For now, Microsoft Flow seems to be a perfect tool to do lightweight integration scenario’s. Using alternate keys might really help in making your life a little easier in extending the options you have with Microsoft Flow.

Create a Dynamics 365 trial in just a few clicks

This post will let you create a Dynamics 365 trial real quick. All you need to have in place is an Office 365 instance and a Dynamics 365 (trial) license. Creating the trial for Dynamics 365 can be useful to test some things in new versions or if your trial instance is stuck.

Error when creating a Dynamics 365 trial: “no instances found for this user ID”

I’ve had it numerous times: you create an Office 365 trial and want to add a Dynamics 365 trial to the tenant. You go to the “purchase services” section and select the trial for Dynamics 365. Unfortunately, the Dynamics 365-tab is not showing up at the “Admin centers” section and when you go to the Dynamics 365 management url you get an error. No instances found for this user ID. Bugger! Now what?

Create a Dynamics 365 trial org in just a few clicks

In order to create a new Dynamics 365 trial org for testing purposes – or just because you wanted a trial org in the first place – you need to make sure that you have a Dynamics 365 license in place and added to your user profile. Haven’t got a (trial) license? Go to the “Purchase services” section and add a trial.

Now go to this url: You’ll get a form you’re probably familiar with. Just fill out the form, wait a few minutes and your trial org is ready for use!


Using alternate keys with the WebApi

Alternate keys are very powerful feature in Dynamics 365 / CRM. Especially when you are integrating with other systems it’s very helpful to use alternate keys to efficiently retrieve and update records. In this blog post I’ll guide you through creating alternate keys and using them in retrieving, updating and upserting records.

Creating an alternate key

The first thing you need to do is to create a field that holds the alternate key. Please keep in mind that you can only use the field types: Decimal Number, Whole Number and Sinlge line of text for an alternate key. There’s also a maximum of five alternate keys per entity and a restriction in size. For a full reference visit the documentation.

When you created the field you need to go to the keys of the entity and create the alternate key. The key can be a combination of multiple fields. In this example I kept it simple by using one field:

For the key to work, a system job needs to run to create an index. Please check if this is successful. The key I created for this blog post had an error on the first try.

Creating an account

Setting the alternate key in the request is the same as setting the value of any other field:

url: /api/data/v8.2/accounts
method: POST
body: {
	"name": "Alternate key account",
	"blg_alternatekey" : "blg12345"

Retrieving a record

Retreiving a record is quite simple. Normally you would use a get request to /account(0000-000-000-000-000) to retreive a single record using the guid of the record. Using an alternate key the request is this:

url: /api/data/v8.2/accounts(blg_alternatekey='blg12345')
method: GET

Here you can see that instead of using the guid, you need to use the field name of the key (not the keyname) and the value to retrieve the account.

Updating a record

The sample to update the record:

url: /api/data/v8.2/accounts(blg_alternatekey='blg12345')
 method: PATCH
 body: {
     "name": "Alternate key account updated"

The nice thing with the PATCH request like this is, that by default it will update the account if it exists, or it will create the account if it doesn’t exist. This is a great feature for integration scenario’s with other systems.

Navigation properties

You can also use the alternate key for setting a lookup value. In this example we will create a contact for the account created above.

url: /api/data/v8.2/contacts
method: POST
body: {
	"firstname" : "john",
	"lastname" : "smith",
	"parentcustomerid_account@odata.bind" : "/accounts(blg_alternatekey='blg12345')"


Alternate keys are a great feature, especially when you’re integrating with other systems. It’s good to see that the WebApi has full support for this, even with upsert requests and using it in navigation properties. This saves writing a lot of logic and a lot of calls getting guids from Dynamics365 / CRM.

Authenticate to the Dynamics 365 oData API with AppId and Secret

Authentication to the Dynamics 365 oData API (CRM) is something that all CRM Developers have been busy with. Previously, this was done by a normal user. The downside of this method is that the CRM web interface can be used with this user as well. Some versions ago, Microsoft introduced the concept of “Non-Interactive” users (see this article). This was already a huge step forward from security point of view. We could use a user now that didn’t have access to the web interface. There is still a downside to this: a username and password need to be stored somewhere. Since a username mostly has some kind of logic in it’s name, this can be predicted and may be available with an attack. Conclusion: still not so secure as you’d want to.

Introducing the AppId for Dynamics 365 oData API

Microsoft created the ability to authenticate to the Dynamics 365 oData API with the use of an AppId (in the December 2016 update)! Together with a secret, this replaces the username and password. Guess what: readability and logic are far more complex with an AppId and a secret, than usename and password. Again a great step forward in security. So to wrap up:

  • A username and password are readable and therefore less safe
    Password: somePassword
  • An Appid and Secret are far more difficult to read and therefore safer
    AppId: 82068d67-54a7-4698-a4eb-876dcc90b9d1
    Secret: BD”m_hIy461-E!p&;u0l@7sPCvp?579MA`iU3ek|5rD]V

If you want this, please read on and I’ll describe what steps you should take to use this new feature.

Steps to take to implement the AppId

A quick overview of the steps for the quickies is here:
1. Create an Application with AppID and Secret in Azure Active Directory
2. Assign the Dynamics CRM Online API rights to the Application
3. Create an application user in CRM and attatch the AppId
4. Enjoy!

Now here we go for the deep-dive.

Create an Application with AppID and Secret in Azure Active Directory

  • Sign in to the Azure Portal and Azure AD tenant by selecting your account in the top right corner of the page.
  • On the left side of the page, go to Azure Active Directory and select App registrations.
  • Click New application registration and provide the name. The application type must be Web app / API. You can chose any valid URL as Sign-on URL.

Assign the Dynamics CRM Online API rights to the Application

  • Open the new App registration and select Required permissions. Click Add, Select an API and Chose Dynamics CRM Online. Select all permissions and click Select and Done. Now the permissions should look like this.
    Application registration Permissions for the Dynamics 365 oData API
  • Now select Keys and create a new Key. Save the Key for later, this is the Secret you need to authenticate. Together with your Application ID, you are now done creating the Application.

Create an application user in CRM and attatch the AppId

  • Go to the CRM users and open the Application Users view.
  • Click New and you’ll get a special form for Application Users. Here you’ll provide the Application ID of the registrated application, a full name that sounds logical to you and a primary email. This e-mail must contain an existing domain within the same tenant.
  • For the final step, assign a custom security role to this user. Please make sure it’s a custom security role. A default security role won’t do.

Sample Code and Libraries

If you want to test if authentication works properly, you can download and use this VS project. If you change the config-file and you run the project, you should see your 5 first Accounts.

Since not everyone uses C# for their projects, you should know Microsoft also has the ADAL Libraries in all kinds of programming languages. ADAL stands for Azure Active Directory Authentication, which you can use to authenticate to Dynamics 365 Online as well. You can find the ADAL Libraries here.


Check if all required fields are populated

In my previous post Workaround for double update on statecode change via WebAPI, I use a custom Action to make a status change. However, when this is done the status of the records gets changed regardless of the required fields that might not be populated by the user. So in this case, we need to check if all the reuired fields have been populated before we call the custom Action.

First, we need to have a function that checks if all required fields are populated. After the check, I want the function to return “true” when all required fields are populated and “false” if otherwise. This is the function:

function allRequiredFieldPolutated() {
    var populated = true;
    Xrm.Page.getAttribute(function (attribute, index) {
        if (attribute.getRequiredLevel() == "required") {
            if (attribute.getValue() === null) {
                populated = false;
    return populated;

Now this function can be used in other functions, such as the function that has been used in my previous post, to check if the status can be changed.

Ignore the added https in a Text Field type URL

Since Dynamics 365 version 2016 (8.0), text fields with type URL get automatically https added in front of the URL if the user has not typed that. In previous versions of Dynamics 365 / CRM this addition was http. Since a lot of websites still don’t have secure certificates and don’t do https, we want Dynamics 365 to use http instead of https.
With a simple JavaScript Web Resource, we can apply the old functionality as we used to have it. These 6 lines of code fire when an URL is typed in by the user, so the URL gets http as addition instead of https.

Make it work

In order to make this work, we first need to add the JavaScript Web Resource into Dynamics 365. So open your Dynamics 365 environment and go to your customization (either a solution or the default customizations). Now go to Web Resources and add a new Web Resource. Give the Web Resource any name you want to and upload the file that can be downloaded at the bottom of this page. Save and publish the Web Resource.

Next, go to the form on which you want this functionality and start editing this form. Open the Form Properties and add the Form Library you just created. After you clicked ok, you open the properties of the URL field and go to the Events tab. Add an Event Handler in which you select your library, set the Function field to ‘setHttp’ and check the ‘Pass execution contact as first parameter’. Now click ok, close all and publish your customizations.

That’s it! You’re good to go now. Please keep in mind that Dynamics 365 can sometimes experience some caching issues. When you open the form next time and you get a scripting error, please press Ctrl+F5 once. The scripting error should be gone now.

The code

UPDATE (2018): Since there have been some changes in javascript actions since Dynamics 365 v9.0, there is a new script for the v9.0 and above organisations.


function setHttp(context) {
    var webUrl = Xrm.Page.getAttribute(context.getEventSource().getName()).getValue();
    if (webUrl != null && webUrl.substring(0,7) != "http://" &&  webUrl.substring(0,7) != "https:/") {
        Xrm.Page.getAttribute(context.getEventSource().getName()).setValue("http://" + webUrl)


function setHttp(executionContext) {
    var formContext = executionContext.getFormContext();
    var webUrl = formContext.getAttribute(executionContext.getEventSource().getName()).getValue();
    if (webUrl != null && webUrl.substring(0, 7) != "http://" && webUrl.substring(0, 7) != "https:/") {
        formContext.getAttribute(executionContext.getEventSource().getName()).setValue("http://" + webUrl)

Download your file here

Download v8.0 and above

Download v9.0 and above

Cannot track e-mail to custom entities

With the use of the new Dynamics 365 for Outlook-App, we finally have a proper-working Outlook integration. However, I ran into a problem at one of my customers that they couldn’t track e-mail to a custom entity. That is, search the proper record in the Dynamics 365 for Outlook-App. The record that I wanted to Regard the e-mail to was in the Recent Used Records section. However, when searching for it, it didn’t pop-up as you can see down here.

The solution

I found out that you need 2 settings right to make this work properly.

  1. Make sure that the custom entity you have created is “Enable for Mobile”;
  2. Enable Relevance Search and add your custom entity to Relevance Search (for more info about Relevance Search, read this article on Microsoft Technet);
    (There is a Configure Relevance Search Option, within the Dynamics Default Solution)

Now when this is done, all you need to do is publish your customizations and wait for about 15 minutes until Azure has indexed your search data. Et voila! Your entity now shows up in the search!

Testing the Dynamics 365 / CRM app without device

We all have customers still working with an on-premise Dynamics CRM environment. Most of the time, these customers have IFD enabled on their Production, but not on their Development and Test orgs. This means you cannot get to test the app. Also, when using Online, you just might not have a device at hand where you can test the app properly.

Microsoft has come up with a solution for this! From now on, you can open the app just in your browser! Just follow the steps below:

  1. Go to your CRM environment als log in as a system administrator or system customizer (let say http://your-server/org-name);
  2. In a separate tab, open this url: http://your-server/nga/main.htm?org=org-name&server=the-server-url

As an example, I can show you these two for both on-prem and Online.

  • http://crm-server/contsos > http://crm-server/nga/main.htm?org=contoso&server=http://crm-server
  • >

Now you open the app in your browser!

Migrating / Importing data and maintaining Created By, Modified By and Modified On

When importing or migrating data to a Dynamics 365 / CRM environment you cannot migrate the fields Created By, Modified By and Modified On. Most customers won’t bother about it until it comes down to the migration of ‘old’ activities. By default, the Social Panes show activities sorted on the Modified On date. So when you migrate data and all activities have ‘today’ as the Modified On date, you’ll get problems with the sorting of activities.

Migrating the data and setting the fields

As you know, it’s not possible to set the values of the Created By, Modified By and Modified On date. That is when you try to set this data directly. Lucky for us, when using the SDK it is actually possible to set the values of those fields. The solution contains a plugin that does this:

  • At the creation of a record, check if the field ‘dnbs_overriddencreatedby’ contains a value. If so, set the field createdby to that value.
  • At updating a record, check if the field ‘dnbs_overriddenmodifiedby’ contains a value. If so, set the field modifiedby to that value.
  • At updating a record, check if the field ‘dnbs_overriddenmodifiedon’ contains a value. If so, set the field modifiedon to that value.

In order to make this work for your data migration or data import, just make sure you set the values of the fields ‘dnbs_overriddencreatedby’, ‘dnbs_overriddenmodifiedby’ and ‘dnbs_overriddenmodifiedon’. These are the schema names of the fields. The display names are: Overridden Created By, Overridden Modified By and Overridden Modified On. When you import the solution, these fields are available on these entities:

  • Appointment
  • E-mail
  • Letter
  • Phonecall
  • Taks

The solution is extensible

The plugin inside the solution is setup to fire off on all entities. This means that you can extend the use of this plugin to all other entities. When you want to use the same method on the Account entity, for instance. You just need to make sure you create these fields on that entity. The schema names of those fields must exactly match these names.

  • dnbs_overriddencreatedby as a Lookup to the User entity;
  • dnbs_overriddenmodifiedby as a Lookup to the User entity;
  • dnbs_overriddenmodifiedon as a Date and Time field.

Don’t forget to delete the solution!

When you are done with migrating or importing your data, don’t forget to delete the solution! As you have seen above, the plugin fires off at all updates. This is a loss to your performance and also you might get into trouble with the Modified By and Modified On fields not being updated right.

Download your solution here


The source code can be found here

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.