Wednesday, May 16, 2018

Some Ideas for Extending Dynamics 365 (CRM) with Microsoft Azure

By pairing Microsoft Dynamics 365 (CRM) and Azure, you can work smarter and faster to maximize sales and customer satisfaction.

There are thousands of real-world use cases for extending Dynamics 365, and your business, with Microsoft Azure. Here's just a few of those that I've either built for clients or have found interesting and valuable.

  1. Schedule jobs (data updates, rollups, workflows, emails, reminders) to run any time or day using Azure Logic Apps, WebJobs, Azure Functions, Azure Scheduler and other services. If you ever find yourself saying "I wish we could to XYZ every last Friday of each month", or something similar, then know that Azure opens up all of those scheduling possibilities.
  2. Copy all or some of your Dynamics 365 Online data to SQL Azure. Use a WebJob or Worker Role to periodically copy data to SQL Azure for further analysis, reporting, and backup.
  3. Tweet-o-matic! Use Azure Logic Apps to respond to a change in Dynamics 365 to send out a Tweet. The event from CRM is sent to an Azure queue. The Logic App picks up the message and formulates and sends out the Tweet. Or reverse the process: Use a Logic App to listen for tweets and update CRM accordingly.
  4. Host a custom website (public or internal) that reads and writes CRM data. With Azure Web Apps, your page will be delivered fast and reliably, no matter how many visitors appear.
  5. Dynamics 365 workflow not getting the job done? Further automate your business processes with an Azure Logic App. Easily add actions and conditions, interact with Facebook, Twitter, Dropbox, OneDrive and many more services. And if you need some advanced decision making or external data lookups then create an Azure Function to do that work and provide the results back to your Logic App.
  6. Clean up your data! Run a data quality tool on an Azure VM and update Dynamics 365 with the results.
  7. Create a custom report for a CRM dashboard. Schedule an Azure WebJob to build an HTML-based report and store it in CRM as a Web Resource. Add the Web Resource to a dashboard to provide employees timely information to make strategic decisions.
  8. Search the contents of all emails and attachments. Send emails to an Azure queue. Store the content in Azure Blob Storage. Use Azure Search to quickly find files and emails by keywords.
  9. Store survey data in Azure Cosmos DB and search for it from a CRM form. This keeps survey data out of your CRM storage but still accessible for display and analysis.
  10. Send a WebHook from a CRM workflow to an Azure Web App that returns product payment and shipping status.
  11. Store CRM metadata in Azure Redis Cache. Multiple Azure cloud services (worker roles) use this cache while processing data to validate data based on CRM field data type, picklist values, field lengths, etc. Caching CRM metadata will allow your custom websites and integrations run faster.
  12. Move your CRM file attachments to Azure Blob Storage to save money (CRM storage can be expensive). Provide a custom grid in CRM to list and load the attachments.
  13. Aggregate (e.g., using Azure Data Factory) survey responses, feedback, social media content etc. and provide a full-text search capability within Dynamics 365, using Azure Search, to help facilitate decision making.
  14. Predict opportunity win/loss probability for a prospect based on existing CRM data using Azure Machine Learning. Examples: 1, Templates
  15. Run a CRM on-premises instance on an Azure Virtual Machine.
  16. Use Azure Automation to move data from Dynamics 365 via PowerShell to Power BI.
  17. Integrate CRM data to any other system using Azure BizTalk services. For example, send product orders from CRM to an Azure queue and allow BizTalk to send the data in EDI format to your shipping company.
  18. Provide instant product recommendations to your customers using Azure Data Factory along with your CRM data.
  19. Host Adxstudio portals in Azure.
  20. Send customer feedback to Azure to gauge the sentiment of messages to help sales, customer retention, support and more.
  21. Run packaged enterprise applications on an Azure VM, such as SQL Server Enterprise Edition, SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS).
  22. Be your own Trailblazer: Improve your business with Dynamics 365 and Azure.
  23. Feed your CRM real-time data from any type of device with Microsoft's Internet of Things (IoT) technologies running in Azure.
  24. Send your CRM data (e.g., new case/incident records) to an Azure Service Bus queue. From there, a WebJob can process the data and send it to your ERP system.
  25. Create custom help pages in CRM for users to get more specific information about your CRM system. Host the help pages in an Azure website.
  26. Provide single sign-on to an Azure-hosted ASP.NET website. Provide your custom page in an IFRAME.
  27. People still love receiving data in Excel format. To automate the delivery of Excel-based data to your staff, create a WebJob, Worker Role or similar in Azure in conjunction with Aspose.Cells for Cloud. Store the Excel file in Azure storage and send it using Azure Logic App's Office 365 email feature.
  28. Provide video-based CRM training using Microsoft Stream. Customize CRM's help pages to show the video content. This could provide a much more effective way to provide CRM training compared to paragraphs of text and screenshots.
  29. For CRM administrators who want to be notified about plug-in issues, one idea is to create an Azure Logic App that monitors (polls) CRM's Plug-in Trace Log (plugintracelog) entity for records where the Exception Entity field has a value (in other words, plug-ins that failed). The Logic App could send an email with the plug-in tracing details so the admin can take action... even before an end-user sends that "hey, something's broken" email to IT.
  30. Overcome the two minute execution limit for CRM plug-ins by using an Azure service such as a WebJob or Azure Function -- where your operation can run for much longer when needed.
  31. Create and schedule an Azure WebJob that periodically queries CRM metadata and sends out an alert when something important has been created or modified. Store the metadata in Azure SQL and use T-SQL to quickly identify differences. Where this could be valuable is in a CRM organization where admin or customizer rights have been granted to several people and the main admin wants to keep a handle on how CRM's structure is changing over time.
  32. Automate the start up and shut down of an Azure Virtual Machine to handle periodic tasks that require a full VM. For example, let's say that you want to run an SSIS process each night (perhaps using KingswaySoft) to copy large amounts of your CRM data to a local SQL Server instance but you don't want to pay for a VM that runs 24x7. By scheduling the VM startup and shutdown, you can use (and pay for) only the computing time you need.
  33. Create an Azure Logic App to send a reminder email to CRM users when a Task is due.
  34. If your organization is maintaining or building custom applications, using Azure's platform-as-a-service (PaaS) offerings has the following benefits according to a Forrester study published in 2016: 1) IT administration resource savings, 2) Avoided and reduced IT resource costs, 3) Improved service deployment time to market, 4) Application testing resource savings, 5) New business opportunities. When analyzing your business processes and related costs, if there's an application, integration, website or other solution that could save your organization time and money, then Azure PaaS is there with the capabilities needed to make it happen.
  35. Implement reliable auto-numbering to CRM records using an Azure Logic App, Azure Function and a Storage Queue. The idea is to seed a storage queue with the numbers (or unique strings) you want to apply to new CRM records. Create a Logic App that runs on create of CRM records. Call the Azure function to get the next value from the queue and then update the CRM record from the Logic App.
  36. Microsoft Azure to Dynamics 365 is like having a Home Depot nearby for your house: your home is livable but to make it better takes buying some tools and pounding some nails. The promise of Microsoft Flow is to turn those tools that require some skill into automated power tools, where you point, click and get great results. Combining Microsoft Flow and Dynamics 365, opens up hundreds of possibilities for improving your business processes. It's now within the reach of non IT staff to further automate data entry, document preparation and delivery, data analysis, reporting and much more.
  37. This page provides examples on how you can add functionality to Dynamics 365 with Azure Logic Apps. However, for reasons such as complex business logic, there are scenarios where a Logic App can't retrieve or update the required CRM data. Fortunately, it's possible to combine Azure Logic Apps with Azure Functions to build out advanced workflow processes.
  38. Are you hiring a consultant to work on your CRM or related systems? To make sure you retain the source code and can further develop the solution, set up an Azure Virtual Machine with Visual Studio and other needed tools and have the consultants remote connect to the VM to do their work. You can create a Visual Studio Online account and use the source control engine it provides.
  39. Create an Azure Logic App to send an email alert to you when a system job (run by the CRM async service) ends with a "Failed" status. This allows the system administrator to get ahead of a problem before it becomes more widespread.
  40. For auditing, security or job performance reasons, if you'd like to know who opened a particular CRM record, one idea is to create a CRM plug-in that runs on the Retrieve message. This plug-in can send a message to an Azure Service Bus queue and an Azure Logic App or Azure Function can then write the record id, user id, and date and time to an Azure Storage Table.
  41. Get a text message when an Opportunity in CRM is won or lost. To do this, create a CRM Service Endpoint that sends a JSON message to an Azure Service Bus queue when an opportunity is updated and the statuscode is set. In an Azure Function, parse the JSON, check the values and if the opportunity was won or lost then invoke an Azure Logic App to use Twilio to send the opportunity details to you in a text message.
  42. Analyze CRM plug-in trace logs. Azure WebJob runs every hour to get CRM plug-in trace log record and writes statistics (plug-in name, event, total execution time, trace text, exception, etc.) to SQL Azure table. SQL queries provide aggregate statistics such as which plug-ins are running the most often, which plug-ins are taking the most time to finish, which plug-ins are throwing an error, etc.
  43. Allow for an audit of CRM data changes or retrieval by sending JSON-based messages from a CRM Service Endpoint to Azure Cosmos DB. You can "fire and forget" the data to Cosmos DB and later query the data when needed.
  44. To simply add a note to a CRM record, such as an account, contact or opportunity, there are several clicks involved in the CRM UI and this can become a barrier to getting important details saved in CRM for others to view and act upon. Microsoft Flow, hosted on Azure, provides opportunities to streamline the process of saving notes, and other information or files, in CRM. The Flow template named "Attach OneDrive files about Dynamics Contacts as notes" is an example of how you can use Flow to allow users to simply type notes into folders and have the Flow do the work of saving the note to CRM. Or imagine a Flow that reads data from Excel Online uploaded to OneDrive and updates the corresponding record(s) in CRM.
  45. Many businesses would like to automate the process of getting Excel calculations into CRM without the need to write a CRM plug-in or other type of custom application. One no-code way to do this is to use an Azure Logic App to query CRM for the needed data, save it to Excel Online, retrieve the calculated value(s) and save the information back into CRM.
  46. Start an Azure Logic App from a CRM workflow. One way to do this is to create a custom CRM entity that will be used to send commands to an Azure Logic App. The CRM workflow creates a command record and a Logic App gets the command and runs through its process. This is one way to extend CRM workflows into the cloud.
  47. Some Dynamics 365 Online users are saving storage space by moving note and email attachments to external storage, including Azure blob storage. Others still want or need attachments within CRM's database but would also like to free up space and avoiding paying for more. One idea for this is to write an Azure WebJob that will run daily to retrieve new attachments, zip them, and save them back into CRM. A password could also be applied to the files while zipping them, adding extra security if this is needed.
  48. Send encrypted emails from CRM workflows using Azure Rights Management service
  49. Query on how values in CRM change over time. Send data changes to an Azure service that then stores the changed value in an Azure SQL Temporal table.
  50. Search your CRM data with dtSearch in Azure. If you need to instantly search hundreds of gigabytes or terabytes of files of all types, using advanced querying such as fuzzy and proximity searching, check out the various dtSearch products.
  51. Is CRM's FetchXml giving you the blues? Feel like one hand is tied behind your back when trying to query your CRM data? Why not use a tool like Skyvia to copy data to SQL Azure so you can break free of the FetchXml limitations! (Or, do like I've done, use the CRM SDK, C#, Entity Framework and SQL Bulk Insert to create your own ETL solution.)
  52. Your car has sensors, so why shouldn't the system that runs your business (and pays for your car) have sensors as well? Too often, I've seen CRM workflows, integrations, plug-ins etc. that are built to serve an important purpose in CRM, but are then deployed without any mechanism to report on whether or not they're still working. Azure can help with this. It has various schedule-able services, such as Logic Apps, Azure Functions and WebJobs, that you can use to periodically query your CRM data and detect whether expected data is in place. For example, if you are expecting a plug-in to write custom records each day but that stops for some reason, an Azure-based monitoring app can send a high-priority e-mail out to you so that you can proactively determine the cause and fix the issue.
  53. Allow your customers to use their voice and messaging for self-service. The Microsoft Bot Framework provides a platform for organizations to build applications—bots—that consumers can interact with, easily, conversationally, over voice or text, whenever convenient.
  54. When you have the need to quickly build and deploy a custom .NET app that runs on a schedule, sends/receives queue messages, sends emails and/or reads/writes to Azure blob storage, you can have the app running in an hour or two using Azure WebJobs along with the WebJobs SDK.
  55. Keep on top of CRM news, tips, etc. with Twitter and Microsoft Flow. One way to improve your CRM system is to increase your knowledge of the product and related tools and technologies. Each day, there are 200+ Tweets sent out for hashtags #MSDynCRM, #Dynamics365, #MSDyn365, etc. You can use Microsoft Flow to listen for those Tweets and send a copy of them to you by email. Create a rule in your email app/system to move the Tweets to a new folder and you'll always be on top of the latest news!
  56. There are hundreds of possible uses for Azure Functions. Here's a presentation that shows several real-world scenarios.
  57. Use Microsoft PowerApps to build custom line-of-business applications that bring together data from CRM, NAV, Common Data Model and other platforms.
  58. Dynamics 365/CRM to on-premises SQL Server using Azure Logic Apps.
  59. Create your own API in Azure Functions. Build an API to for off-loading Dynamics 365 or other data operations. You can take advantage of the endless scalability and processing power of Azure, all with your own custom API endpoints running as low-cost Azure Functions.
  60. For complex business workflows or those that will likely run for a long time, consider using Azure's Durable Functions. Consider Durable Functions when it's necessary to orchestrate business processes that are external to Dynamics 365 (e.g., flows involving OneDrive, SharePoint or any other service or database) or when they will make developing a business process easier to monitor and cancel if necessary. One use case is to create a Function that waits until specific records in Dynamics 365 have data and for one or more files to exist in OneDrive.
  61. Send out a daily e-mail with aggregate values from Dynamics 365. For example, send a count of new accounts, opportunities, open and closed cases, etc. to the company's management by e-mail to keep them informed. One idea for doing this is to use a Logic App that 1) retrieves FetchXML aggregate queries from Azure Blob Storage, 2) loops through each FetchXML file, 3) Sends the FetchXML query to an Azure Function to execute and return the aggregate value (using a server-to-server connection to D365), 4) append the returned value to an HTML table and 5) send the resulting values in an e-mail.

