In a previous post we explained how the out-of-box automatic or manual geocoding for Field Service works. Unfortunately this is not available for one of the (potentially) key tables of this module. Namely the Organizational Unit, which can also be selected as the start and end point for technicians.
In this post, we explain how to automate the calculation of longitude and latitude for this table. Of course, it will be easy to reuse for other tables if you want.
On the Organizational Unit you will find 2 fields. One to enter longitude and another to enter latitude directly. Strangely enough, there is no possibility to calculate them for us. Just like the nifty automatic or manual geocoding Field Service provides for other tables.
This means that you will have to manually look up the coordinates by surfing to Bing (or Google) Maps and look up the address to determine the geocodes yourself.
Not if we can help it!
To avoid looking up every organizational unit’s address manually, we found a solution to calculate it for us. We make use of a Cloud Flow in Power Automate that triggers on the create or update of an Organizational Unit. When searching the web, we found several other solutions using Workflows to call an out-of-box action, but this action wasn’t available to us anymore inside of the dropdown in the workflow screen.
But, I was wondering, maybe this action is still available for us within Power Automate?
So I set up a new Cloud Flow with the Microsoft Dataverse “When a row is added, modified or deleted” trigger and added the “Perform an unbound action” action.
And would you look at that. The action msdyn_GeocodeAddress we were looking for!
First things first
As you can see, this OOB action expects a couple of address fields to calculate the geocodes. As the Organizational Unit does not contain any address fields of its own, we need to create them. So for this I’ve created 4 custom plain text columns on the Organizational Unit table and added them to the main form. I won’t go into detail about creating custom columns through the PowerApps platform. Instead, you can find more information about that in the PowerApps docs.
Setting it up
Next, setting up the Cloud Flow. For this I set the trigger’s Change Type to “Create or Update” and limited it’s columns to trigger on. This way it will only trigger when our new custom fields are present in the created, or have been modified in the updated, Organizational Unit target.
Then we pass the Organizational Unit and the values of our newly added columns to the msdyn_GeocodeAddress action. In our case the column State Or Province wasn’t relevant for use, but it is required for the action to work. Therefor we just use the asterisk * as a wildcard (and it works like a charm!). Of course, if it is necessary for your use case, all you have to do is add another column to the Organizational Unit table that holds the state or province to look up.
The action only has 2 outputs. Namely the calculated longitude and latitude. So it’s fairly easy to know where to drop these values inside of the “Update a row” action:
And that’s all there is to it! Let’s save the Cloud Flow and put it to a little test.
When creating a new Organizational Unit and filling up the address fields, we see that the Cloud Flow did its work and calculated and completed longitude and latitude as expected!
When we update one of the address fields, our Cloud Flow will trigger again and recalculate the long- and latitude, also as expected.
Wrapping it up
So there you have it! A full working, automated flow to calculate the geocodes on “forgotten” tables within Dynamics 365.
If you need any help regarding Dynamics 365 customizations and/or configuration, feel free to contact us!