Integrating external systems into the Microsoft Dynamics 365 DataVerse

In my most recent Dynamics CRM project, the client had a requirement to collect data from an external source and record it within the DataVerse.

In this particular instance, they wanted to connect to the government database to collect Energy Performance Certificate (EPC’s) to see efficiency ratings of particular properties.

When we were first given this requirement, my first thought was Virtual Table (or Virtual Entity as it used to be known). Using this, we would be able to connect DataVerse to the external EPC database... simple, right?… well so I thought.

The first, and major flaw was although the EPC had an (authenticated) API that we could connect to, the records that were coming back didn’t have a GUID which is a requirement of a virtual table.

So that was out of the window.

Next, we looked at using a HTTP request in Power Automate to pass a request (in this instance a house number and postcode) in the hope that it would return this EPC register for that specific property which then meant we could update the relevant record in DataVerse.

Unfortunately, this only half worked…

The problem was that due to the way the data was structured in the external EPC database when we requested the information (from the fields Address 1: Street 1 and Postcode/Zipcode) from the contact record it returned every EPC register matching that postcode. As you can imagine, this could potentially be multiple records, so when we ran the DataVerse update step in Power Automate it updated the contact record multiple times….not ideal.

So after a bit of thought, I realised we needed something similar to the ‘postcode’ lookup as this functionality gave you the ability to pick a single address from a postcode. Then it dawned on me that trying to do this within the Model Driven app was probably going to involve a custom (java or . Net) plug… this is not my area of expertise.

Then the idea came that we could use Canvas apps to create this functionality. Can we embed this Canvas app in a Model Driven app form? And could we get pass values from the Model Driven app to the Canvas app and back again?… the answer was 'yes'.

Building a simple Canvas app, we called our ‘HTTP request’ flow from a button and passed the postcode from the Canvas app to the EPC register database.

We then stored the results in a Canvas app collection and displayed the results as a gallery so the users could choose the correct property.

Then using a patch command, we passed the selected information back to CRM to create an EPC Register record within the DataVerse. This gave the end-user the ability to easily query and create EPC records from the external data source, right into the DataVerse.

The final piece of the puzzle was to embed the Canvas app within the contact form in the Model Driven app.

No more manual copying and pasting!

This is just one example of utilising the Power Platform to integrate external data within the DataVerse. I can only imagine lots of companies perform these manually tasks multiple times a day, every day just so they have all the necessary information in one place.

So, let me ask you this question. Are you or your team members repeating repetitive, manual and time-consuming tasks every single day? If so, would a similar type of integration benefit your business process? Speak to your Account Manager today to learn more.