Sunday, January 21, 2018

Using Microsoft Planner to organize a Continuous Learning Plan

Have you used Microsoft Planner yet?

Planner is one of those apps in Office 365 that doesn't get much attention, but it's actually quite useful. You can use it to run different types of projects at work, from a software implementation project to planning a move to a new office.

The team members added to a plan will know what they need to do, when tasks are due, the status of each task, etc. Plus, each person can add notes, files, checklists and links, so that everyone is working with the same information.

Planner is great for teams, but you can also use it for individual projects and goals.

One never-ending project that most of us have (or should have) is called "lifelong learning" or "continuous learning". Managing this kind of project, though, is not easy. Throughout the day, you run across articles, whitepapers, books, videos, Tweets etc. that you'd like to read at the time, but deadlines, meetings and other priorities keep this from happening. But where can you keep a record of what you want to learn, all in a single place, organized by category? How about... Planner?

Planner can help you create a continuous learning plan by:
  • Organizing books, articles, whitepapers, links, etc. by category ("buckets")
  • Track items that you've started reading and those that you haven't
  • Flag (label) items as "Now Reading", "Next Up", "Favorite", etc.
  • Create a web link to the resource
  • Keep a running set of notes for each item, or maintain an expanded set of notes in the attached OneNote file
  • Search notes through Outlook
  • Add an image (i.e., book cover) for quick recognition of a resource
  • Share your learning plan with others (read-only or co-editing)
