Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 309 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Best way to Insert data from external database into NAV
doddwell
Member Posts: 65
Hello
I have an old 3rd party application that writes Sales Orders into some bespoke tables in our NAVISION V4 database. The 3rd party application inserts the Sales Orders into NAV using a SQL Stored Procedure. I can alter the Stored Procedure but can't alter the 3rd party application.
We are currently upgrading to NAV2017 and have found that the NAV2017 Menu tiles (bespoke tiles provided by our partner) don't update their counts to reflect the number of open Sales Orders written by the Stored Procedure. If I create the records directly in the NAV table, the tile counts are correct. I think the issue is something to do with the timestamp.
What's the best approach to resolve this? Options I can think of are:
1 - Create a NAV Web Service and a SQL CLR Stored Procedure
2 - Amend the existing stored Procedure so that it updates the timestamp (have had a play with this and can't find a way to do it)
3 - Write something in NAV that checks the Order table every minute or so and updates a Boolean field on the table (new field called something like Time_Stamp _Updated). This will update the timestamp and then make NAV aware of the order.
4 - Alter the Stored Procedure so that orders are written to a staging SQL database (outside NAV). Create a SQL job that periodically exports the orders to an XML file. Create a NAV XMLPort to import the orders. Create a NAV job that periodically runs the XMLPort.
Many Thanks
I have an old 3rd party application that writes Sales Orders into some bespoke tables in our NAVISION V4 database. The 3rd party application inserts the Sales Orders into NAV using a SQL Stored Procedure. I can alter the Stored Procedure but can't alter the 3rd party application.
We are currently upgrading to NAV2017 and have found that the NAV2017 Menu tiles (bespoke tiles provided by our partner) don't update their counts to reflect the number of open Sales Orders written by the Stored Procedure. If I create the records directly in the NAV table, the tile counts are correct. I think the issue is something to do with the timestamp.
What's the best approach to resolve this? Options I can think of are:
1 - Create a NAV Web Service and a SQL CLR Stored Procedure
2 - Amend the existing stored Procedure so that it updates the timestamp (have had a play with this and can't find a way to do it)
3 - Write something in NAV that checks the Order table every minute or so and updates a Boolean field on the table (new field called something like Time_Stamp _Updated). This will update the timestamp and then make NAV aware of the order.
4 - Alter the Stored Procedure so that orders are written to a staging SQL database (outside NAV). Create a SQL job that periodically exports the orders to an XML file. Create a NAV XMLPort to import the orders. Create a NAV job that periodically runs the XMLPort.
Many Thanks
0
Best Answer
-
Juhl
Member Posts: 724
Write orders to a staging table. This can be in NAV database.
Have a codeunit create the real order and schedule this.
NEVER insert data into NAV via SQL, there are tons of validation code and related data written in the process of creating a sales order!!!Follow me on my blog juhl.blog5
Answers
Have a codeunit create the real order and schedule this.
NEVER insert data into NAV via SQL, there are tons of validation code and related data written in the process of creating a sales order!!!
The issue is related to NAV data cache
As @Juhl said
Inside this codeunit use the function SELECTLATESTVERSION