Since NAV2
Since NAV2016, the possibility of synchronizing data from a table outside the database application with a table “created” within the application was introduced natively.
This synchronization allows the use of external tables as if the application itself allow to consult, modify, delete and create records and also allow the inclusion of code to implement business logic in that external table through the application. These external tables may or may not be on the same server as the application database.
This synchronization can be done with the OnPremise version, not with SaaS.
We have the following table, in this case, on the same server and in the same SQL instance as Business Central:
An external connection must be made from Business Central to be able to modify and consult the data in said table.
The first thing, we must create is the table in Business Central. This table will not generate a new table in the Business Central database since it is a foreign table, that is, the data is not duplicated.
The table properties are “TableType”, “ExternalName” and “External Schema”.
When making the fields in the table we must bear in mind that:
The type of the fields, corresponding to the external table, will correspond to the following conversion:
The “ExternalName” property of the fields is only required if the table name for Business Central does not match the name of the SQL table. In the case that I am using there is a missmatch between languages so the “ExternalName” property of the fields has to be filled in.
I have also added logic to the table so that when modified, from BC, the code fills in the description.
Now we go with the realization of the connection. Depending on the way you want to work, this can be done from a codeunit subscribed to “LogInManagement” or from the page itself when it is opened.
The code would be:
IF HASTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyTableConnection1') THEN
UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,'MyTableConnection1');
REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyTableConnection1', 'Data Source=MyDatabaseServer\NAVDEMO;Initial Catalog=MyExternalTable;Integrated Security=SSPI;');
SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,'MyTableConnection1');The first two lines are used to not register the connection several times since it would lead to an error.
Depending on the way in which the connection with the external database is made, a connection string or another will be used so that:
Windows Login:
REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyTableConnection1', 'Data Source=MyDatabaseServer\NAVDEMO;Initial Catalog=MyExternalTable;Integrated Security=SSPI;'); (Realmente es opcional en este caso añadir "Integrated Security=SSPI;")
SQL Login:
REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, '<TableConnectionName>', 'Data Source=<DatabaseServer>\<DatabaseServerInstance>;Initial Catalog=<DatabaseName>;User ID=<username>;Password=<password>');
AZURE SQL:
REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyAzureTableConnection1', 'Server=myazureserver.database.windows.net;Initial Catalog=MyAzureDatabase;User ID=admin;Password=P@ssword123!;');In this example it will be done by subscribing to the codeunit event «LogInManagement» «OnAfterCompanyOpen» but it can be done from a report or a page:
And I have created a page to be able to see and modify the data:
And a processonly report. This report can be executed because it is at the time of opening Business Central where the connection with the DB is established:
Let’s check the result.
When opening the page:
Adding a new code automatically adds the description because of the code established in the “OnValidate” trigger of the table:
Execution of the processingonly report, we will see the message and modified descriptions:
At the end, we are gonna check result in the external database:
As we have verified, this is a very powerful utility since it allows you to integrate external database tables within the application, allowing you to consult, modify, create and delete records and also being able to add business logic through code in the application.
As I have said before, Connection code inserted in the LogInManagement codeunit could be done at the report or/and the page so that the connection is not established when the application is started and thus avoid unnecessary external connections.
