Hi all,
for a new Project (Integration of Landwehr L1 into NAV1) I have to read Data from Firebird SQL (Customer Data and Posted Invoices). My plan is to read data from Firebird and write into temporary Table for further processing…
Anybody already done this and willing to share code ?
Thanks and nice weekend.
I have done something similar from Oracle to NAV 2016, read the data from Oracle and push into NAV temporary table via Integration database through a small Javascript code and then from NAV temporary table validate the data and create Sales orders and post the Sales order batch to get the Posted sales invoice because you have to validate data in Nav before hits the posted sales invoice table viz Sales invoice header and Sales invoice line.
Hi Dirk,
Had a request for something like this many times. Not especially from Firebird SQL, but getting the data from on DB out into a “readable” format normally isn’t what is the most challenging.
Shbatbng’s methode could be used. What have been doing prior to NAV 2016 was something very similar.
Since you say you have NAV 2016 I would definitely recommend you to look into if the Data Exchange Framework.
https://msdn.microsoft.com/en-us/library/dn495318(v=nav.90).aspx
The DEF gives you more or less a read-to-use tool to create your own imports and exports. All you have to do is to either have a javascript or something similar like PowerShell to extract the data from Firebird SQL and turn it into a “readable” file. Then DEF can take it from there. More or less as I wrote. You could also build a codeunit to do it from inside NAV.
DEF allows you to map the imports using a Intermediate Data Import table (1214), but still map it to the customer and sales orders. DEF handles the validations and allow you to map fields using regexs etc. The part that always end up taking much more time than expected. It may take you a couple of “imports” to get really a specialist on. But there are many examples in the Cronus database. The most comparable example is maybe the OCR Invoice import functionality. Instead of importing purchase invoices, you need it to import sales orders/invoices and customers. The more you use, the easier it gets. In my latest project we used a web service to read it from the other database. No code as such to share, more complex, but basically the same.
I assume even thought you wrote Posted sales invoices, then you are planning to import into table 36/37 and post them in NAV? Importing into the posted sale invoice tables is a “no-go”. I’m sure you’re aware of this, but other member reading this may not be. So just wanted to be sure. [:)]
Thank you for your suggestions… I will check the DEF…
Did anybody try to create a table as “external SQL” ?
If I can use the external Table as Table in NAV, this would be really great !
Hello,
i’ve tried External SQL it and it is awesome. It works with SQL-Views too. You could make a View in SQL which shows a Openquery to Firebird SQL and use this in Dynamics NAV.
This helps to start.
msdn.microsoft.com/…/dd338982.aspx
best regards,