integrating navision with other systems based on SQL server.

I have been assigned job to integrate some systems which are based on SQL server with Navision. So what i did , i brought the tables related to that system to the Navision database in Enterprise manager. To integrate the tables of that system with some new tables i created in Navision, i wrote triggers on insert, update or delete in the tables of that system to update Navision tables as soon as there is new data or upated data from other system. This kind of integration is right? if this is wrong , how to make the integration in right way? I guess this is wrong because the Navision tables are fed with data inside SQL and not from navision, which may cause problems when taking the navision backups or upgrades.

Please advice.

Hi Imran,

There might be other more slick ways of doing this, however i’ve done it in a similar way a few times.

There is a few things to keep in mind.
As i understand your task, you only have to put data into NAV?
This mean that you only need to add trigger-code in non-NAV tables, and your backup/upgrade consideration can be removed, i think, if you do it somewhat like this…

I created a “bridge”-database, in which i had the other system leave it’s data.
Then i created a number of “inbox”-tables in the NAV-database, and remember to create those from within a NAV-client, not from Enterprise Manager, to make them accessible from NAV-client.
Then i added trigger-code to the tables in the bridge-database, to have it send the data into my inbox-tables in NAV. Remember to initiate each and every field, as NAV can’t handle null-values.
Then you can either have a user handle the inbox-data manually through a NAV-client, or have a NAS do it automatically.
The essence is that all datahandling inside NAV, must be done either through a client or a NAS, to have the NAV-logic intact.

Have a look at Jitterbit…

One more option is to just add a foreign view to the Navision database and create a table witin NAV with the same data structure and field names as in the view.

Then set the LinkedObject property of the table.

You can now access the external data in the other database directly from within Navision.

What I would suggest is that both databases are beeing held at the same server as otherwise the performance would be beyond any imagination (in the worst sense).

Using triggers and views both create a dependency between the Navision database and the other database. This means that when one of them is not available, the other is affected. If this is not acceptable, than you should look for alternatives. When a little latency is no problem, then integration tools like Jitterbit of SqlServer Integration Services can help. Otherwise you will need some kind of reliable messaging solution.

The Nav Business logic is another aspect. What happens when you delete a record in the other database? Only use views if you can delete records without consequences on the Navision side.