Here's a look at my Continuous Learning Plan in Planner so far:

I chose to use the "bucket" concept in Planner to organize learning materials by major category, such as Dynamics 365, Microsoft Azure, Architecture Patterns, .NET/C#, JavaScript, Inbox, Miscellaneous, etc.  I am then applying labels to items so that I can find materials that I'm in the middle of reading, materials that I want to review next, etc.

Another idea is to use Microsoft Flow and Microsoft Forms to automate the creation of items in your Planner board. With this automation, when you come across an article, whitepaper, etc. that you want to add to your learning plan, simply open your Microsoft Form, post a link or description and save the form. Then, Microsoft Flow can take that text and post it to your Planner board. This is a more convenient way to add new items to the board.

A Planner board like this might finally give you a place to organize your technical resources and create a strategy for a lifetime of learning.

Friday, December 29, 2017

Debugging JavaScript in Dynamics 365 Outlook App

Here's a quick tip on how to debug your custom JavaScript code in the Dynamics 365 App for Outlook
  1. Right-click in the page/form shown by the Dynamics 365 app and select the Debug option.
  2. Select Visual Studio as the debug tool to use.
  3. In Visual Studio, in the Exception Settings window, select all options/boxes for the JavaScript Runtime Exceptions option.
  4. In Visual Studio, press F5 to continue running the Outlook App. Perform an operation that leads to the exception/error in your code or to a "debugger" statement that you've added to the code.
  5. You can then proceed to step through the code, get variable values, etc.

