Get Tweets in Email, Send to Yammer: Automated with Microsoft Flow

Automation success! I feel like a weight has been lifted from my shoulders. I used Microsoft Flow today to automate a few tasks that have been on my list for a while.

The Goals

• Keep up with CRM happenings via Twitter without wading through hundreds of "noise" Tweets.
• Receive a filtered set of Tweets into an Office 365 email folder for easy access and review.
• Share my favorite Tweets (and the linked article) with others at Altriva.

The Solution

Microsoft Flow provides the ability to act upon new Tweets based on keywords that you specify.  I created Flows to trigger on Tweets with hashtags #MSDynCRM, #MSDyn365, #Dynamics365, etc.. The Flows send the Tweet message and related details to my Office 365 email. In Outlook, I set up rules to move the emails to a specific inbound folder.

A few times each day, I go through the Tweets (as emails) and move any of them that I want to review further to a "To Read" folder.

After I've had a chance to review the Tweet and the related article, if I think my colleagues at Altriva might also want the information, I drag the Tweet (email) to a folder named "Post to Yammer".  I created another Flow to periodically retrieve the emails in that folder, post the Tweet to Yammer and then delete the email.

Conclusion

I've been critical lately about Microsoft Flow, particularly regarding the several bugs I've run into in dealing with data from Dynamics 365 CRM, but it works very well for a lot of other types of tasks. If you find yourself doing the same repetitive tasks (copy/paste is a big red flag for this) then take a look at Microsoft Flow and see if you can connect the dots to automate things. It feels good when it's all working.

A fix for Azure Logic App and Dynamics 365 CRM connector

A project came up for me recently where the requirement was to insert data from a Dynamics 365 CRM Online organization into a Google Sheet, once per day. Using Azure's Logic Apps for this seemed like a perfect fit. Logic Apps provides a connector for CRM and Google Sheets, and it has a built-in way to run the app on a schedule.

I was able to put together the structure for this Logic App using the visual designer in just a few minutes. However, when I clicked to run the app, it failed with the following message:

Unable to process template language expressions in action 'Update_file' inputs at line '1' and column '11': 'The template language expression 'body('List_records')['msft_date']' cannot be evaluated because property 'msft_date' doesn't exist, available properties are '@odata.context, value'. Please see https://aka.ms/logicexpressions for usage details.'.

Fortunately, Logic Apps provides a "Code View" that allows you to view and edit the underlying code behind the Logic App. I searched for the problematic "msft_date" string and found it in the code here:

"body": "@{body('List_records')['msft_date']},\"@{item()?['msft_description']}\",@{item()?['msft_hours']}"

That text came from the Logic Apps designer.

After reading through the Workflow Definition Language for Logic Apps, it became apparent that the "@{body" part of the syntax was not correct.

The fix to this bug was to change that line to the following:

"body": "@{item()?['msft_date']},\"@{item()?['msft_description']}\",@{item()?['msft_hours']}"

After making that change and saving the app, it now runs fine. Data from CRM is making its way to a Google Sheet on a regular basis.

Lessons learned on this project include:

• For a lot of tasks, Logic Apps works fine. But don't assume that it's a solid platform at this point. It is not SQL Server. It is clearly not going through the same level of QA that other Microsoft enterprise products go through.
• Some of today's GA (general availability) cloud apps would've been considered Beta back in the day. Explanation: When I worked at Asymetrix (Paul Allen's first company after leaving Microsoft), the engineering, QA and support teams would almost get to the point of throwing punches in battles over product quality vs ship dates. I remember working past midnight on several occasions closing out all of my bugs (even small ones) after QA won the latest screaming match in the hallway. As a team, though, we were all working toward the same goals: feature-rich applications that were as solid as we could make them. With Logic Apps, I'll just say that I don't think the same battles are happening. Maybe they should. (Note that this opinion isn't coming from just this one bug I found, but several others.)
• Get to know the Workflow Definition Language -- the underlying structure of a Logic App. The visual designer only presents a small amount of the functionality that Logic Apps offers. For example, there are data conversion and other types of functions available to enhance a Logic App.
• Don't write off Logic Apps due to a few bad experiences. I was recently in a meeting and the general consensus was that Logic Apps needs another year before a lot of the people will consider it for a "real world" project. I think that's wrong. It's useful for a lot of projects today, if you can live with occasionally fixing bugs introduced by the designer or getting creative to work around some limitations (e.g. currently, no way to add rows to an Excel Online file).

