Bad data is fake news

The term "fake news" is being tossed around a lot lately. It points to the general opinion that it's becoming increasingly difficult to trust what we read online or even in our local newspapers.

Reading "alternative facts" from seemingly honest news sources is disturbing, but what's equally damaging is reading a news article that omits important details.

Likewise, in software business systems, such as Dynamics 365 and Salesforce, fake news is the result if those using the system aren't careful to consistently provide accurate and complete information.

Examples of bad data include duplicate records, inconsistent field entries (e.g., US, USA and United States in the country field), data in the wrong place (e.g., mixing customers with leads), fat-fingered values that go unverified and data entered in the wrong place.

In addition to bad data, another common problem is missing data -- data that is omitted for a variety of reasons: it's too difficult to enter, takes too much time, not sure where the data goes, difficult to import, getting an error message, forgot to update it, etc.

Just like with fake news, consumers of the data will start to question the content of the records and reports they view. This will eventually erode confidence in the system and people will find alternative ways to do their jobs, which usually means creating another Excel file and storing the data on their local machine.

To prevent bad data from making its way into your Dynamics 365 (CRM) system and to keep it clean over time, Microsoft provides features in the base product that can help. Those features include duplicate detection (real-time and scheduled), form-based business rules and scripting for data validation, processes and plug-ins to validate, correct and complete data, and bulk import and deletion tools.

Just as the New York Times and the Wall Street Journal are careful about publishing accurate news stories to at least maintain their subscriber base (and avoid the "fake news" label), it's also essential for CIO's, system administrators and end-users to treat their business systems in the same way. Otherwise, once trust is lost, it's difficult to get back.

Azure Logic App to Report on Neglected Dynamics 365 (CRM) Cases

Scenario: You want to send an email to the owner of an unresolved CRM case record if the modifiedon date/time is greater than 24 hours.

Solution: Azure Logic App and Azure Function

Note: The Logic App references an Azure Function named GetHoursSinceUtcDateTime. You can find the function code and instructions here: https://gist.github.com/tdutch1/a14c60eed1a005c074f317b581cd6f01

Logic App

• For the "Send an email" action, you can include fields from the CRM case. Consider providing a link to the case record in CRM.
• In the "List records" action, set the query condition to "statuscode eq 0" to include only active cases.

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.