Tuesday, August 15, 2017

Identify differences between Dynamics 365 (CRM) instances


You want to identify differences (entities, fields, picklists, etc.) between two CRM Online instances (e.g., production and a sandbox).

Possible Solution

You might be able to follow the approach detailed on Develop 1's blog, but if your default solution zip file never appears or you get an error (as I do quite often these days with the default solution), then here's another approach.


Note that these steps are targeted toward CRM developers. That is, until someone writes an XrmToolBox plugin that does the same thing or in a better way.
  1. Export entities as solution zip files: The Dynamics 365/CRM SDK contains an article named "Work with Solutions" that provides source code samples for creating and exporting solutions. The first step in the diff process is to write an app that creates a temporary solution in the instance, adds an entity component to it, exports the solution (as a zip file) and then moves to the next entity, and so on. This will result in all entity components (and their dependencies) in solution zip files.
  2. Extract the solutions to sub-components: Use the SolutionPackager tool that's included in the SDK to extract each entity solution zip file to individual sub-component files. You can use Excel to build a batch file that will run the SolutionPackager utility for each solution zip file, or better yet, run the SolutionPackager from within your app.
  3. Follow the steps above for a second CRM instance: Now that you have all entity components extracted on disk, you can run steps 1 and 2 again for a different CRM instance. Or run your app twice at the same time, one pointed to production and one to the sandbox.
  4. Use a file diff/comparison tool to identify differences: You can now use a diff tool such as Beyond Compare to diff the two sets of folders. This will reveal metadata differences in both instances.


  • By exporting entities along with their required components (that's an option when adding components to a solution programmatically), you'll end up extracting most components that relate to entities. However, you might also want to export other component types such as all processes (not only those that have entity dependencies), global picklists, web resources, etc.
  • It might take an hour or two for the app you create to export all entities as solutions.
  • You can add the extracted solution files into a source control system to keep track of changes over time.
  • You can index and search through the solution zip files or extracted files using a grep or indexing tool (e.g., dtSearch) to track down a component that might be giving you trouble while importing or exporting larger solutions.

Tuesday, June 20, 2017

Dynamics CRM workflow editor bug might be related to editable grids

The problem: While creating a workflow process in Dynamics 365 (CRM Online), the "Form Assistant" drop-down boxes (i.e., where you can select the "Operator" or dynamic values) were empty/blank.

What we tried: We tried these steps to troubleshoot the issue: 1) click into a variety of fields to see if the problem is related to specific types of fields (no, the options are blank for all field types), 2) clear browser cache, 3) try IE, Chrome and Edge, 4) determine whether the problem is happening for all entities.

