Sync data between Business Central and SharePoint

Introduction:

This text describes how to implement bi-directional data synchronization between SharePoint and Business Central using Power Automate flows.

Scenario:

Consider a scenario where a company uses Business Central to manage purchase orders internally but wants to allow external users or customers to access and verify these purchase orders without giving them direct access to Business Central ERP. Instead, they are given access to a SharePoint site where their purchase order is listed. To achieve this, we create a corresponding table in SharePoint for purchase orders, and the data between the SharePoint table and Business Central is synchronized.

Solution Overview:

The synchronization between Business Central and SharePoint is achieved using Microsoft Power Automate flows.

The synchronization process involves four key steps, all done by Power Automate flows:

1. New Entry in Business Central: Whenever a new entry is created in Business Central, a corresponding entry is automatically created in SharePoint.
2. Update in Business Central: Whenever any entry is updated in Business Central, same entry is also updated in SharePoint.
3. New Entry in SharePoint: Similarly, when a new entry is added in SharePoint, it triggers the creation of a corresponding entry in Business Central.
4. Update in SharePoint:  Updating an entry in SharePoint automatically updates the corresponding entry in Business Central.

Prerequisite:

Before syncing the 'Pro Forma list' between SharePoint and Business Central, it is essential to create the Business Central API for the 'Pro Forma' list. This API enables Power Automate to access the 'Pro Forma' list of Business Central

New Entry in Business Central:

This flow is designed to trigger when a new record is created in Business Central in the 'Pro Forma' list. The objective is to automatically create a corresponding entry in SharePoint's 'Pro Forma' list.

The flow begins with a trigger set to activate when a record is created in Business Central. This trigger requires specific connection parameters, including the environment, company, API category, and table name. Upon activation, the trigger returns the Row ID of the new entry in the Business Central 'Pro Forma' list.

The next step is to use the Row ID obtained from the trigger to retrieve all field values of the newly created entry in Business Central. This is done using the 'Get Record' action, which again requires connection parameters for Business Central.

With the record details from Business Central, the flow proceeds to create a corresponding item in SharePoint's 'Pro Forma' list. The 'Create Item' action is configured to map the fields of the SharePoint list with the corresponding fields from the Business Central record, ensuring accurate data transfer between the two platforms.

Update in Business Central:

To ensure that any changes made in the Business Central 'Pro Forma list' are reflected in SharePoint, another Power Automate flow is created:

The flow begins with a trigger set to activate when a record is modified in the Business Central 'Pro Forma list'. Similar to the previous step, this trigger requires specific connection properties, including the environment, company, API category, and table name. The flow trigger will return the Row Id which is used to retrieve all field values of the newly updated entry in Business Central. This is done using the 'Get Record' action.

Now in order to update the corresponding record in SharePoint, the ID of the SharePoint list item needed. To achieve this, another step is added in the flow: 'Get Items' from SharePoint. This step filters the SharePoint list based on a matching field between Business Central and SharePoint, which uniquely identifies the line item. In this case, the 'SalesOrderNumberFromForeCast' field is used for filtering. The filtered result provides the ID of the SharePoint list item that corresponds to the modified entry in Business Central.

 

Finally, with the SharePoint list item ID obtained, the flow proceeds to update the corresponding item in the SharePoint 'Pro Forma list'. The 'Update Item' action is configured to map the fields of the SharePoint list item with the corresponding fields from the modified entry in Business Central, ensuring that the changes are accurately reflected in SharePoint.

New Entry in SharePoint / update in SharePoint:

To handle both the creation and updating of entries in Business Central based on changes in SharePoint, a single Power Automate flow is created:

The flow is triggered by the 'When an item is created or modified' SharePoint trigger. This trigger ensures that the flow is activated whenever a new item is created or an existing item is modified in SharePoint.

A condition is added in the flow to determine whether the record received from SharePoint already exists in Business Central. If the record is found to be present, the flow proceeds to update the existing record with the new values received from SharePoint. If the record is not found, the flow creates a new record in Business Central with the values received from SharePoint.

Conclusion:

Thus by following the outlined steps and Power Automate flows, the data between SharePoint and Business Central can be synchronized. This synchronization ensures that any input entered by a customer in the SharePoint site is reflected in the company's Business Central and vice versa, all without the need for customers to have direct access to Business Central.