Welcome to our blog where will be sharing tip, tricks and detailed walk-throughs covering all areas of Dynamics 365 but primarily focused on the Field Service and Project Service Apps, Scheduling and integration using Microsoft Flow and the Common Data Service (CDS). We hope you find the blog posts useful and please feel free to comment if you would like us to elaborate on any particular area. 

  • Tash

CDS Data Integrator - CE to Operations - Account/Customer

Over the past year I have been using the CDS a lot in pre-sales demonstrations, and as a result we have had to use it in live deployments and a lot of lessons have been learnt. It's a new year and I am now in the process of rebuilding demo environments and setting up these environments for CE and Ops integration taking into account these lessons learnt and latest features released by Microsoft. I am setting up 3 demonstration scenarios to show Dynamics CE and Operations as an integrated solution:

- Quote to Cash

- PSA to Operations Projects

- Field Service

In these integration solutions I am not going to use any of Microsoft CE solutions. I don't like the Quote to Cash solution as it locks down Sales Order and Invoice and it currently doesn't seem to be working with the latest CE versions.


Let's get started with CE Account -> Ops Customer

There is plenty of articles online introducing the Data Integrator covering the basics of:

- Creating a connection

- Creating a connection set

- Creating a data integration project

- Customising and creating a new template

I suggest reading through the following guide first. https://docs.microsoft.com/en-gb/power-platform/admin/data-integrator


In this blog series I am going to cover specific issues I came up against and some things I have done to troubleshoot. If you deploy the Prospect to Cash solution you might not face all of these problems.


1 - CE solution for CDS specific configurations

Created a solution in my CE instance for any configurations I needed to do in CE to make the integration work.

Added a checkbox on Account record - Sync to Operations. When testing and working out the routines I prefer to have a limited subset of data. I add this checkbox to all CE -> Ops entities, further down the blog post we will cover how to add this check into the query.

2 - Microsoft Template

In admin.powerapps.com/dataintegration I started with the Microsoft Template for Account (Sales to Fin and Ops) - Direct

3 - Metadata refresh

When the template loads, even without the P2C solution all field mappings should appear. If the Operations fields (Destination) are not appearing, refresh your F&O entity list. See https://community.dynamics.com/365/financeandoperations/b/howtodynamics365/archive/2018/07/18/how-to-refresh-data-entity-list-in-dynamics-365 for instructions.

4 - Enabled Advanced Query

Microsoft built the Account to Customer CDS sync prior to Advanced Query being released. I convert all templates over to advanced query so i have the power to add additional filters and configurations into the message.

The screen shot below shows the template before being converted to advanced query. Note the use of Map Type functions.

Enable advanced query on the project, this cannot be undone.

Once this is enabled, the function mappings are moved to the Advanced Query. Note in the screen shot all Map Types have moved to equals and 'Advanced Query and Filtering' is now available.

5 - Data Quality

Operations is a lot more strict on data quality than CE. Couple of key items that will results in errors when trying to create a customer record in Ops from CE are as follows:

a) If the Account number is not populated, sync will fail. In the P2C solution a unique ID is auto populated but I prefer freedom on setting what this auto number is & for testing purposes I just type it in.

b) Address field values. Ops will not accept rubbish data namely around address country. The best part of the CDS template Microsoft provides is that they have done conversions on many different ways an address can appear. If you open up the Advanced Query and Filtering section you will see ReplaceValue commands for typical country spelling with how the country needs to be represented in Operations.

6 - Add a filter to the sync, via Advanced Query Filter

In step 1 a new field was added to CE where I only want accounts passed over as customers if 'Sync to F&O' checkbox is selected. More often than not new configurations added to CE do then not display in the data integrator project, even if Refresh Metadata is selected.

6a. Open Advanced Query Filters, select schema and copy all the schema text into notepad.

6b. Search for the new field added. If the field is not found, you will need to manually add the field into the schema. In my scenario I added in {"ebecs_synctooperations",Logical.Type},

6c. Add field to URL. If you select the URL text, you will notice the new field is not appearing. In my scenario I add in ebecs_synctooperations

When making changes to a URL, these changes can be tested by pasting the entire URL into a browser window where you are authenticated to the CE instance.

6d. Select _data_ area (1), add a Filter to the ebecs_synctooperations column (2) by clicking on the drop down arrow.

Select True (1) in the filter panel and ok (2)

Click on Insert to add the new step

The “Filtered Rows” step has been created and this can be renamed (right click on step) to a description name that’s easy to identify the filter that’s being applied in this step.

NOTE - consider performance when applying filters. This filter could have been applied at the end of all the existing transformation steps, however as this checkbox significantly limits the number of accounts being passed into the query, it is best for it to be up front.

600 views0 comments