Help section for AL Synchronize Databases
HOW TO DO THE SETUP FOR AL SYNCHRONIZE DATABASES
Enter the Node page, it will show you all the existing connections between Business Central and your external databases. If no connection was ever created, the list will be empty. To create a new link to an external database click on new and enter all the information of the new connection:
- Choose the database type, either Microsoft SQL or PostgreSQL. Other database types may be added: if that is your case, contact us at support@applibra.com
- Enter the database server address
- Enter the Port number
- Enter the database name
- Choose the node group ID and set up the data synchronization frequency. You can add as many IDs you need, clicking on new; choose the script restart frequency and the data refresh frequency of every node or node group
- Enter the update interval, namely the frequency you want your data to be updated with
- Enter the domain name, if you are working from a WAN connection
HOW TO CONNECT EXTERNAL DATABASES WITH BUSINESS CENTRAL
Click on Node interface, the page will display all the existing connections with the tables that need to be synchronized. To create a new connection with a table, click on new and enter the relevant information:
- Select the Business Central table ID, that has to be connected. If you don't find the table ID on the list, you can add it yourself clicking on new, then on the 3 dots on the empty line and choosing the table number from the drop down menu. We advise you to tick all the 4 boxes, so that data from Business Central and external databases are always consistent.
Warning: make sure that the table name entered in the interface name field is consistent with the one on the external database.
- Set the SQL status: this is necessary to let the server know if the table that needs to be connected either exists, has still to be created, has to be deleted, has to be deleted and created anew.
- Tick the box if you want to import the data rows one at a time
- Enable the field Export all records to Export all records upon activation
- Choose the Update Interval for the Interface you are setting
- Enable the import from DB box, if you want to import your data from the external server to BC
- Enable the export to DB box, if you want to export your data from BC to the external server
HOW TO SYNCHRONIZE AN EXTERNAL DATABASE WITH BUSINESS CENTRAL AND VICEVERSA
Set the synchronization options: you can enable the synchronization for every data input, modification, deletion and refresh cycle. We advise you to enable all the four fields, so that your data are always consistent.
- Enable the event post on after data import, if you have additional code to execute
- Enable the event Delete on Post After Data Import, if you want to delete the line after importing and posting data.
- Enable the Use synchronization fields box to set the following additional fields, instead of using the primary key:
- Server modification field name, enter the name of the external database modification field
- Client modification field name, enter the name of the Business Central modification field
- Server deletion field name, enter the name of the external database deletion field
- Client deletion field name, enter the name of the Business Central deletion field
- External ID field name, if existing, enter the name of the ID field
In the Node Interface Fields Subform make sure the field number of the Business Central table and the corresponding field name of the external database are correct.
Check the Synchronize box, if you want to synchronize the fields present on the line. Check the Accept Null Value box, if the external database recognizes NULL values.
You can also opt for validating the fields, enabling the Validate Field box on the respective lines.
Filters: this field is useful if you need to filter data through specific criteria. To make it work, enter such specific field values in the field.
Once everything is set, change the connection status to "enable" in the general section above.