If you come up with some ways that you've used Logic Apps, particularly with Dynamics 365 CRM, let me know. I'd love to hear about your experiences with it as well.

Use T-SQL to Parse Dynamics CRM Form XML

It's possible to use SQL Server (including SQL Azure) to parse the XML for CRM forms. With the form's XML, you can run a query to, for example, list all JavaScript libraries applied to a form.

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(max)
SET @XmlDocument = N'YOUR CRM FORM XML GOES HERE'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT *
  FROM OPENXML (@DocHandle, '/form/formLibraries/Library',1)
      WITH (name varchar(100),
            libraryUniqueId varchar(50))
EXEC sp_xml_removedocument @DocHandle

Or list all tabs on the form, as in this example.

DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(max)
SET @XmlDocument = N'YOUR CRM FORM XML GOES HERE'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT *
  FROM OPENXML (@DocHandle, '/form/tabs/tab',1)
      WITH (name varchar(100))
EXEC sp_xml_removedocument @DocHandle

Benefits of copying Dynamics CRM Online metadata to SQL

Dynamics CRM is, to a large extent, a metadata-driven platform, as illustrated here. What this means is that a lot of its functionality is derived by business entities, their relationships, fields, picklists, etc.

Given the importance of metadata in CRM, you'd expect that you'd be able to write a query against it, such as "List all account fields that are not on a form." or "List all web resources and the forms, if any, each one is used on.". Unfortunately, these types of queries aren't possible without writing a fair bit of SDK or Web API code.

Being a long time user of SQL Server and T-SQL, my instinct was to find a way to regularly copy CRM Online's metadata to SQL tables. To follow is an overview of what I have working so far.

My initial set of requirements are these:

• Make it quick and easy to run robust queries (joins, filters, sorting) against CRM Online metadata.
• Allow for enhancing CRM's metadata, such as listing all picklist items next to each picklist (OptionSet) field and showing whether a field is on any forms and which ones.
• Allow for diffs on the metadata, to be able to determine, for example, what new entities and fields were created in the past week.
• Provide a fast on-demand way to export CRM Online metadata to an Excel file.
• Allow for the service to run for multiple CRM Online organizations.

The approach I've taken (so far) is this:

• An Azure WebJob is responsible for querying CRM Online metadata (entities, fields, relationships, roles, global optionsets, views, forms and form structure, etc.) and copying this data to a set of SQL Server tables. Entity Framework, along with some CRM SDK code, makes this relatively easy to do. (The benefit of using a WebJob is that it can be scheduled or triggered on-demand.)
• A SQL Azure database stores the latest metadata and maintains copies of the previously extracted metadata. Stored procedures write differences to a table for easy reporting.
• Another WebJob (I might move it to REST service) allows for extraction of the metadata into various forms, such as Excel and Word templates. This allows for fast documentation of a CRM Online's org metadata.

This initial design has met all of my requirements except for handling multiple CRM orgs. That will take a bit more work and will probably deplete my Azure credits each month so I'm leaving it as a "nice to have".

I'll hopefully be able to put this out as open source but until then I hope you can glean some ideas from this information. And... hopefully Microsoft will provide the same types of functionality soon so that I don't need to maintain this solution. Although I have to admit, it was fun to build.

Using Microsoft Azure with Dynamics CRM

I've always been uncomfortable with the word "can't", especially when it comes to software. My manager at Onyx Software around 2002, John Hawk, once told his team that it's "just a matter of moving data from here to there" and that has always stuck with me. Sure, it can be difficult to move lots of data quickly, transform it for different systems, analyze it for meaning, present it clearly, etc. but there's usually a way to do all of those things. "Can't" should not be allowed in the room.