Some clues: Regarding #4 above, the problem with the blank options when editing a workflow step occurred for some entity types but not all. So what was common between the entity forms where this bug occurred?

The fix: It turns out, at least for the CRM organization we're working with, that if there's an editable grid on the form, then the Form Assistant controls do not get populated. We removed the editable grid, saved, published and then tried editing the workflow step again. That resolve it: The expected operators, field choices, etc. are appearing in the workflow step edit page again.

This might be a coincidence, but this work-around is worth trying if you run into the same problem.

Monday, June 5, 2017

Prototype and fail early with cloud-based development

In these days of rapid and incremental software product releases, where development companies add features for our benefit but often before thoroughly testing them, it's crucial that we all approach projects in a prototyping mindset and find the bugs early rather than assuming that all released features are going to work as we progress through an application.

It used to be that when you bought a license to, for example, Microsoft SQL Server, you were confident that any problems you encountered were likely due to your own code.

These days, it's difficult to know whether a bug is due to something you're doing wrong or whether it's a "known issue" to the software manufacturer due to releasing functionality too quickly.

My latest example of this relates to Microsoft PowerApps. I created and finalized several screens and moved on to the last part of the project and that was to simply list records from a Common Data Service (CDS) entity that relate to a record selected on a previous screen. This is fully supported and Microsoft provides examples on how to do this.

