Wednesday, February 14, 2018

OData Queries in Azure Logic Apps

I am building some Azure Logic Apps and came across a problem where I wanted to get a List of records from Dynamics 365 Customer Engagement (CRM) and update some data in SharePoint. In order to get the records I wanted from CRM, I needed to use an OData filter expression. I was not sure what it should look like so I turned to the XRMToolbox FetchXML Builder which generates OData expressions like this$top=50&$filter=ObjectId/Id eq (guid'9a19c4eb-bc3b-45e1-adc8-c5cf1716101b')

I could see the filter used an unusual notation of “ObjectId/Id eq (guid'9a19c4eb-bc3b-45e1-adc8-c5cf1716101b')” and if I executed that REST call then I could get a response from CRM. But when I use the filter expression in the Logic App, I would get this error message.

“Could not find a property named ‘ObjectID’…”


So I created a stand-alone Logic App that had a single List Records from CRM (oops, Dynamics CE) to see what it returns:


I noted that GUID field names were converted to using an underscore in front of the field name, and “_value” after the field name, and that the field name was all lower case. This told me that under the covers, OData is dealing with EntityReference fields by converting the GUID to a string value. So I changed the Filter to use the adjusted field name together with “eq” and the unique identifier from a prior query like this:


which now returns exactly the list I needed.

Wednesday, October 4, 2017

Find other accounts near an address

I have a customer in NYC that we are helping to implement Field Service using Dynamics 365 (online). One of their hot issues is making it easy for them to find all other accounts near a given location, so I started thinking about the problem.

One of the features built into that solution is geocoding, so that all account addresses get a geocode. If I could use SQL queries, I would be able to use a native SQL query to find all addresses near a given point. As far as I know, the FetchXML syntax does not do those kinds of queries.

To narrow down the problem space a bit, my client only works in the NYC area which makes things a bit easier because we can simplify the variables: We don't need to worry about the curvature of the Earth over long distances, or the distance between longitudes for a given latitude. I am going to simplify this a bit more. If I ignore the fact that a radius around a point is the best representation of what is nearby, and I use a rectangle which is good enough for my clients purposes, then the query is super simple because now I just need compare the current Lat/Long with ones that are between "+" or "-" a difference in order to define the rectangle.

Lets say, from a given Lat/Long, we need to know who is inside a vicinity of  1 square mile. That means we are interested in all locations that are roughly 1/2 mile North, 1/2 mile South, 1/2 mile East and 1/2 Mile West from the current point.

Each degree of latitude is 69.99 miles apart.
At 40 degrees latitude (NYC), the longitudes are 53.08 miles apart.

Therefore, 1 mile is about (keep it simple):
1/69=0.0145 degrees of latitude
1/53=0.0189 degrees of longitude

So if we search for all addresses that are within one square mile, then we are looking for a Lat/Long that fall within the range of the
current location +/- half the 1 mile area.

So if we are at this location: 40.61938 Lat, -73.91799 Long, then we are looking for all addresses where the latitude is:

40.61938-(.0145/2)=40.61213 and 40.61938+(.0145/2)=40.62663 (up to 1/2 mile South and 1/2 mile North of us)

And the longitude is:
-73.91799-(.0189/2)=-73.90854 and -73.91799+(.0189/2)=-73.92744 (up to 1/2 mile East and 1/2 mil e West of us)

and in Advanced Find, would look like this:

Remember, because NYC is in the western hemisphere, the longitude is negative.

Ultimately my goal is to take this kind of query, build it into a JavaScript on a form and populate a map with the points. But that is a blog for another day.

Friday, September 29, 2017

Why use Azure to extend Dynamics 365?

What are some good use cases for building logic for your Dynamics 365 using Azure?

1. Inconsistent workload demands
Have you started using the Dynamics 365 portal? It is pretty cool, but if you have waves of people hitting the web site and making updates, and you have numerous plugins and workflows running, you may find that your system can be sluggish if not outright slow. By moving some of your plugins and workflows to an Azure service, you can mitigate some of the effects of the portal. Consider using Azure Functions.

2. Offloading batch processes
Are you an association that needs to update 100k membership records at the same time, or generate 20k invoices on the last day of the month? You shouldn't even try to do that with workflows or your users coming after you with torches and pitchforks. Just like #1 above, you could have an on-demand service running in Azure to do the dirty work without seriously impacting your users. Consider using a Cloud Service that you can deploy, run, then shut down.

3. Scheduled jobs are not easily managed within D365
You want a system update to run at 5PM every Friday? I dont think you can do it in D365 without writing some plugin code and a workflow, but Azure has an OOTB solution for that. Take a look at Azure Logic Apps. You can schedule them to run at a specific time.

4. You are moving to D365 from an on-premises environment that uses SQL Stored Procedures
You have some SQL queries and views you use to support reporting systems. CRM has a feature to replicate an entire entity to an Azure SQL database. You want to push updates back? That is a bit more tricky, but perhaps you can manage it with Azure Logic Apps or Azure Functions.

5. Plugins and workflows in D365 have a timeout limit of 2 minutes
Yep, trying to update more than a few records in a single plugin is detrimental to your employment. In one case, I found that a client had created a bunch of small plugins, but what the developer(s) didn't realize is that they had created a chain of plugins that was over 1500 lines of code and periodically failed from time to time, depending on the current server load. The worst part was that there was no way to re-trigger the plugins because they were designed to work only with a Create event message, and the code did not support idempotent transactions, so you could only run it once and hope it worked the first time. Consider using an Azure Cloud Service, Web Job, Service Fabric, or Function, depending on what you are trying to accomplish.

