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.