Converting Dynamics’s Geolocation To SQL Geolocation Using Microsoft Flow And Azure Function

Background

One of the awesome features of the Azure Search service is the ability to search information based on location. Azure Search processes, filters, and displays geographic locations. It enables users to explore data based on the proximity of a search result to a physical location. This feature is powered by SQL Server Geolocation data type. Since SQL Server 2008, developers are able store geospatial data in SQL server using Geolocation fields. Geolocation fields allow querying data with location based queries. To facilitate the Azure Search service to search within CRM accounts and contact, I had to pushed my account and contact searchable information to SQL server hosted in Azure. To copy information from Dynamics to Azure SQL server, I used Microsoft flow. Everything worked good except, copying CRM longitude and Latitude to SQL Server.

The problem

The problem with copying longitude and latitude to SQL server Geolocation field is the compatibility. When you try to insert longitude and latitude fields to Geolocation you encounter casting error.

The solution

  1. The solution I used to tackle this problem is making use of Azure Function and converting Longitude and Latitude to Geolocation type in the Azure function and return the response before the Insert action in the flow. See the below steps:
  2. Step 1 is self-explanatory.
  3. The step “CC Contact” extracts the Contact name (or any lookup name property) from a lookup.
  4. The “Http” step, calls the Azure Function to converts the CRM longitude and Latitude to SQL Geolocation field
  5. The “Insert Row” step, inserts our data to SQL server row.
Microsoft Flow
Microsoft Flow

The Azure Function

The Azure function is a very simple function. You will need to import Microsoft.SqlServer.Types Nuget package and use the below code:
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();  
       Coordinates data = JsonConvert.DeserializeObject<Coordinates>(requestBody);  
       SqlGeography point = data.GetGeography();  
       return ( ActionResult ) new OkObjectResult ( $"{point}" );  
 public class Coordinates  
   {  
     public double Longitude { get; set; }  
     public double Latitude { get; set; }  
     public SqlGeography GetGeography ( )  
     {        
       try  
       {  
         return SqlGeography. Point ( Latitude , Longitude , 4326 );  
       }  
       catch ( Exception ex )  
       {  
 // Log ex and handle exception  
         throw ex;  
       }  
     }  
   } 

 

 

Implementing Enterprise Search In Power Platform

Photo by Anthony Martino on Unsplash
Photo by Anthony Martino on Unsplash
Providing good search capabilities is a key feature in modern business applications to support usability and end user satisfaction. We have seen how the search capabilities of the Dynamics platform has evolved from providing “Quick Search” and “Advanced File” to “Relevance Search”. The goal of the platform search features has been to support users to find the relevant information they need in the quickest and easiest form. These search features are out-of-the-box and easy to enable/configure/use. As the platform progresses to offer richer features to users and enable them to search better, the demand for richer and better search techniques grow, and we see instances where the platform capabilities cannot meet user demands with its out-of-the-box capabilities. Before going further about advanced search scenarios, you can read about the platform out-of-the-box search capabilities in this official documentation. In this article I share why we may decide to implement a search solution of our Dynamics solution using Azure Search Service.
In enterprise implementations, business applications are not the only systems used in the organization. We often see call center agents and sales representatives need to obtain their required information from various systems to service customers. Searching users in every system is a cumbersome job which may cause setbacks in end-user adaption. Integrating Dynamics with Azure search offers consolidation of search operations in one specialized search service with ability to connecting to various data sources and apply modern search techniques to find the most relevant data. A practical example of this scenario can be seen in one my recent experiences where the organization users had to search for user information in CRM, SharePoint, Sybase and a pool of CSV files.

Customized Search experience

To facilitate more user adoption, using customized search techniques are highly favorable. In all modern search engines, we see use of “Auto complete”, “Suggestions” and “highlighting” features which can be added to the Dynamics solutions search experience. Displaying search results by support of “Document Preview”, “Document Opening in a customized containers”, “Facets”, “Filter” and “Sorting” are examples that enhance your Dynamics solution’s capabilities.

Customized Search Behavior

