Dynamics 365 CE integrations scenarios with Azure Functions

Over the past year or so, our team has worked on almost exclusively cloud-based Dynamics 365/CRM implementations. Common requirements haven’t changed, long running batch processes, integrations with external systems, maintenance jobs etc. We have found ourselves using Azure Functions in almost all projects for the past year. In this article, I define what Azure Functions, how they are priced, and I provide some of the use cases that we have seen when it comes to Dynamics 365/CRM integrations.

What are Azure Functions

Definition

Azure Functions is commonly referred to as serverless computer service. What this means is that it is a cloud-based service that allows you to run code on demand without having to deploy it to any infrastructure, and to only pay for the usage that you make. Microsoft developed this Framework to allow us to write code in a language of your choice (C#, JavaScript, PowerShell, PHP, Python), have it do all sorts of cool stuff, deploy it somewhere and run it on demand. The notion of “on demand” is broad and it takes us to the triggers discussion. Functions can run a wide set of triggers, including Timer, HTTP call, Webhooks, Service Bus, Queue Storage and others.

Pricing model

The pricing model is relatively straight forward. There are two models. The first one is the App Service plan. I will not spend too much time on this one. App Service plans are the type Azure services that you subscribe to when you want to host a web application (web site, web service etc.). If you already have such a subscription, you can simply deploy your Azure Functions into it at no extra cost.

The other pricing model is a consumption based model. This model takes two components to do pricing:

1. Number of executions: any time a function is executed (i.e. triggered), it counts as an execution
   
2. Resource consumption (in GB-s): any time a function runs, the platform calculates the amount the memory (RAM) that is used and for how long. This translates into a number of Gigabytes-seconds (GB-s) that you have to pay for. It is also a number that is sometimes hard to predict because you cannot easily know how much memory your piece of code will require when it runs.
   

There are monthly free grants of 1M executions and 400,000 GB-s resource consumption. After that, you start to pay a certain amount by execution and by GB-s used. Prices can vary slightly based on the region that you are in.

In most cases, it tends to be the most adapted for business scenarios that we have worked on, meaning the price that you pay and the guarantied scale for performance outweighs the benefits of buying let’s say an inexpensive App Service to reduce your monthly cost.

Dynamics 365/CRM and Azure Function use cases

As we’ve worked on CRM Online projects, we have often faced scenarios in which we have considered and often used Azure Functions. I grouped them in use cases.

• Recurring jobs: these types of processes are typically built using console apps running as scheduled tasks, SSIS Jobs or sometimes even recurring Dynamics 365/CRM workflows. It could also be a DevOps PowerShell script that developers run every day from their workstations. Azure functions with timer triggers work well to address many of these scenarios.
  
• Reusable business logic: when you have a bloc of code that is called from multiple plugins/integration jobs or custom applications, lots of times you will find code being copied manually or file references use heavily. It makes sense to consider moving the code to an Azure function with an HTTP trigger and have the function being called by multiple processes.
  
• Plugin scenarios impacted by sandbox limitations: there are plenty of restrictions when you run your plugins or custom workflow activities in Sandbox mode. For example, referencing external libraries is not officially supported, running processes that last more than 2 minutes, calling web services with complex authentication, writing on disk etc. Most of the limitations are completely removed when working with Azure Functions.
  
• Reducing the cost of alternative tools: In all the scenario above, there is usually some financial impact with licences (Windows Servers, SQL Server, SSIS Connectors etc.). The local servers also have to be monitored and maintained. Azure functions remove some the servers and tools licensing cost and shifts it to a “pay per use” model while suppressing the need to maintain servers on premise.
  

These use cases along with resource skills availability have made us use seriously consider Azure Functions in our solutions architecture for the past year or so. I strongly recommend taking a look at them if you haven’t.

Getting started…

I had the pleasure of presenting this content on an xRMVirtual User Group webinar. The slides are available below.

Check back for the link to the webcast, I will publish it when it makes it to the website. In the mean time, here are some articles to get started with Azure Functions and Dynamics 365.

Getting started with Azure Functions here.
Integrating D365 with Azure Functions – Part 1 and Part 2

Enjoy!

Mobile Options for Dynamics 365/CRM

Often in CRM implementation project, there is a need to have CRM data available on mobile devices. When using the Microsoft Dynamics 365 platform, there are a few options available and it is sometimes unclear how to decide which is the best fit for an organization. In this article, I start by giving common scenarios in which mobile access is required before describing some of the available solutions and corresponding use case.

Common Requirements for CRM Mobile Access

Sales people are often on the road, meeting with customers and prospects, attending conferences or trade shows. When they use a platform like Dynamics 365/CRM to support their activities, having access to their sales tools can be extremely beneficial: they can check out customers history on the spot prior to meetings, add notes after a visit. They can also lookup, create or update CRM contact information while away from the office and computers.

For Field Service professionals (e.g. internet cable installation agents), they need to have plenty of information to work efficiently: where to go, what to do when they get there, who they are supposed to meet, where they are supposed to go next based on their current location etc. For these types of uses, the level of interaction with the CRM data is higher than for typical sales individual, and it is more critical to their work (i.e. they could hardly do their job without it).

Another use case that I have often seen is the timekeeping requirement. We see this regularly in various Professional Services scenarios (consulting, legal, contracting etc.). Professional Services’ firm employees that are often moving from one task to another, one meeting to another, one client site to another, having the ability to enter the time spent on various tasks could add great added value.

While there are plenty of other mobile CRM requirement scenarios, I think these three are a good fit to illustrate several types of usage and need for people on the move. Now Let’s see what our options are and tag them to the scenarios they apply to.

Dynamics 365 for Phones and Tablets

Commonly referred to as MoCA for Mobile Client Application, this is the application provided at no additional cost by Microsoft as part of the Dynamics 365 offering.

• Supported CRM Source
  
  • D365/CRM Online
    
  • D365/CRM On Premises with Claim-based + IFD
    
• Installation Process
  
  • Download the platform application store on Windows, Android and Apple devices (tablets and phones, compatibility varies based on your Dyn 365 version – more details here)
    
  • Once the application is downloaded, connect to one CRM Organization using its access URL and your credentials
    
• Configuration & Extensibility
  
  • Uses the out of the box CRM web configuration (as long as the components are enabled for mobile, example for Dashboards here)
    
  • The application can be extended the same way as the web forms. Most JavaScript API will work but not all – see details. You can write or skip some JavaScript code execution based on the current experience.
    
  • You can view your web custom resources on mobile (this of course has design / build implication to make sure the content renders nicely on mobile)
    
  • The application can function in Offline mode.
    
• Access to Phone
  
  • When connected to Dyn365 Online Organization – access to GPS and Camera
    
• Cost
  
  • No additional cost. It is included with your Microsoft Dynamics 365 licences
    

The OOB mobile application works nicely, providing us with facing visuals such as dashboards and tiles to access various areas. It does lack the look and feel of a native phone application in my opinion, and feels very much like a fancy/very well-built web page displayed through a mobile app. It is a major area of investment for Microsoft so expect it to get better over time.

I usually recommended using it in low application usage type scenarios that includes data consultations, charts visualization and quick updates (add, update contacts, accounts, notes etc.). This fits the Sales People scenario above nicely. It is free of charge which is a big plus when mobile access is not considered critical but a nice to have for your organization.

Power Apps

PowerApps is Microsoft’s new platform to rapidly build custom business applications for Mobile devices. Because it is part of the Office 365 suite, it can connect to most of the services and applications available in the Microsoft Cloud, including Dynamics 365/CRM.

• Supported CRM Source
  
  • D365/CRM Online
    
• Installation Process
  
  • Download the PowerApps application from the store on Windows, Android and Apple devices, connect to your Office 365 account and you will see the list of all Apps deployed within your organization and made available to you
    
  • There is no need to add additional credentials to connect to CRM (unless specified by the App Builder)
    
• Configuration & Extensibility
  
  • PowerApps are build by a Power user or an expert, typically using the PowerApps Studio application, or using the app configuration tool on the web.
    
  • The configurators will let you start from a template or start from scratch, and connect to existing cloud services such as Dynamics 365 and other Microsoft and non-Microsoft sources. The documentation to get started is great.
    
  • There is a large set of configuration options (types of controls available, business rules formulas).
    
• Access to Phone
  
  • PowerApps has commands to use the phone’s GPS and Camera. However, the interaction between the GPS and Camera and Dynamics 365 have to be customized (i.e. when the position is taken, you have to configure your app to consume and perhaps save the information in a Dynamics 365 record)
    
• Cost
  
  • It is included in select Dynamics 365 and Office 365 plans. It is usually the best way to get it as you will connect to applications related to these services. The regular price is $7 USD per user per month, and $40 USD for app makers.
    

Aside from the application installation and access model that is uncommon (starting PowerApps which will display your list of available apps in your org), the app themselves look and feel much like native phone applications.

It takes more effort to get a PowerApps based-application to provide fancy graphics such as charts or customer web pages but it usually meets requirements for simple, targeted functions on-the-go, such as doing time entry. We give the user a few lists (their projects, the time entries etc.), we give them a screen to view project summary, a page to create, update time entries and link them to a project, and we are done. Again, simple, targeted function, which matches the Professional Services scenario described above.

In you are using Dynamics 365 and have Plan 1 licenses, then there is no additional cost. The effort to build PowerApps can be very low depending on the complexity of your business needs. This has potential to provide enormous value at a very reasonable cost.

Resco Mobile CRM

This is, by far, the Cadillac of mobile applications for Dynamics 365. Resco Mobile CRM is a completely configurable mobile application that can connect to Dynamics 365 and competitor Salesforce. It allows for simple configuration such as Mobile forms, views, as well as more complex business rules and custom behaviors.

• Supported CRM Source
  
  • D365/CRM Online
    
  • D365/CRM On Premises with Claim-based + IFD
    
• Installation Process
  
  • Download the platform application store on Windows, Android and Apple devices
    
  • Once the application is downloaded, connect to one CRM Organization using its access URL and your credentials
    
• Configuration & Extensibility
  
  • Configure home screens, forms, views and additional custom behaviors through the Woodford configurator. It is a Silverlight-based application that connects to your CRM and allows you to configure the Mobile application to meet your need (don’t panic, they are rolling out an HTML5 based app to replace the Silverlight configurator).
    
  • All the client side configuration (forms, views, form scripts, client side business rules) are not available. New views, forms and business rules must be recreated in Woodford
    
  • There are a lot of advanced features such as Map or Calendar View of selected record types, Dashboards Routing and Route optimization ($)
    
  • There is also the possibility of extending the Mobile application using JavaScript-like code to perform all sorts of operations (hiding, showing fields, custom web pages etc.).
    
  • The application can function in online or offline mode.
    
• Access to Phone
  
  • Access to GPS, Camera, phone storage to upload pictures of other types of files
    
• Cost
  
  • Licenses are per user per month, the Professional License cost US$25 while the Enterprise License is US$40.
    

The thing that I really liked when we started to use Resco Mobile was that it just felt like a native phone application. The controls are the same as what you are used to in other apps, it is very responsive and just does not feel like a web page made available through a Mobile App. For use cases where users must use their phone as a main part of their job, such as a Field Service professional, who goes from one place to another, checks cases and customer information, takes pictures, gets customer signatures etc., using Resco to interact with Dynamics 365/CRM seems like a no-brainer. This is in part justified by the fact that Microsoft uses Resco Mobile as its mobile application for the Dynamics 365 for Field Services application.

Keep in mind that outside of the licenses, there is a large configuration effort that has to go into configuring and sometimes customizing the mobile experience which can take time and cost money.

If you have a large user base, you have to factor in the license price per user / month over time and see if this still makes sense for your scenario.

Recurring Jobs Strategies for Dynamics CRM/365

With the rise of the cloud, I have seen lots of small businesses building their technology strategy with a very little amount of local infrastructure. Similarly, I also see larger enterprises migrating to the cloud and trying to remove most of their local infrastructure. In Dynamics CRM/365 implementations, we often need to run recurring jobs that executes business logic (data integration or synchronization, complex calculated field etc.). This article talks about the different places where you can have these Dynamics 365/CRM connected jobs running periodically.

Windows Executable or SSIS Packages

Windows Executable are a simple and very common approach. The principle is to write an Console Application and use CRM SDK to connect to CRM and C#/.NET and other APIs to connect to various systems as required to do all sorts of data processing. After the console app is coded and tested, it is typically deployed on a Windows Server machine as a Scheduled Task. Using a Window Services is a similar approach, the main difference is that the recurrence of the execution needs to be managed at the application level (in the Window Service code) instead of leveraging the Task Scheduler built-in feature. This strategy gives a lot of flexibility:

• no constrains on how long the process can run for
  
• you provide the hardware required to handle your activities
  
• you leverage internal resources if they have .NET/C#/CRM SDK knowledge
  
• no need to build a UI which saves a lot of time on the development side
  

If your business operates mostly in the cloud and you don’t have available servers to install and run you application(s), you can create a Virtual Machine in the cloud and deploy your executables on it (as long as your CRM/Dyn365 organizations and other integrated systems are accessible from the VM).

Using SQL Server Integration Services (SSIS) Packages is pretty much identical to the Windows Executable approach. The difference is that you use SSIS, combined with a third-party tool for your CRUD operations with CRM/Dyn365 such as KingswaySoft, to contain your business logic and manipulate your systems data. You can then schedule SQL Jobs to run SSIS packages on a recurring basis. This technique is used when there is stronger knowledge on the SQL/SSIS side within an enterprise. Again, if you do not have a local infrastructure, you will have to create a Virtual Machine in the cloud with the required SQL Server components to create SQL Jobs and execute SSIS packages.

Recurring CRM Workflow(s)

While Dynamics CRM/365 does not have true task scheduler functionality, out of the box processes (workflows) can be used to achieve similar type of functionality. This is done by using the “Process Timeout” operation within a CRM Workflow. Check this article from PowerObjects on exactly how to setup recurring workflows in CRM 2016.

In order to run custom business logic, data integration or calculation, you can create Custom Workflow Activities and call them from within the recurring workflow. The custom activity is native C#/.NET Code so you can do all sorts of operations from there. I love this approach because it allows you to keep your recurring processes mechanism inside the CRM solution. It makes the deployment lighter and simpler, you don’t have to deploy a package on a SQL Server, you don’t need to deploy an application on a server and configure it. That being said, this approach has quite a few limitations:

1. It is not simple to reference libraries other than .NET and CRM libraries, fellow MVP Gonzalo Ruiz has a good article on how to achieve this here. If your custom business logic makes references to many external assemblies, it will be a bit more challenging to integration in a Custom Workflow activity (it’s a good idea to target web services instead).
   
2. When your custom workflow activity as running is isolation mode or Sandboxed (this is always the case in cloud-base Dynamics CRM/365 Online tenants), there are limitations on what you can in the custom workflow activity (e.g. how to call external web applications, no IO operations, cannot access registry etc.). There is also a 2-minute timeout limit for any process running in Sandbox.
   

If your business logic is long to execute (more than 2 minutes), this is not a viable option.

Using Microsoft Flow

Flow is relatively new to the Microsoft Cloud. It is a configurable workflow engine that allows to automated operations related to a large variety of cloud applications. The workflows have triggers that originate from cloud applications (e.g. email received from a specific sender, document created in OneDrive, or Contact created in Dynamics 365) or timer based.

Flow offers a connector to Dynamics 365 in the cloud with a few trigger operations (record created, updated, deleted) and basic actions on Dynamics 365 such as creating, retrieving, updating or deleting a record or retrieving a list of records. This is a detailed article from Wayne Walton that perfectly summarized what Flow is and how it can be used, you can read it here.

While Flows can run on a schedule (every x seconds, minutes, days, hours), there is a limit to the systems you can connect to from flow (last time I checked, there was integration built with just over 80 SaaS apps). If you want flow to execute custom business logic, you can develop and expose a Web API to the web and configure Flow to call it with contextual parameters. Custom operations can then be processed from within the Web API.

The biggest advantage of using Flow is the ease of configuration. Power users with enough knowledge of Flow and Dynamics 365 can easily configure workflows. However, depending on the complexity of the operations that need to be executed, you might find yourself limited with the basic operations available. This could force you to write a custom Web API for additional processing. Overkill if you ask me, but there are situations in which this could make sense.

Other facts to take into consideration:

• Flow can only connect to Dynamics 365/CRM organization in the Microsoft Cloud. If you are running on-premises with IFD, it will not work.
  
• The License for flow is based on a number of runs per month (4500 with Plan 1 and 15000 with Plan 2).
  

Azure Functions

If you are not familiar with Azure Functions, you really should do that soon: “Azure Functions is a solution for easily running small pieces of code, or “functions,” in the cloud. You can write just the code you need for the problem at hand, without worrying about a whole application or the infrastructure to run it” (reference here).

You can write functions in the development language of your choice (C#, F#, Node.js, PHP or Python) that connect to various external services or systems, including Dynamics 365. See this walkthrough if interested in knowing what’s involved. Azure Functions support events based on a configurable timer.

What I particularly like about Azure Functions is that it is a pure serverless architecture to run custom code. If you had a small console application deployed as a scheduled task, you can easily bring your code into Azure Functions and do the same operations.

If you are a company that does not want to deal with the overhead of having and maintaining servers internally to run your recurring processes, this is definitely the way to go. The billing calculation is complicated, but in short you only pay for the resources your code consumes when it runs. That means if you don’t run anything for a period of time, you have nothing to pay. Another positive about Azure Functions is that we can reference the CRM SDK which means should be able to connect to IFD-configured on premise installations (I haven’t tried this but I am pretty confident).

One of the downside of using Azure Functions is the development experience. While this is changing, at the beginning you could only write your code in the Azure Functions code editor which makes you lose the magic of Visual Studio.

 

Of course, these are a few existing options for recurring processes as it relates to Dynamics 365/CRM. Ultimately, the decision for each enterprise comes from its strategy, its people and its willingness to make investment.

Hope this helps !

Plugin on Retrieve and Retrieve Multiple – How bad is it?

I have managed to be in the Dynamics CRM/365 world for over 7 years without having to write a single plugin on Retrieve and Retrieve Multiple. The recommendation that I give is to stay away from those. The reason is simple, it sounds horrible from a performance standpoint, and even people from Microsoft have recommended against it in many scenarios. Faced with an issue recently where we had to really consider it, I did some research and testing to try to measure the impact of such plugins on system performance. This article provides some background as to why we recommend against these types of plugins, and it also provides some of our finding after we tested for performance.

Why are Plugins on Retrieve Multiple scary?

When looking at the event execution pipeline for Dynamics CRM/365, we need to consider that there are a lot of steps involved as part of every CRM transactions. To do anything, we need to go through the CRM web service APIs which will start the chain of events in the pipeline (pre-validate, pre-event, core action/database access and then post-event).

This means that in general, it is a good practice to build everything for optimized performance to give your user base a good experience. It’s not like having a custom database where you can create store procedures easily, add triggers and so on, taking advantage of the SQL Server features and infrastructure.

Now, back to Retrieve and Retrieve Multiple plugins.

Retrieve Multiple: Think about it this way, you retrieve a list of 200 accounts, your plugin on Retrieve Multiple fires once and gives you the list of accounts being returned to the screen with all columns being retrieved. For each of these row, you do some type of operation. It doesn’t too sound good, does it?

Retrieve: You double click on an account from a list view. As the account columns are being retrieved to display the account form on the screen, you plugin on Retrieve fires and gives you the account object. At that point you can modify the content of the columns being retured as required before they are returned to the screen for the user. This really doesn’t sound too bad.

Some Findings on the impact on Performance

To provide some context into what we were trying to do, I wrote about multi-language lookup in a previous blog post. One of the solution that we have considered for one of our client is to use a plugin on Retrieve and Retrieve Multiple in order to change the value of the lookup primary fields in order to display a value in the user’s current language. The method we used is similar to what Aileen Gusni does here and almost identical to what Scott Durow does here.

We store a Region in a custom Entity. Accounts have a lookup that indicates its region.

Scenario 1:

The Region’s Primary Field contains a concatenation of the English and French region names with a relatively safe separator (we use “|”). When you load the list view of accounts, we have a plugin on Retrieve Multiple that looks at the columns being returned. If the Region column is returned, we retrieve the user’s language, we split the name of the region with the separator (the name is available in the entity reference) and we replace its value in the target object by the region name in the user’s language. The plugin on Retrieve does the same operation on the single role being retrieved.

The average execution time for the Retrieve Multiple plugin when loading 150 rows was 15.43 milliseconds so 0.01543 seconds.
The average execution time for the Retrieve plugin to load one rows was too little for the system to return a value (we got 0 milliseconds every time).

Scenario 2:

The Region entity has English Name and French Name attributes. When you load the list view of accounts, we have a plugin on retrieve multiple that looks at the columns being returned. If the Region column is returned, we retrieve the user’s language, we then retrieve the English or French name from the Region entity and we replace its value in the target object by the region name in the user’s language. The plugin on Retrieve does the same operation on the single role being retrieved.

The average execution time for the Retrieve Multiple plugin to load 150 rows was 1003.94 milliseconds so 1.00394 seconds.
The average execution time for the Retrieve plugin to load one rows was 16.02 milliseconds so 0.01602 seconds.

What should you read into this?

While these numbers don’t look too crazy at all, especially in the first scenario, there are a lot of factors to take into consideration that are not really showing here and that will vary in almost any scenario.

• What is the infrastructure you are running on? The faster your severs and networks, the better the performance will be.
  
• What you do in these plugins matters a great deal. You should avoid or limit the number of read/writes to the database during the execution of those plugins.
  
• Our tests were made with a low level of users in the system, it is critical to scale up and see what these numbers look like at peak time of your system.
  

With all this said, I still recommend against it. Use with a great deal of caution! If/when possible, use calculated fields instead of writing plugins on these messages. This will also keep you away from limitation such as this one.

Hope this helps!

Dealing with Multi-Language Lookups

Very often, CRM entities are used as reference data tables, for example to keep a list of countries, states or provinces or other business/industry specific data. For some businesses I have seen entities to keep a list of distributors, list of business roles, regions to only name a few. When used that way, CRM entities provide a lot of great features that cannot easily be met with option sets such as the ability to manage large reference tables, lookup search, lookup filtering, ease of adding/editing/modifying data by power users without a deployment.

One of the issues with using CRM entities for reference data is that they is no concept of multi-language lookup in the Dynamics 365 / CRM platform. Lookups will always display the value of the primary field by default. This can cause an issue in places where you must have a fully multilingual application. In this article, I provide a few possible solutions to solve this issue.

As an example, we’ll use the context of a task for which we need to track its type. The list of the available task activity types is stored as records in an entity called “Task Activity“. The Task entity has a lookup to the Task Activity entity. The information needs to be stored in English and French

1 – Task Form with Lookup to Task Activity

2 – List of Task Activities

Resolution Option 1 – Concatenate multiple languages in one field with a separator

You will be disappointed, this is not a fancy solution. In the Task Activity entity, we have one field for the name in both languages and use concatenate both field values in the primary field using a workflow or plugin.

• Name English (Single line of text – 100)
  
• Name French (Single line of text – 100)
  
• Name (Single line of text – 203) – read only for users, populated with “Name English | French Name”
  

3 – Task Activity Form

This is the most common approach that I have seen when the number of languages is small (2 languages). This has a disadvantage of sometimes creating long name values that are not fully visible in the views and on the forms, but it’s cheap and you keep the ability to search using lookup, and display the columns in French or English the views if you need to.

Resolution Option 2 – Plugins on Retrieve & Retrieve Multiple

This solution is a little more interesting, but risky. In the Task Activity, we still have one field for the name in both languages and we still concatenate both field values in the primary field.

• Name English (Single line of text – 100)
  
• Name French (Single line of text – 100)
  
• Name (Single line of text – 203) – read only for users, populated with “Name English | French Name”
  

The principle is to write plugins on the Retrieve and Retrieve Multiple events of the Task Activity. In both of these plugins, you need to retrieve the connected user’s language (query the user settings table), and then replace the text being returned in the Name field by the value in the user’s language. This value can be obtained by querying the task activity record and retrieving the name in French or English, or simply splitting the Name field (primary field) with the separator and return the part in the desired user’s language. One plugin will handle the lookup column in list views (Retrieve Multiple), and the other will handle the form views (Retrieve).

Generally, it is not recommended to write plugins on the Retrieve and Retrieve Multiple events for performance reason. If the operations executed in those plugins are simple and optimized, it might be a viable solution. This is a solution that can scale well if you are dealing with more than 2 languages because in all cases, users see only the value in their selected language and the multiple values are transparent to them.

Stay tuned, I have an upcoming post where I provide some metrics about the impact of a plugin on Retrieve and Retrieve Multiple on performance.

Resolution Option 3 – Automated mapping of Option Set with Reference Entity Records

This is a bit of a complex solution, by far the fanciest. The idea is to use an option set instead of a lookup to reference the task activities, but the option set values will be “controlled” with records from the Task Activity CRM entity. It goes like this:

1. Create a global option set named Task Activity
   
2. Create your Task Activity entity with a primary “Name” field. Put the name of task activities in the primary language (language of the CRM org)
   
3. When a record is created in the Task Activity entity, use a plugin to create an option set value in the global option set
   
4. When a record is updated in the Task Activity entity, use a plugin to update the corresponding option set value in the global option set
   
5. When a Task Activity record is deleted/deactivated, use a plugin to update the corresponding global option set value by putting brackets around the name for example, and also pushing the value to the bottom of the option set list
   
6. You can then get the CRM Translation file and get the Task Activities values translated as part of the global solution.
   

4 – Records to Option Set Value mapping

The outcome is that for entities that need to capture the task activity information, there will be an option set field as opposed to the lookup field:

5 – Task Form with Option Set

While this has low impact on performance and leverages the out of the box language-aware option sets, it requires a serious time investment to define the development framework for each entity that required this mechanism to be implemented. It also requires a translator to update the CRM translation on a regular basis (every time there is a deployment). This is a fancy solution that requires a lot of coding and maintenance. In addition to that, you lose the ability to search and filter the content easily like you would do with lookups. You should make sure yours lists are not very long if you don’t want to end up with Option Set lists that are very long which will result in poor user experience.

I have rarely seen companies making such large investments to circumvent the lack of multi-language lookup in CRM. This is usually seen when there are laws that force you to have a system running fully in multiple languages.

Resolution Option 4 – Custom Screen for Lookup views & search

This is another fancy one for which I unfortunately don’t have any screenshot. We want to leverage option sets to “overwrite” lookup values and selection process with the following steps:

1. Create a set of standardized Web Resources for Lookup Display and lookup value selection
   
2. Display the web resources on the CRM Forms and hide the lookup controls
   
3. The web resources will have built-in logic to display the value in the language of the current user, as well as a mechanism to allow searches (could be auto-complete based)
   

Writing a standard web resource control for that purpose is relatively simple. However, you might have additional work to do if you want to take advantage of filtering based on other fields, or custom filters. Also, this solves the issue on the form in the sense that you will see the values in the right language on the form, but for list views, reports etc. the problem will still exist so you’ll need to find another solution there.

Closing Thoughts

As you can see there is no perfect solution. Each organization has to decide the level of investment and risk they want to take to make sure they have multi-language option sets. Living in Canada where we have two official languages, this is a challenge that we often see in public sector implementations because having fully bilingual system is mandated by law. There are very few countries where this is the case (which is probably why Microsoft has not made investments in this area). Most private sector companies will usually impose a primary language for the entire organization.

Hope this helps!

Creating readable unique identifiers for your CRM records

A common requirement in CRM implementations is the need to have readable identification numbers auto-generated for custom attributes. There are various ways of achieving this. This article provides a few options to do that.  

Let’s use one scenario as a context. Let’s say we are building a Grants Management application and we are tracking scholarship applications in Dynamics CRM. We have an application entity which contains an Application Id field. For business reasons, we need to have a unique identifier to refer to the applications. What are out options? For starters, let’s look at some of the elements to consider when looking for an auto-numbering solution in Dynamics CRM?

• Are you using an entity that is auto-number enabled? (e.g. Case, Quotes, Orders etc.)
  
• Do you need the numbers to be sequential?
  
• Do you have potential race conditions? (i.e. can the event assigning the auto-number be triggered by multiple processes simultaneously?)
  
• Do the auto-numbers need to be human readable?
  
• What is the format required? (numeric only, alpha-numeric, letters only, prefix, suffix etc.)
  
• Can you and/or do you want to write code to generate these numbers or do you want an simple configurable solution?
  

Generate an unique number based on the current Date Time

Of course, this required a plugin on an event that will generate the application Id. From a technical standpoint, there are a few algorithms out there that will give you an indication as to how this can be done. The full date time and the “tick” should be use to generate the number to ensure uniqueness. But even with that, there is a very very very small chance of having race conditions depending on your application and on your (bad) luck. Also, this will not allow your to use sequential numbers.

Create a record of an auto-numbering CRM entity

If you are *not* using one of the CRM auto-numbering enabled entities (for example, Campaign), this solution is have a plugin (or a Workflow) on your triggering event that will create a Campaign record. This campaign will automatically be assigned a unique number based on your CRM configuration (image below). You can re-use that number as the unique ID for your record and delete the Campaign record that was created. While this is a simple method that can be implemented without coding, the numbers are not really sequential, “garbage” records are created at a cost (create/delete transactions time, what happens if you want/need to use that entity in the future?..). To be honest, it feels like a quick-and-dirty way of doing things, but it works.

The advantage here is that because the auto-numbers are platform generated, so you do not have to worry about race conditions in theory.

Use a model based on CRM Synchronous Workflow + Custom Auto-Number entity

I will not go too much into details about this one. There are lots of articles on the topic. A few “how-to” articles for reference:

• Auto-Numbering Solution for CRM 2013 Online from The MS CRM World Blog
  
• The Power of Synchronous Workflows For Autonumbering on Shan McArthur’s Blog
  
• Jukka’s “Auto-Numbering with CRM Workflows: Real-Time vs Asynchonous” article
  
• Eran Fuks’s LinkedIn article “Custom Auto-Numbering in 6 quick steps“

Bottom line, this is a great solution if you cannot write any code and don’t want to spend any money for something that should be standard in the platform (yes, I’m ranting, can’t help it). That being said, it you have race conditions, I’m sure you can have a few duplicated numbers with this solution.

Using a 3rd Party Tool

There are plenty of options out there. Some are more flexible than others. Some explicitly guarantee uniqueness, some don’t really mention it. Some are free, some are not.

• CRM Innovation’s AutoNumber Solution
  
• IOTAP’s Advanced Auto-Number for Dynamics CRM
  
• Open Source (free) : CRM 2015 / 2016 Autonunmber,  Celedon Autonumber Solution for Dynamics CRM
  
• Formula Manager form North 52

These are just a few, a research will allow you find more of these and decide the one that fits your needs the best.

Build your own solution

At this point, you can do it anyway you want.

• If you have access to an external database, a CRM plugin that create a record in the database with an identity column will guarantee to always have unique and sequential numbers. This comes at a cost obviously (external database dependency, speed)
  
• There is also a smart way of doing robust auto-numbering suggested by Ken Heiman of Green Beacon here. Good read, smart idea.
  
• Some enterprises already have Auto-Numbering web services, you can simply consume that web service from a CRM plugin as well (same cost as database, dependency, speed)
  
• I have also seen the idea of using the Guid as a unique identifier, by copying the value into a text field with a plugin. I don’t think it’s readable, but definitely unique.
  

 

It had been too long. Happy CRM’ing ! 

Custom User Interface in CRM – HTML5/JavaScript or ASP.NET Web application?

Lots of times on CRM projects, we are required to build and display custom screens inside of Dynamics CRM. There are a lot of factors that come into play in the choice how we build these custom user interfaces. It is a very key decision to make because it can sets the standard technologies and tools that you use for custom screens on project (among other things). I thought I’d share some key points to consider in your decision making process to determine what technologies and tools to use.

The need for custom user interface

First, it is important to know and understand when it is required to build custom UI as part of your CRM solution implementation. As a wrote a few years ago, we sometimes find ourselves in situations where we can come up with a complex data model and we feel like the out of the box UI capabilities won’t be user friendly enough to drive user adoption. Situations like that create the need to build more user friendly interfaces to simply make people’s lives easier when they start using the CRM/XRM application. Some examples are

• Custom calendar view for timekeeping
• Tree view to display a complex hierarchy
• Interactive Bing or Google maps views of CRM data
• Editable grids
• Complex grids with aggregates, grouping functionalities
• Integrated views to other systems

And the list goes on. The needs are very different depending on the nature of the enterprise looking to implement a CRM/XRM solution.

Most common technology choices

From experience, I have seen a lot of custom screens built using the following tools/technologies:

• Silverlight
• ASP.NET / ASP.NET MVC
• HTML/HTML5 + JavaScript + CSS

The screens are usually displayed in web resources or in an IFRAMES inside CRM on forms or as standalone modules (open from a button, display in sitemap area). I will not cover Silverlight since Microsoft announced its end of life and continued support for a few additional years, plus the world has started to move on.

ASP.NET / ASP.NET MVC

• Pros
  • Flexible server side business logic
  • Easily integration with external systems (web services, databases etc.)
  • Can be very quick to develop depending on the requirements, expertise and accelerators (advanced controls provided by Telerik, Infragistics or others can speed up development time significantly)
  • Develop with a familiar programming language (C# + Visual Studio) – this of course depends on your development background
• Cons
  • Requires the deployment and maintenance of a hosted web application
  • You have to deal with the weight of ASP.NET (ViewState, Postbacks etc.)
  • Not a great fit with CRM Online and cloud offerings in general (cloud deployment, cross origin web sites, integrations etc)
  • Required additional configuration in CRM + JavaScript to set the IFRAME’s sources (URL)
  • Effort for developing custom web applications is often under-evaluated

HMTL(5)/JavaScript

• Pros
  • Usually, a much lighter solution and if done properly, custom UI components can be very responsive and provide great user experience
  • All components are in CRM Web Resources (html pages, JavaScript files, images…)
  • Custom screens are CRM Solution aware and there is no need for deploying and maintaining an additional web site on a web server
  • A definite plus for cloud based Dynamics CRM implementations
  • Can be very quick to develop depending on the requirements, expertise and accelerators (Kendo UI controls or others can speed up development time significantly; other frameworks like Knockout JS, Bootstrap can also be useful)
  • Leverage trending technologies and tools
  • Use any IDE you want (no obligation to use Visual Studio, even if you should J)
• Cons
  • Possibly additional complexity if you need to display information from an external system
  • If not structured properly, JavaScript code can become massive and hard to navigate/maintain
  • For non-web developers, might be a steep learning curve
  • Need to establish good project structure and standards (e.g. use TypeScript or Script Sharp)

What else to consider?

There are other factors to take into account, some being specific to each organization. A few examples:

• What is the expertise or your resource pool?
• Do we have people to build and maintain what we deploy?
• Are we in CRM Online or planning to move to CRM online?
• Are we, as a company, trying to get into HTML/JavaScript more?

This is good food for thoughts for starters. If you want more guidance, feel free to reach out to us.

Using Full-Text Search to improve Dynamics CRM search experience

Consider the following scenario: You need to perform email searches in your CRM and your search needs to look at the content (body) of your email messages. When you start having a lot of emails in your CRM (500k+ in my case), searches become extremely slow and you need to start to look at indexes or other solutions to make the search faster and keep your users happy. This post describes a solution that leverage a new CRM feature in CRM 2015 Update 0.1 (On Premise) : Full-text search.

Enabling Search on email body

This is the easy part. If you are having this issue, you have probably done this already. To enable the ability to search on the email body, you need to add the “Description” field as a “Find Column” in the Quick Find view of the Email entity, as shown in the screenshot below. After completing that step, save the view and publish the entity to make the change available in the system.

Searching through emails

Now that we’ve configured the search column, we can do a quick search to get our emails by using the search box on the activity list view view or by using the “Global Search” on the top right of the top navigation bar, next to the “Quick Create” button. Note that emails are not enabled in the “Global Search” by default so you’d have to add them (not required).

If you have a few emails in your system, results are going to come back quickly. In the system that I tested against, I had about 700.000 emails in CRM so the search result took on average just over 30 seconds to return results (and I consider this good, because I’m running on SSD drives). That being said, in real life, 30 seconds or more of waiting time is enough to make users think that the system is frozen, or to just stop trusting in the application, especially if they rely heavily on this type of search.

Making search return results faster

If you are using CRM 2015 On Premise, you are in luck. In CRM 2015 Update 0.1, the product team introduced the ability to enable full-text search for Quick Finds queries. To enable it, go do Settings –> General –> Set up Quick Find. There you’ll see the option to “Enable full-text search for Quick Find.”

Greater details about the feature can be found here and you can also take a look at this video on the Microsoft Dynamics YouTube Channel.

Important points to keep into consideration before enabling full-text search:

• Full-text indexing for Quick Find uses SQL Server full-text indexing
• It can take up to 24 hours for the system to enable or disable full-text search, or add and remove find columns (maintenance job has to run on the server and make the adjustments specified in the CRM configuration)
• As a bonus, you should also activate the Org Settings “EnableQuickFindOptimization” and “EnableRetrieveMultipleOptimization” in order to get the best results. Thanks to Jean-François Cantin for that indication. If you don’t know how to update the Org Settings, here is a reference.

After waiting long enough time for the emails to be indexed, the email searches started to return results within 5 seconds. Drastic improvement. The same solution was implemented into the client’s database and it seems to have made a huge improvement. 

Have fun.

Leverage the power of Power BI with Dynamics CRM data

Yesterday I attended a presentation from felow MVP Sophie Marchand about Power BI. I had previous exposure to Power BI when it became generally available for free as part of the Office 365 suite. The content of the presentation was around multiple areas in Power BI, so I thought I’d revisit some of them and put some thought into how we could leverage the power of the Business Intelligence platform to manipulate and play with Dynamics CRM data.

Connecting Dynamics CRM Online to Power BI (web)

This area is well documented. When you create or log on to you Power BI account, you can get Dynamics CRM Online data by going to the “Get Data” section, selecting “Services” and finding the Microsoft Dynamics CRM content pack.

Once you click on Connect, you will need to provide your Dynamics CRM Online OData Service URL (make sure you don’t leave a trailing slash in the end).

Once this process is completed, Power BI will connect to your Dynamics CRM Online Organization, get a set of data and build a Sales Manager Dashboard. That dashboard is pre-configured as part of the native integration between CRM Online and Power BI. Keep in mind that you can also connect to CRM Online from Power BI for Desktop. The way the data is structured is a bit different and you don’t get the Sales Manager Dashboard by default. That being said, it bring the data in using the OData Service and gives you the ability to create your own visualizations and reports. Let’s take it a step further and see what options you have if you need to manipulate data from custom entities and create visuals, dashboards that are useful for your business.

Custom Dynamics CRM data sources for Power BI

If you want to leverage Power BI, which you should in many ways, there are other ways to connect it to your CRM data. Here are some of the options that you have.

Extract Dynamics CRM in Excel Spreadsheets and Import the files to Power BI

Power BI (web)

Power BI for Desktop

For CRM On-Premise organizations, using Power BI for Desktop, you can simply connect to your CRM Database start creating for dimensions, cubes and reports from there. If you need more information on how to get started with Power BI for Desktop, make sure you check out Microsoft’s article here.

Worth noting, you can export your Power BI for Desktop content and upload it to the Power BI web platform. This gives you the ability to view the dashboards online, question it differently and also share it with people in your organization. In the Dynamics CRM and Microsoft ecosystem, it has never been easier to build reports and intelligence around your operational data. Enjoy!