Let’s talk about Table type property of NAV tables. There’s a very interesting option in here, ExternalSQL. This option can help us sync a table in Nav with a table in SQL granted they have a same structure.
Let’s see how we can set this up…
- A copy of Nav 2017 ( might work on 16 as well)
- Development License
- Sql server ( Can be on-Premise or hosted , Just need the connection string and credentials)
- Identify the table you want to connect with a nav table. Here I am using a simple demotable with just 4 fields
- Create the table in Nav.
- Now go to table properties and Change the following properties as in Screenshot. The ExternalName should contain the name of the table in external database you want this table to sync with.
- You can also go to individual field property and set ExternalName property in case your nav field name differs from External sql one
- That’s all for the table. Now create a new codeunit in your range.
- Create a new function in it for SQL connection.
IF HASTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName) THEN UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName); REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName,DatabaseConnectionString); SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName);<span id="mce_marker" data-mce-type="bookmark" data-mce-fragment="1"></span>
You will need identify external database name here. You also need to define DatabaseConnectionString. It should be of the form
- Next create a page for the nav table.
- Then Add code to run our connection codeunit in OnInit()
That’s all. Now if you create any record in Nav it gets updated to SQL table and vice versa.