6. Consolidate Business Logic 
Perhaps you have several places in your system that do the same thing, like processing a credit card. If you put the logic in a plugin, it is not easily accessible to your web site (yes you can do it, but it is not a good user experience). You could use a WebJob that works with both CRM and your web site.

Friday, September 8, 2017

Dynamics 365 (CRM) Unknown Report Version: 9.0 problem

Just a quick post on a nasty problem that caused me some grief, and my solution to fix it.
I am using Visual Studio 2017 with the recently released Dynamics 365 Report Authoring Extension. I wanted to customize the Dynamics 365 (aka CRM Online) Invoice report, so I went to the reports and downloaded the Invoice and Invoice Sub-Report RDL files. I knew that these reports would have SQL queries so I quickly set up a VS 2017 BIDS report project, and put the RDL files in there. I spent some time converting the SQL to FetchXML, and when I tried to save it, got the error message "Unknown Report Version: 9.0" and could not save the project.

I read several blogs that suggested manually tweaking the RDL source code and tried a few of those but it just caused more problems. I read that someone had a similar problem with RDLC files and they used VS 2015 to open and then save the original files before opening them in VS 2017, so I tried going back to one of my old VS 2012 environments where I had BIDS installed, opened and saved the RDL files in there, and them moved them to my 2017 development environment, and I now I can make my changes and save the work.

Please note, if this is your first time creating D365 reports with VS  2017, be aware that the RDL file you start with is not what you will publish to CRM. You must build your report project, then navigate to the Bin folder under your project and get the RDL files from there to publish in D365. The reason is that the report Project allows you to target different versions of SQL server.

Monday, April 17, 2017

View of views

Are you trying to find all the views that relate to the Account entity? Check this out! You can use Advanced Find to search for views. Here is how:

Open Advanced Find and choose “Views”

In the search criteria, choose FetchXML as the field you want to search and give it the schema name of the entity you are looking for:


This will result in all the system views that involve the Account entity. The FetchXML field holds the names of all the entities that the view touches. How do you find the schema name for an entity? Open your default solution and look at the entity “name” field.


I was initially looking for ways to document an existing system and then started thinking about ways I could generate some governance over the views in the system, but that will be a future blog post. Sadly, this will not help you find personal views, and unfortunately, you cannot export the results from the Advanced Find directly into Excel – I had to use the report builder to export the views into a format I could include into my documentation. But it can be helpful if you are about to perform major surgery on an undocumented system.

Thursday, August 25, 2016

Insufficient Permissions

When users get the Insufficient Permissions dialog, it means that security will not permit them to do the task they wanted. Sometimes this is desirable as you don't want to give users permission
The key to deciphering a permission issue in Dynamics CRM is in the log file. Click the Download Log File button. This will be an XML file containing a bunch of stuff that is difficult to comprehend, but using these steps to find just what you need will help make this easier.

Step 1: look for the Message tag. In my example, I see the following: 

SecLib::AccessCheckEx failed. Returned hr = -2147187962, ObjectID: 16476d24-ca68-e611-80cd-0050568077fb, OwnerId: 153d564f-1264-e611-80cc-0050568077fb, OwnerIdType: 8 and CallingUser: 400b5cdc-0d64-e611-80cc-0050568077fb. ObjectTypeCode: 3, objectBusinessUnitId: 8439abf5-4a07-e611-80c0-0050568077fb, AccessRights: AppendAccess 

Step 2: The parts you need to look at are the ObjectTypeCode, and the AccessRights. You can identify the entity by checking the ObjectTypeCode against this reference: so in this case, the entity is the Opportunity.

 Step 3: Check the users rights for the Opportunity entity. You do this by opening the users record (under settings/users) and then click the Manage Roles in the command bar (sometimes it is under the "..." more commands icon). Make a note of all the Security Roles assigned to the user. Note: Some users can have more than one role.

Step 4: View the assigned Security Roles (under the Security menu in the main navigation).
In this case, the user only has rights to Append records to her own opportunities. 

Step 5: It appears in this case that the user must not have had ownership of the Opportunity record and therefore could not Append any information to it, such as a note, activity, product, or a connection. To resolve the issue, you can assign the ownership of the record to the user (if it is just one time), or give her full rights to append to opportunities to records owned by others. In some cases, you might want to build more complex workflows that assign ownership of an opportunity depending on what stage the sales process is in order to control who can make changes during the sales process.

Saturday, May 7, 2016

Using FetchXML in a CRM form without having to use string concatenation

I am a solution architect using Microsoft Dynamics CRM, and I must be the laziest programmer I know. I HATE to write FetchXML code embedded in Javascript. But I am getting over it. I started using FetchXML Builder add-on to XRM Toolbox and that was very helpful for working with CRM Online. But last week I needed to query the Product table in a custom form to pull back a list price of a product, and I was dreading the thought of having to add all the little quotes and doing string concatenation because I knew I would be tweeking it and repeatedly making changes in FetchXML builder, them moving the query code back to Javascript and making it a bunch of concatenated strings (wash, rinse, repeat).

If I were putting the FetchXML into a C# program, no problem because that language supports having multi-line strings. And I could use Format.string() to insert my parameters into the code so I don't have to concatenate that either.

Not so much in Javascript. So I did some research and found some articles on how it could be done. I can put the FetchXML between comments and then use a regular expression to make it into a legit string. As long as nobody turns on 'compression' on the web server, it should continue to work. The code looks like this:

The last function in this takes care of replacing the {0} in my FetchXML with the GUID I need in the query. Line 15 of my code gets the GUID from a lookup field, and Line 16 calls the Format function to replace the {0} parameter with the GUID.