How to synchronize List data from and to SharePoint

In this example you will use NavSherpa to synchronize BC table data between Business Central and SharePoint List. The tutorial uses option Replicate NAV <--> SP (both ways synchronization), but almost the same setup applies for one-way synchronization too. For possible synchronization options see field Synchronization Type.

If you plan to have more than 5000 items in your list, please read the guideline How to fix error "The number of items in this list exceeds the list view threshold" too. There are some built-in limits on SharePoint that complicate the setup after the 5000 items threshold is reached.

Note: This scenario won't work properly in BC15 and BC16, because NavSherpa is missing SystemModifiedAt timestamp field added in BC17. Without this timestamp, synchronization from NAV to SP will always synchronize all items (not just recently updated ones).

The example synchronizes data from table 5207 Employee Absence (Absence Registration page in BC):

List Setup

Create new List Setup EMPLOYEEABSENCE_LIST. For the synchronization to work you need to fill in Import Config. Package Code and optionally Init Record Template Code fields:

Please select table 5207 Employee Absence, otherwise you can place the List on any SharePoint site.

The Import Config. Package Code field sets a RapidStart Configuration Package that defines, which tables and fields can NavSherpa synchronize back into NAV. You can use our demo NAVSHERPA_IMPORT package and add your tables into it:

Please ensure, that you have your table in the RapidStart package (table 5207 Employee Absence in this example).

Now open the list of table fields:

If there is a checkbox in Include Field column, the field can be synchronized to BC from SharePoint.

Even if you have a Description column in the SharePoint List and in the List Setup, it won’t be synchronized from SharePoint, unless you check the checkbox here. This can be useful, if you want to synchronize some data only to SharePoint, but not back.

Now, create a new Configuration template as following, and fill its code into the Init Record Template Code field in the List setup:

The configuration template must have the same Table ID like the List setup.
You can set up all default values in the Lines area.
If all values in primary key are downloaded from SharePoint, the Configuration Template is not necessary at all. However, if you want to set some of them by default (for example Document Type in table 38 Purchase Header to Invoice), place them in the Configuration Template lines.
The example of table 5207 Employee Absence is a bit specific. The primary key is field Entry No., which is autogenerated by BC. As you will see in the List Setup lines, you will synchronize the field to SharePoint, but you need to place it in the Configuration Template lines anyway – if you don’t, new lines synchronized from SharePoint would not be able to generate correct Entry No.

Let´s have a look at the List Setup Field Mapping:

You can select any field for synchronization and NavSherpa will select the default SharePoint Data Type for you. As you can see on the last line in the example screen shot above "Field Mapping", you will be exporting Employee full name into the Title column, which means you need to set a Table Link to Employee table for this line:

Fields generated with variables (with 0 in Field ID Column) are never synchronized back to BC, only to SharePoint.

Tip: You can synchronize Date fields to Text column in SharePoint, just change the SharePoint Data Type of the field mapping. Then play with the exact date format in the Site Setup Card. When synchronizing Date to Text you may want to disable Date as DateTime and Time as DateTime options:

Lastly, set the Synchronization Method and Type in the Synchronize tab of the List Setup:

For testing purposes, use the Manual Synchronization Method, and you will use Replicate NAV <--> SP Synchronization Type in this example.

Now Deploy the List Setup to SharePoint and Release it:

Testing the synchronization

Create a new entry in the Absence Registration page:

Go to the List Setup and manually start the synchronization. You can use actions Synch. Differences on SP to synchronize last changes only or Synch. All on SP to synchronize all records:

Tip: If you synchronize all records, you can filter them with Record Conditions.

The record should be now synchronized to the SharePoint List:

Let’s try to create a new record on SharePoint now. Please, ensure you are entering valid data into fields like EmployeeNo or CauseOfAbsenceCode:

Please note, that you are not filling the EntryNo field. This should be autogenerated by BC during the synchronization.

Now, go back to the List Setup and select the Synch. Differences on SP action. Refresh Absence Registration page and you should see the synchronized record with correct Entry No. filled-in in the background:

But, if you return back to the SharePoint List, you won’t see the Entry No. in the List yet:

NavSherpa needs to do one more synchronization run, when it synchronizes the changes in BC to SharePoint. Go back to the List Setup and select the Synch. Differences on SP action again. After that you should see proper EntryNo in the SharePoint List:

Run the synchronization automatically

To start the automatic synchronization, go back to the List setup and set the field Synchronization Method to Automatic. After the List setup release, NavSherpa will start the automatic synchronization with Job queue or service tier job:

Please, note the following fields Last Check Date, Last Synchronize Date and Last SharePoint Sync. Date:

Last Check Date shows, when NavSherpa last checked for new entries on SharePoint.
Last SharePoint Sync. Date is the time, when NavSherpa synchronized a new record from SharePoint for the last time. When checking for new entries, this date and time is used as a timestamp and NavSherpa gets just the lines that were changed after.
Last Synchronize Date is the time, when NavSherpa synchronized new record from BC for the last time.

Tip: If you add some kind of Status field to the Employee Absence table and synchronize it to SharePoint, you can use the NavSherpa synchronization with Power Automate flow for absence approvals.

Tip 2: You can synchronize other tables like Employees or Causes of Absence and build a small Power App on top of the SharePoint data – which then gets synchronized right back to BC.

Troubleshooting

See the guidelines on How to troubleshoot NavSherpa synchronization errors.