Then I ran into this "known issue": "For some connectors, the connection to the data source is lost if you modify the Items property."

In my case, I selected the connection to the CDS entity for a data table and went to apply a filter and the data source disappeared. I thought that I was doing something wrong so I re-read the documentation on using filters and the best I could tell I was using the functionality the way it was designed.

Then I searched online some more and eventually ran into the issue.

I'll likely find a work-around to this bug, probably by storing the related data onto the main CDS entity itself.  But this is another reminder to myself, and hopefully to you as well, to approach application development with cloud-based platforms in a way where you prototype as much of the functionality first before spending a lot of time polishing various forms/screens so that you can switch gears on your design when you run into an issue like the one I described.

Saturday, June 3, 2017

Checking for existence of record in Azure Logic Apps

I recently had the need to store data into an Microsoft Common Data Service (CDS) database, and using a Logic App seemed to fit the need in this case.

Project requirements:

- Run the integration (data copy) once per day
- Query Dynamics 365 (CRM) for Project records
- Insert or update into the Common Data Service database, to the Project entity

I'm probably missing something obvious, but that last step was more difficult to figure out than I thought. I created a "For each" to loop through the CRM records and then wanted to query the CDS entity to determine whether or not the record already exists. If the record exists, update it, if it doesn't exist, create it.

To query the CDS entity to determine whether the source CRM record exists, I used the CDS "Retrieve record" action. When running the Logic App, though, when it encountered the condition where a CDS record didn't exist the Logic App stopped processing and set the instance as "Failed".  Not finding a record really shouldn't be considered a failed job, but that's the way Logic Apps handles this condition.

The way Scribe Online handles this type of query is to set a property (e.g., RecordsMatched) to indicate whether or not the record exists. You can use the property's value in IF..THEN formulas to change the runtime processing path.

In Logic Apps, I couldn't find a simple way to set a condition based on whether or not it found the CDS record. The solution I came up with was to check the HTTP status of the query to the CDS. A response of 200 means that the record exists and a 404 means the record does not exist.

For the "IF" statement in the Logic App (after querying for the CDS record), I switched to "Code view" in the Logic Apps Designer and set the expression to the following:

"expression": "@not(equals(outputs('Attempt_to_retrieve_Project_CDS_record')['statusCode'], 200))",

Here's how that change looks in the Designer view:

From there, I added an action to insert the CDS record if it doesn't exist or update it if it does exist.

This solution was confusing because I couldn't find any examples or documentation from Microsoft on how to deal with this common scenario. I found an article on dealing with "exceptions" using the runAfter setting for an action, but checking for a record and not finding it really isn't an exception, it's a simple operation that should, in my opinion, be easier to handle in Logic Apps. Maybe it is and I just missed it. If so, please let me know.