Every experienced Dynamics CRM user/admin/dev should spend some time each week answering questions on the Dynamics CRM forums. I try to answer a few questions each week. I especially like the questions or responses that use that word -- can't. My immediate response it, "oh yeah, let's see about that".

One cure for the Dynamics CRM can't's is Microsoft Azure. Azure means "world of possibilities"... in some language, I'm sure of it. These cloud services open up a myriad of possibilities for any company or organization to further automate the business, provide better customer service, make better decisions and lower costs.

So far, as I write this, I've listed 43 ways that those responsible for Dynamics CRM in their company can improve CRM with Azure. I came up with a lot of them in response to that word in the CRM forums. "I can't schedule a job to run against CRM data." Yes, you can, with a scheduled WebJob or other scheduled service. "Without writing code, I can't create an Excel file, populate it from CRM data and store it on SharePoint." Yes, you can, with an Azure Logic App or Microsoft Flow.

There are already several ways to link CRM and Azure (Service Endpoints, WebHooks, REST, etc.) and this will continue to expand. There will come a day when CRM administrators will be seamlessly using Azure services without seeing that it's "Azure". Perhaps CRM's workflows can start an "extended workflow" (really an Azure Flow workflow) or a CRM form can interact with a Node.js app (running as an Azure Function). Whatever Microsoft comes up with, the can't's won't stand a chance.

Azure Logic App to send email if other cloud app stops running

I created an Azure Logic App today that runs once per day and does the following:

1. Call a stored procedure in an Azure SQL database. The stored procedure returns the number of hours since an application last ran to completion.
2. If the return code from the stored procedure is greater than 6, send an email (using Office 365) to me.

If I were to create an app for this (e.g., a WebJob), I'd have to add in the SQL connectivity plumbing (or use Entity Framework) and add code to send an email. That's still an easy application to write and deploy in Azure, but being able to put together a small app (workflow) like this in just a few minutes is pretty great.

As of this post, Logic Apps is still in Preview, so it has lots of issues that the team will work out. One problem I ran into was trying to use the SQL connector's OutputParameters return object in a Logic Apps Condition step. I wanted to know if the OutputParameters value was greater than 6. When running the app, I ran into the following error:

{"code":"InvalidTemplate","message":"Unable to process template language expressions for action 'Send_Email' at line '1' and column '11': 'The template language function 'greater' expects exactly two parameter of matching types. The function was invoked with values of type 'Object' and 'Integer' that do not match.'."}

I then went into the "Code View" and wrapped the condition expression with an int() function call, but then the Logic App threw this error:

{"code":"InvalidTemplate","message":"Unable to process template language expressions for action 'Send_Email' at line '1' and column '11': 'The template language function 'int' was invoked with an invalid parameter. The value cannot be converted to the target type.'."}

Finally, I tried using a ReturnCode value from the stored procedure (as shown in the screenshot) and that worked; I was able to compare the integer return value to a specified integer value.

Logic Apps still has a ways to go to be ready for prime time but in this case it worked out well for what I was trying to achieve. And it's one less Visual Studio project to maintain!

Link Buddy for Dynamics CRM

On one of my Dynamics CRM (on-premises) projects, the company wanted CRM to calculate pricing for insurance-related services. We implemented the functionality in a plug-in and added tons of logging (log4net) statements to help us analyze CRM data during testing and troubleshooting. The pricing path went through dozens of steps, so being able to see the logic path taken was essential.

The log files contain mostly CRM record GUIDs -- the thought was that we could use those GUIDs to manually navigate to CRM records when needed. That turned out to be a pain.

So I worked late one night a created a Windows app that translates one or more CRM entity record GUIDs into CRM "addressable" form hyperlinks. Now, we (including end-users) can copy the log contents to the app, click a button and get hyperlinks that lead to the actual CRM records.

I named the app "Link Buddy for Dynamics CRM". It's up on CodePlex (source code and executable) if you'd like to put it to use. Please let me know if you run into any issues with it, have suggestions, etc.

-Tim