If you need to be sure that the data in Business Central and in an external system stay in sync and that there is always the same information in both system, you need to use some kind of synchronization. There are basically two scenarios:
- Synchronize data from Business Central to an external system (e.g. update Customer data in an external system if there is a change in Business Central).
- Synchronize data from an external system to Business Central (e.g. download new orders).
For both scenarios you will use Synchronization entries that allow you to keep track of all synced records and operation run results.
¶ Synchronization Entries
¶ Overview
Synchronization entries are always related to the RESTwithUS operation. You can see their overview in the Operations list (scheduled entries and entries, that ended with some kind of error, have their own columns for easier management).
Tip: You can use function Create Synchronization Entries to create multiple entries at once (e.g. for an initial synchronization). The function will use the first table in the request body for filtering.
Click on any number or use action Synchronization Entries to open the list of entries for selected operation.
Each entry contains some basic information:
- Operation ID and Description – related RESTwithUS operation
- Source Record and Table ID – record in Business Central related to the entry (e.g. the Customer that should be synced to external system)
- Status
Scheduled– The entry is scheduled and will run in the future.Processing Error– all attempts to run the operation were not successful (you can see the error in the field Error Text).Error ReTry– operation run was not successful, but maximum No. of Attempts was not yet reached, so it will be repeated in the future.
- Date and time of creation and last processing
- Origin Description – custom field with information about the process or event that created the entry.
When solving issues, you can select multiple entries and use Reschedule or Run Now action. If the entry is related to a Business Central record, you can view it using action Show Record.
¶ Max No. of Attempts
If the operation run ends with an error, each entry can be run multiple times with some delay between attempts. This can help solving issues like temporary service malfunction etc. If all attemps end with an error, the entry will be set to the Processing Error status.
You can set the maximum no. of attemps in the provider Details.
¶ Job Queue Entry
RESTwithUS automatically creates job queue entry that will periodically check for scheduled entries and run them in the background. Entries are processed in the same order in which they were created. By default the job runs every 15 minutes but you can change that in job queue entry details.
¶ Create and Handle Sync. Entries
You can create sync. entries in a batch using action Create Synchronization Entries but in most cases you will create the entries from code. In the following example you create sync. entry whenever the customer is created or updated.
[EventSubscriber(ObjectType::Table, Database::Customer, 'OnAfterInsertEvent', '', true, true)]
local procedure OnAfterCustomerInsert(var Rec: Record Customer; RunTrigger: Boolean)
begin
APIScriptRWU.CREATE_SYNC_ENTRY(ProviderID::RWUTest, OperationID::RWUTest_CreateCustomer, 'Customer record was created', Rec);
end;
[EventSubscriber(ObjectType::Table, Database::Customer, 'OnAfterModifyEvent', '', true, true)]
local procedure OnAfterCustomerModify(var Rec: Record Customer; var xRec: Record Customer; RunTrigger: Boolean)
begin
APIScriptRWU.CREATE_SYNC_ENTRY(ProviderID::RWUTest, OperationID::RWUTest_UpdateCustomer, 'Customer record was updated', Rec);
end;
If there is an existing
Scheduledsynchronization entry for the same Business Central record and RESTwithUS operation, the function will not create a duplicate.
Tip: CREATE_SYNC_ENTRY function returns the Entry No. of newly created entry if successful. You can use that in your code to work with the entry afterwards.
In some basic scenarios this might be enough to run the operation with sync. entry, but in most cases you will need to add your custom code to run the operation with all required parameters. You can do this by subscribing to OnBeforeHandleEntry event in codeunit Sync. Entry Mgt RWU.
[EventSubscriber(ObjectType::Codeunit, Codeunit::"Sync. Entry Mgt RWU", 'OnBeforeHandleEntry', '', false, false)]
local procedure OnBeforeHandleEntry(_SyncEntry: Record "Synchronization Entry RWU"; var IsHandled: Boolean);
var
Customer: Record Customer;
begin
case _SyncEntry."Operation ID Enum" of
"Operation ID RWU"::RWUTest_CreateCustomer, "Operation ID RWU"::RWUTest_UpdateCustomer:
begin
Customer.GetBySystemId(_SyncEntry."Source SystemID"); //Get the customer related to the entry
RefreshCustomer(Customer); //Create non-existing customers, update existing ones
IsHandled := true; //Set the entry as handled
end;
end;
end;
- See the Recommended Code Snippet of POST / PUT operations for
RefreshCustomerfunction details. - In older Business Central versions you may need to use following code to get the related record:
Customer.Get(_SyncEntry."Source Record ID");
¶ Synchronization Entries: External System -> Business Central
There are some major differences when synchronizing data from an external system to Business Central:
- You will mostly use GET Multiple Records and GET Single Record operations instead of PUT / POST.
- The synchronization entry usually won't be associated with Business Central record (the order may not exist in Business Central yet).
- You need to use some kind of timestamps to mark the last record (e.g. order) that was synced from an external system – and download all new records in the next run.
¶ Subscribe to OnBeforeHandleEntry Event
But the first step is the same: subscribe to the event OnBeforeHandleEntry and handle the operation run (in this example the download of multiple new orders)
[EventSubscriber(ObjectType::Codeunit, Codeunit::"Sync. Entry Mgt RWU", 'OnBeforeHandleEntry', '', false, false)]
local procedure OnBeforeHandleEntry(_SyncEntry: Record "Synchronization Entry RWU"; var IsHandled: Boolean);
begin
case _SyncEntry."Operation ID Enum" of
"Operation ID RWU"::RWUTest_GetOrders:
begin
GetOrders(_SyncEntry."Entry No.");
IsHandled := true;
end;
end;
end;
¶ Get the List of Records
Function GetOrders can look as follows.
procedure GetOrders(_SyncEntryNo: Integer)
var
LastTimeStamp: DateTime;
begin
APIScriptRWU.INIT("Operation ID RWU"::RWUTest_GetOrders);
APIScriptRWU.ENDPOINT("Provider ID RWU"::RWUTest, "Operation ID RWU"::RWUTest_GetOrders);
//Get the last record timestamp from order entity and pass it to URL parameters
LastTimeStamp := APIScriptRWU.TIMESTAMP("Provider ID RWU"::RWUTest, "Entity ID RWU"::RWUTest_Order);
if LastTimeStamp <> 0DT then
APIScriptRWU.ADD_VARIABLE('','URLFilterModified',Format(LastTimeStamp));
//Execute the operation to download selected records
APIScriptRWU.EXECUTE();
//Update the timestamp of last record on the order entity
APIScriptRWU.TIMESTAMP("Provider ID RWU"::RWUTest, "Entity ID RWU"::RWUTest_Order, LastTimeStamp);
end;
Tip: You can use LAST_AUTO_INCREMENT function if it is easier to index last external id instead of timestamp.
¶ Save Single Record
If you do not want to rely on the RapidStart functionality to save the records in Business Central, you can process the result with code. Add following lines to the function.
var
JSONData: JsonToken;
JArray: JsonArray;
JToken: JsonToken;
ResponseBodyList: List of [Text];
JSONBody: Text;
//Execute the operation
APIScriptRWU.EXECUTE();
//Go through all pages of the result and process them one by one
APIScriptRWU.GET_RESPONSE_JSON(ResponseBodyList);
foreach JSONBody in ResponseBodyList do
if JSONBody <> '' then begin
JSONData.ReadFrom(JSONBody);
if JSONData.SelectToken('$.data',JToken) then begin
JArray := JToken.AsArray();
foreach JToken in JArray do
ProcessOrder(JToken, LastTimeStamp);
end;
end;
ProcessOrder function may look as follows.
local procedure ProcessOrder(_JSONData: JsonToken; _LastTimeStamp: DateTime)
var
SalesHeader: Record "Sales Header";
JToken: JsonToken;
TmpDateTime: DateTime;
begin
//Create new order
SalesHeader.Init();
SalesHeader.Validate("Document Type", SalesHeader."Document Type"::Order);
if _JsonData.SelectToken('$.order_id', JToken) then
SalesHeader.Validate("Your Reference", JToken.AsValue().AsText());
//... process all the other header fields and order lines
//Save the order to Business Central database
SalesHeader.Insert(true);
//Update the timestamp of last record on the order entity
if _JsonData.SelectToken('$.modified', JToken) then begin
TmpDateTime := GetDateTime(JToken.AsValue().AsText()); //Get the date and time from JSON node with a custom function
if TmpDateTime > _LastTimeStamp then
APIScriptRWU.TIMESTAMP("Provider ID RWU"::RWUTest, "Entity ID RWU"::RWUTest_Order, TmpDateTime);
//Commit all changes before trying to process another record
Commit();
end;
¶ Advanced Options
This is obviously just a basic concept that has at least following issues:
- The Connection for newly downloaded order won't be created (you may need to create those manually).
- The synchronization stops on the first record that ends with an error and won't proceed until the error is solved. The solution can be as follows:
- Call the
ProcessOrderfunction withif Codeunit.Runpattern and manually handle (or skip) all errors when saving the record to Business Central. - After the function is called, create a new synchronization entry for each downloaded order to keep track of processed records. Manually update the new entry to either set it as
Processedor save the error details. - You can even save the external id of the record in the sync. entry Origin Description and update your code so it will enable the download and processing of a single record. (e.g. if the single order download fails, you can just manually run the newly created sync. entry to retry it).
- Call the
- The entry will run just once and won't be repeated. You can fix that by rescheduling it after the job queue run ends with following code.
[EventSubscriber(ObjectType::Codeunit, Codeunit::"Job Queue Dispatcher", 'OnAfterHandleRequest', '', true, true)]
local procedure OnAfterHandleRequest(var JobQueueEntry : Record "Job Queue Entry"; WasSuccess : Boolean)
var
SyncEntryRWU: Record "Synchronization Entry RWU";
begin
if JobQueueEntry."Object ID to Run" <> 4075265 then
exit;
//Reschedule the periodical download sync. entry
SyncEntryRWU.SetRange("Provider ID Enum", "Provider ID RWU"::RWUTest);
SyncEntryRWU.SetRange("Operation ID Enum", "Operation ID RWU"::RWUTest_GetOrders);
SyncEntryRWU.SetRange("Origin Description", 'Periodical order download');
if SyncEntryRWU.FindFirst() then
APIScriptRWU.RESCHEDULE_SYNC_ENTRY(SyncEntryRWU) //TODO: APIScript function does not exist yet, you would need to do this manually
else
APIScriptRWU.CREATE_SYNC_ENTRY("Provider ID RWU"::RWUTest, "Operation ID RWU"::RWUTest_GetOrders, 'Periodical order download');
end;
¶ Synchronize Data with Timestamps (deprecated)
The main idea of synchronization is simple: You need to save somewhere a timestamp (or an id in some cases) of the record you updated last. Then you repeatedly call synchronization job, but it updates only those records that have changed from the last time. After each successful API call you should update the timestamp in case some later operation fails.
In RESTwithUS the timestamps are saved on Entity level:
You can work with the timestamp on an entity using following code:
var
APIScriptRWU: Codeunit "API Script RWU";
EntityID: Enum "Entity ID RWU";
ProviderID: Enum "Provider ID RWU";
LastTimeStamp: DateTime;
//Save current timestamp to Customer entity
APIScriptRWU.TIMESTAMP(ProviderID::TestProvider,EntityID::TestProvider_Customer,CurrentDateTime());
//Get timestamp of last exported record from Customer entity
LastTimeStamp := APIScriptRWU.TIMESTAMP(ProviderID::TestProvider,EntityID::TestProvider_Customer);
//Save the last synchronization date and time to entity (use this to mark last successfull sync. time)
APIScriptRWU.SYNC_DATETIME(ProviderID::TestProvider,EntityID::TestProvider_Customer,Today(),Time());
Tip: For more API Script functions see guideline API Script Functions Reference.
¶ Run the Synchronization
Let's write two simple functions to run the synchronization. The first one will get the timestamp of last synchronized record, filter the customers to get only the ones modified after this time and call record update on each of them:
procedure SynchronizeCustomers()
var
Customer: Record Customer;
APIScriptRWU: Codeunit "API Script RWU";
LastTimeStamp: DateTime;
EntityID: Enum "Entity ID RWU";
ProviderID: Enum "Provider ID RWU";
begin
//Get the timestamp of last synced record and filter the customer records with it
LastTimeStamp := APIScriptRWU.TIMESTAMP(ProviderID::TestProvider,EntityID::TestProvider_Customer);
Customer.Reset();
Customer.SetCurrentKey(SystemModifiedAt);
if LastTimeStamp > 0DT then
Customer.SetFilter(SystemModifiedAt,'>%1',LastTimeStamp);
//For all records modified after the saved timestamp run the update
if Customer.FindSet() then
repeat
RefreshCustomer(Customer,true);
until Customer.Next() = 0;
//If the synchronization was successful, save the sync. date and time
APIScriptRWU.SYNC_DATETIME(ProviderID::TestProvider,EntityID::TestProvider_Customer,Today(),Time());
end;
In this example you are using
SystemModifiedAtsystem field, which is available from Business Central version 17 (2020 wave 2). See the Last Time Stamp and Last Auto Increment section for sync using SQL timestamp or integer fields.
Now let's write the RefreshCustomer function. This function will create a new Customer in an external system if it was not created yet – and update his data if he already exists.
Notice the _UpdateTimestamp parameter: With it, you can tell the function, if it should update the timestamps on the Customer entity after successful API operation. This serves two purposes:
- You can call the function from elsewhere in your API integration with the parameter set to
false(e.g. when a user creates a new Customer from a menu action), and the timestamps remain intact. - When updating five Customers in a batch, the data are committed and timestamp saved after each successful API operation. So for example if the second API operation fails because of server error, in the next batch run you will start from it.
Be really careful with the
Commitfunction, though – it does not save only the changes made by these two functions but all changes made during the whole process so far.
procedure RefreshCustomer(_Customer: Record Customer; _UpdateTimestamp: Boolean)
var
APIScriptRWU: Codeunit "API Script RWU";
EntityID: Enum "Entity ID RWU";
ProviderID: Enum "Provider ID RWU";
OperationID: Enum "Operation ID RWU";
MappingId: Text;
begin
//Get the Customer mapping id from entity connections
MappingId := APIScriptRWU.MAPPING(ProviderID::TestProvider,EntityID::TestProvider_Customer,_Customer);
//For BC 18 and newer use record SystemId instead
//MappingId := APIScriptRWU.MAPPING(ProviderID::TestProvider,EntityID::TestProvider_Customer, Customer.SystemId);
//Connection not found, create the record
if MappingId = '' then begin
APIScriptRWU.INIT(OperationID::TestProvider_CreateCustomer);
APIScriptRWU.ENDPOINT(ProviderID::TestProvider,OperationID::TestProvider_CreateCustomer);
APIScriptRWU.ADD_VALUE('/',Customer);
APIScriptRWU.EXECUTE();
end
//Connection found, update the record data
else begin
APIScriptRWU.INIT(OperationID::TestProvider_UpdateCustomer);
APIScriptRWU.ENDPOINT(ProviderID::TestProvider,OperationID::TestProvider_UpdateCustomer);
APIScriptRWU.ADD_VARIABLE('','CustomerId',MappingId);
APIScriptRWU.ADD_VALUE('/',Customer);
APIScriptRWU.EXECUTE();
end;
//If the timestamp should be updated, do it and commit the data
if _UpdateTimestamp then begin
APIScriptRWU.TIMESTAMP(ProviderID::TestProvider,EntityID::TestProvider_Customer,_Customer.SystemModifiedAt);
Commit();
end;
end;
¶ Call Synchronization from Job Queue
To call the synchronization from the job queue you need to create a new codeunit and call the sync function from there. You can then schedule the codeunit in job queue entries.
codeunit 50000 "Test Provider - Synchronize Customers"
{
trigger OnRun()
begin
TestProviderMgt.SynchronizeCustomers();
end;
var
TestProviderMgt: Codeunit "Test Provider Management";
}
¶ Last Time Stamp
In Business Central 16 and earlier, there is no SystemModifiedAt field. But you can add a SQL timestamp field to the table (or with a table extension):
field(50000; "Record Timestamp"; BigInteger)
{
Caption = 'Record Timestamp';
SQLTimestamp = true;
}
This timestamp will be updated in a similar way as SystemModifiedAt field (with every record change). But the type of this variable is BigInteger, so you need to save it into a different field in the RESTwithUS entity – but you will use the TIMESTAMP function as usual:
var
LastTimeStamp: BigInteger;
//Save the timestamp to Customer entity
APIScriptRWU.TIMESTAMP(ProviderID::TestProvider,EntityID::TestProvider_Customer,Customer."Record Timestamp");
//Get timestamp of last exported record from Customer entity
LastTimeStamp := APIScriptRWU.TIMESTAMP(ProviderID::TestProvider,EntityID::TestProvider_Customer,0);
The last timestamp will be saved in the Last Time Stamp field on an entity.
Tip: The SQL timestamp is not in a readable format, so we recommend saving the sync date and time with SYNC_DATETIME too. It won't be used for processing, but the user will have an easy overview.
¶ Last Auto Increment
There are some cases when datetime timestamp is not necessary or it will not do the job properly. For example:
- If you want to download new Customers created in an external system and they do not have any kind of timestamp – but they have an autoincremented
idcolumn. - If you want to export records with integer primary key from Business Central and the records will not change in the future (like Customer Ledger Entries).
In both cases, you can store id or Entry No. of the last record in the entity and then do the filtering based on this information.
To get the last auto-increment value for the entity use code:
APIScriptRWU.LAST_AUTO_INCREMENT(ProviderID::TestProvider,EntityID::TestProvider_Customer);
To set the auto increment value select an operation, that should update the value. Open the request or response body (request for saving entry no. from Business Central, response for logging id from an external system), select the node with the id and open Home / Details:
Now go to the Connection Management tab and check Log Auto Increment option:
Every operation run will now save the highest id of a changed Customer into Last Auto Increment field in the entity.
¶ Synchronization Reset
For testing purposes you may want to reset the synchronization status and start from scratch – i.e. sync all records once more. To do that select an entity from the list and use Full Reset Sync Status or Reset only Last Autoincrement actions:
- Full Reset Sync Status will reset all four synchronization fields.
- Reset only Last Autoincrement will reset only the Last Auto Increment field.