Sync Master Data on Microsoft Dynamics NAV / D365 Business Central: Extensions or Power Automate

March 15 2020

Editor's Note: This article contains the author's personal views and not those of KPMG .

As a business consultant working on an ERP project for a group of companies, you might have received a business requirement to sync master data between different fiscal companies. If the ERP to implement is Dynamics NAV or Dynamics 365 Business Central, then it is common to follow one of the three options as a first approach:

  1. Create a customization to enable this automation (Extension on Business Central)
  2. Adopt an ISV solution that includes this functionality (There are some in the market usually under the umbrella term "Intercompany")
  3. Select the tables that you want to share between the different companies and change the property DataPerCompany to “No” on the Development Environment and this table will be unique for all the companies of that database (Only on Dynamics NAV or Business Central on-prem).

When starting to detail the business requirement for an end customer, it is easy to face difficulties! Examples of blocking issues include:

  • The second option (ISV solution) will have a considerable footprint on the database. Usually these ISV solutions cover more than only Sync Master Data and a lot the times the related cost of the solution is not competitive
  • The third option (DataPerCompany=’No’) is not the best as some data of some tables may not be completely the same
  • The third option (DataPerCompany=’No’) is not applicable because there is more than one database (having for example different localisations).

So as a partner, we frequently advise the first option. We completely control this way of implementing, we have developers available with the proper skills, and the customer receives a custom-made solution for their requirement.

Let’s be disruptive! Microsoft business applications include more than just Dynamics NAV and Business Central. The suite now includes the Power Platform, and Power Automate (formerly Microsoft Flow) could be the right tool for this requirement.

Some advantages to consider:

  • Power Automate is cloud based, ready, easy to use and friendly for "super users" who want to participate in no-code/low-code projects.
  • It is cost effective.
  • The footprint is minor.

Short step-by-step setup

  1. Create a new Service with NAVUserPassword Authentication (if using Dynamics NAV or Business Central on-prem). Verify some pre-requisites:
    1. To run a new service with Credential Type = NAVUserPassword a certificate should be installed. To install the certificate, follow this guidance.
    2. The OData Service tab is mandatory.
  2. Create a new user on Dynamics NAV or D365 BC to be used by Power Automate
  3. Access Power Automate with an Office 365 account and create a new connection on Power Automate
    1. Sign in to the Power Automate home page and access Settings > Connections.
    2. New Connection, Filter by NAV / Business Central and select your option. Dynamics NAV is shown as in Preview.
    3. OData Feed URL with Syntax: http://[DNSNameAzureVirtualMachine]:[Odata_Port]/[NAV_ServiceName_with_N AVUserPassword]/Odata/
    4. Username (User with NAVUserPassword)
    5. A new connection should be created to each different Dynamics NAV / Business Central database/company.
    6. The same BC / NAV user can be used for all the different connections.
  4. Create a new flow
    1. To create a new flow, select My Flows.
    2. If you try to create a new flow from blank, there is no option to select NAV Action or Trigger. To work around it, you should select to import one. After importing, the NAV action and triggers become available.

This approach is applicable to several scenarios to Sync Master Data between different fiscal companies or even databases. Examples include:

  • Sync CoA between different companies
  • Sync Dimension and Dimension Values between different companies
  • Sync Other Master Data tables between different companies.

Be aware that this is not the best for the following scenarios:

  • Highly scalable Master Data syncs (Lot of records or lot of fiscal companies)
  • Transactions Syncs like intercompany transactions or consolidation transactions.

Flow yourself!!!

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About José Miguel Azevedo

My name is José Miguel Azevedo. I am a Microsoft Certified Business Management Solutions Professional on Microsoft Dynamics 365 Business Central.

My main activity is designing Enterprise Solutions based on the entire stack of Microsoft Business Applications (Microsoft Dynamics NAV, Dynamics 365 Business Central, Azure and Power Platform). In the last 20 years, I worked as Trainer, Business Consultant, Functional Consultant, Project Manager, Engagement Leader, Team Leader, Site Manager, Product Manager and Solution Architect.

During this period, I developed competences in several industries: Fashion Retail and Fashion Wholesale, Manufacturing, Food, Pharmaceutical Distribution and Manufacturing and Professional Services.

Blog: https://maique00.wixsite.com/blog

Linkedin: https://www.linkedin.com/in/josé-miguel-azevedo-29565b4

More about José Miguel Azevedo