The true power of search is demonstrated with different pieces of information are linked together to make sense of a bigger picture. Extracting words and sentences from documents including images and pdf files, extracting key phrases, people names, location names, languages and other custom entities with the help of AI is another unique feature that you can add to your Dynamics’s search capabilities. Another amazing search capability you can have in your Dynamics implementation is the ability to search based on geolocation information, i.e. you can search for all your partner network from CRM or get the location of your field service force. The beauty of implementing your own enterprise search lies in the fact that you can search information in your data stores and link them using AI to generate knowledge and better insight to your data.

Customized Search Result

Another need for customized search in your Dynamics solution to the ability to refine your search result profile. When you use AI in your search, the system gives you the power to see how relevant search results are to your search keywords. And by knowing this you can refine your search profiles to generate a different result for the same keywords. This way you train the AI engine to work better for you and enable users to get more accurate search results.
Architecture

Dynamics integration with Azure Search service can be integrated in the following pattern:

 

  1. Integration through web resources: These web resources will host a web application acting as a client to the search service. The web resource can be a HTML file or an iFrame hosted on forms. The important point in this approach to ensure cross-origin settings in the client application and writing your html in a secure way and according to the best practices.
  2. Integration through custom power platform controls. You may build your own custom control which sends REST requests to the Azure Search and display results by consumes REST responses. The custom control can call Azure Search services using Actions or direct REST calls to Azure Service.
  3. Azure Search works based on indexes and your first step is to push your CRM searchable data to Azure Search indexes. This can be done using Microsoft Flow, Microsoft App Logics, custom solutions or Azure Data Factory. I have used all these tools in my implementations, and you can opt to any of these tools based on your requirements.
  4. Once the data is in your data store, you can create your indexes in the Azure Search. You can go for separate indexes for each data source or combine multiple data sources in one index. Each approach has its own requirements which will need to be met either in your client web application or a separate azure compute resource. Once indexing is done, you can make use Azure Search Rest API directly or using Azure API management to expose your search service to your Dynamics solution.
Summing these all up, you see as business application products get more sophisticated and organizations move from data to big data, engineers now must look for innovative approaches to implement Dynamics Solutions. Microsoft Azure along with Dynamics platform offers necessary tools to solution architects to design such solutions.

The easy way to compare environments

Have you ever had to compare two CDS/D365 environments and see what the differences were? Maybe you want to do a data migration into an existing environment to combine environments, and you need to know what the field and metadata differences are between the two environments.

Traditionally I would export the xml and compare in Notepad+ or some other tedious method, but I discovered (thanks to Tanguy Touzard’s recommendation) that there is a tool in the XrmToolBox plugin store that makes the job much easier.

Introducing the System Customization Comparer.

This awesome tool was created by Lars Muller, and it lets you select a source and target environment

Then it will compare the metadata, showing you the differences in entities, fields, and views.

Thank you Lars for making a frustrating task less frustrating.

An easier way to export data from your D365 environments

Exporting/Importing data can sometimes be a tedious and time-consuming task but thanks to this feature in PowerApps, exporting data from multiple entities is as easy as ever. Huge thanks to our PowerApps SME from Barhead, Mary Rose Bagtas, for helping out. 🙂

 

To quickly export data, go to https://web.powerapps.com

Click on Data -> Entities

 

Then click on Export Data

 

Select the entities you want to export, then click on Export Data again.

 

Download the exported data and you’re good to go. 🙂

 

Connect Dynamics 365 Finance & Operations to CDS – new CDS environment, integration, new entities & populations – Article Two

This article is part of a series of articles that is going to run you through setting up the following:

  • creating a new CDS database,
  • creating custom entities within CDS
  • creating an integration from F&O to CDS
  • Creating a new view
  • Exposing this new data within a CanvasApp

Article two of four – integrating Finance & Operations to CDS and creating a new view. Find part one here(!)

Now that we have our entity focused around projects we need to integrate it with Dynamics 365 for Finance & Operations to fill it with some sweet, sweet data! Let’s head over to www.admin.powerapps.com and go to “Data Integration” from within the navigation pane:

From here go to the “Connection Sets” tab and then “New connection Set”:

