We are working on ‘lookup’ into an external SQL DB from Navision 4.03 SQL. We have some ideas but wanted to get feedback from others who worked on something similar as well.
Basically what we are trying to do is to be able to read a table in an external SQL database. This customer is going to use Navision A/R, A/P and G/L, and they have the other applications in another home grown SQL based system (applications such as Sales Order, Inventory, etc.).
For example, we have a field in Navision SQL which needs to be ‘lookup’ into an external table in another non-Navision SQL database such as: the ‘Payment Terms Code’ field in the Navision Vendor Card should ‘lookup’ into an external SQL table instead of the Navision ‘Payment Term’ table. We did the initial design and are now looking for additional feedback.
We need to know what will be the best option to accomplish that. What tools should we use, etc. We also want to make sure that performance and system response time will be OK. Accessing an external database from a Navision lookup field, retrieving the data, writing into a Navision table and calling the corresponding lookup form can of course affect the speed of the system.
Well, there are basically two options I ould like to propose for your purposes:
Linked Object
To have a real “lookup” - means opening a form which is displaying a list records - on an external table, you could create a View within the NAV database, querying the required data from the external database/table. If this external table is not on the same SQL Server you could use the “Linked Server” feature of SQL Server.
Once you have created the View, you could create a table in NAV which is precisely named (table and fields) as the View. Please regard that the datatypes you use in the View are compatible to NAV-used datatypes; and that the View name needs to have the Company$ prefix if the NAV property “DataPerCompany”=Yes. In this NAV table the the property “Linked Object”=Yes, then this table is linked to the View. Now you could use this table(View) as any other table in NAV; so could use it for real “Lookups”.
This feature is quite convenient if used not too often, and if just reading data from this View, writing could be problematic.
Querying via MS ADO
If you just want to get a specific value, record or recordset to be processed in NAV, you should directly query the remote database/table using MS ADO. You could use MS ADO as “Automation” in NAV; there you could establish a connection using the SQLOLEDB provider and “fire” a real SQL statement to the server. Then you could receive the result and process it in NAV. Depending on the queried target and the query-statement this could be rather fast! This feature could be used for reading and writing.