Importing External Data Into the Common Data Service
One of the new features of the Common Data Service is to use Power Query to create custom entities that are linked back to external data. This data could reside in a number of different places, including OData queries.
The good thing is that Dynamics 365 for Finance & Operations allows us to surface all of the data entities through the OData connection which means that we can use this to incorporate direct connections from the Finance & Operations into the Common Data Service entities and then those entities can be used by tools like PowerApps to create new forms and integrations.
- Creating Custom Entities from OData Connections
- Creating a PowerApp using the Custom Entity
Creating Custom Entities from OData Connections
The first example that we will show is how you can use an OData feed to create a new Entity within the Common Data Service.
How to do it…
Start off by opening PowerApps and click on the + New entity link within the menu bar.
This will open up the Choose Data Service gallery where we will be able to see all of the different Data Services that we can choose to import the data through.
For this example, we will want to get the data from Dynamics 365 Finance & Operations, and we can access that data easily through an OData feed. So we will want to click on the OData tile.
This will open up the OData Connection setup form where we will be able to start defining our OData connection.
Start off by pasting in your base URL for your Dynamics 365 Finance & Operation instance and then add /data to the end of the URL.
That will then allow you to select the data gateway… although I am not entirely certain that you need this.
And then we will want to select the Authentication Kind that will be used to connect to Finance & Operations.
For this example, we will want to use our tenant credentials and select the Organizational account option.
Now we will want to authenticate so we will click on the Sign in button.
This will open up an account sign-in form where we can log in with our tenant credentials.
Once we have authenticated our account, then we can click on the Next button to continue on.
This will take us to the Choose Data form, and Power Query will start reading all of the entities that are available through the OData feed.
After a little bit, we will see all of the 1800+ entities that are available within Dynamics 365 for Finance & Operations.
We can filter out the entities to easily find the entity that we are looking for. So if we type in sellable into the filter the entities so that we can see the SellableReleasedProducts entity that will allow us to retrieve a list of all of the validated products.
All we need to do here is select the SellableReleasedProducts entity and then click on the Next button.
This will take us to the Power Query form where we will be able to see a sample set of data from the OData query.
Before we continue on, we will make a quick change and change the Name to Sellable Products to make it easier for the user to read and then click on the Next button.
That will take us to the Entity Mapping where we will make some finishing touches to the query.
We will start off by selecting the Load as new entity to tell the query to create a new Entity that used the query as a basis.
Before we continue on, we will need to specify what the index or primary field is for the entity. To do that we just click on the dropdown list for the Primary Name Field and select the index field.
In this case, we will select the ProductNumber field.
Once we have done that we can continue on and click on the Next button.
This will start the load of the data, and the creation of the data entity within the Common Data Service. When it says Completed we can click on the Done button.
That will return us to the Data integration page, and we will see that a new project has been created for us.
Creating a PowerApp using the Custom Entity
Now that we have created our custom entity within the Common Data Service we can use it within PowerApps by attaching to it as a data source.
How to do it…
To create a PowerApp, we will want to start off by selecting the type of PowerApp that we will want to create. To do this, we just click on the Choose a design mode link at the bottom of the form, and we will see that we can either create a Canvas or a Model-driven PowerApp.
For this example, we will click on the Canvas option.
This will open up the Canvas gallery. We will want to create the app from scratch, so click on the Start from blank tile.
This will open up the options for the PowerApp, and we can either create a tablet or a phone app.
We want to create a phone browser, so we will select the Phone icon and then click on the Make this app button.
Now we will be asked if we want to jumpstart the app by creating a form or gallery. For this example, we will want to click on the Create a gallery link.
In a couple of seconds, we will have a simple gallery app, that is connected to sample data.
We want to connect to the new Entity that we linked into the Common Data Service, so we will want to create a new data connection by clicking on the data connection and then click on the + Add a Data Service button.
This will open a list of all of our connections. If you haven’t connected to the Common Data Service already, then you will want to add a connection.
Here we already have a link to our Common Data Service connection.
This will open up a list of all of the entities that we have within our Common Data Service.
If we type sellable into the Choose a table filter, then we will be able to find the new data entity that we imported into the Common Data Service.
All we have to do here is select the entity and then click on the Connect button.
This will automatically map some of the fields – i.e., the Product Number.
But the Subtitle field is not mapped to the right field so we will just click on the dropdown list for the field and select the productname field.
Now the data looks a little cleaner, and we can click on the X to close the panel.
That will return us back to the canvas, and we will be able to see all of the data.
We can make the gallery a little bigger so that it fills the entire form.
And also we can browse down through the data, and we can see all of the data coming directly from Dynamics 365 for Finance & Operations.
We have just connected a table from Finance & Operations and integrated it into the Common Data Service so that it acts as if it is any other entity within the system.
How cool is that.
Is the connection to dynamic read only, can we write back data.
The easiest way to do a write back is through a Flow trigger, I haven’t tried updating through the OData connection, but it is bidirectional so it may work.