Give the connection set the name Project, then select your CDS environment and F&O environment – now a key part is stating the organisations you wish to connect. Provide the Legal Entity ID of the Finance & Operations company you wish to connect to and then the ‘org’ name of the CDS environment – found by going to www.admin.powerapps.com then looking at the bracketed (org######) string – this is the  organisation ID

Then go to the “Projects” tab and hit “New Project”:

Provide your project with a name for example “F&OtoCDS” and then select the template “Fin and Ops to CDS” then hit ‘Next’:

Select the connection set you create earlier named “Project” and hit “Next”, then select the “Organizations” grouping you created along side the connection set earlier and hit “Next”. Then agree to the ‘Privacy notice and consent’ without reading it because, well, life is too short to be boring and not take risks – by hitting “Create”!!

You’ll then be greeted by your new Project! But currently it is an empty carcass that is achieving nothing – so hit “Add Task”:

Now, name your tasks “Project”, select the “Projects” data entity from Dynamics 365 for Finance and Operations, select the “Projects” entity you created within the Common Data Service then select the “Organizations” created earlier. Then hit that big GLORIOUS button “Create”.

This will then create the new ‘Projects’ tasks, select the task which will then drill-through to allow us to start mapping between the two entities, map the following and hit “Save”:

Now for the exciting part, let’s execute/”run” this integration and see the data flood in! Hit “Run”! This will now kick-off the integration and take you to the “Execution history” screen

Then you’ll be greeted by the greatest sight of all a big green tick under the ‘Submitted’ column meaning it was successful:

Now let’s go check out that data – head back to www.web.powerapps.com and go to the custom “Projects” entity you created, now before we can see the data using the “Data” tab we need to create a new view, so go to the “View”  tab within the “Projects” entity and hit “Add View”:

Give your view a name and a description (if you’d like too) and hit “Create”:

Now select the fields from the left you want to appear on the view (simply by click on the ones you want will automatically transport the to the view) then hit “Save” then “Publish”:

Then go back to your entity (previous tab within your browser) and hit “Done” which will refresh and bring in your newly published “Main” view.

Now let’s go and check out the results, go to the “Data” tab and switch to the newly created view:

And now you can see all your awesome data (annoyingly i forgot i made some changes to my DE so the name hasn’t come in correctly but yours will be fine):

This concludes part two!

 

Connect Dynamics 365 Finance & Operations to CDS – new CDS environment, integration, new entities & populations – Article One

This article is part of a series of articles that is going to run you through setting up the following:

  • creating a new CDS database,
  • creating custom entities within CDS
  • creating an integration from F&O to CDS
  • Creating a new view
  • Exposing this new data within a CanvasApp

Article one of four – creating a new CDS database and new entity (this is not a deep-dive more of a beginners run through, deeper dives will be created soon(!))

1] Creating a new CDS database:

Go to www.admin.powerapps.com/environments and  select “New Environments” located in the top-right hand corner of the screen, then provide a name, region and environment type as shown below:

Then hit create, a new dialog box will pop-up asking if you wish to create a new database – of course the answer is yes:

The next dialog box will ask for more settings related to the CDS environment, select the currency and language you want it to be deployed in and whether you wish for sample apps and data to be included in your environment then hit “Create database”.

Your new environment will be provisioned, give it some time but it will happen.

Now head over to www.web.powerapps.com so the real work can begin!

First we need to select the newly provisioned environment from the environment drop-down list located at the top of the screen:

Then from the navigation pane on the left hand-side go to “Entities” then ‘New Entity’:

A new dialog box will pop-up – provide this with a relevant display name, it will automate a plural display name but this can be changed if necessary, then a name for the actual CDS entity which cannot be changed once created.

Once all filled out hit “Next” this will spin up the new entity.

It will automatically place you in the “Fields” tab – hit”add field” and we are now going to create a series of these new field starting with “Project ID”. Provide the new field with a display name, name (system name that cannot be changed) and the data type of “Text”:

then hit “done” – now rinse and repeat this AMAZINGGGGGGGGGG process for all the fields show below (Contract, CustAccount, Customer Name, ProjectName):

Once you have completed all that hit “Save entity” and…

VOILA your first entity is created and thus completes the first article in this series! Click here for part two (which runs through integrating F&O to the CDS)!