Create sales order from SQL

schoolkid

Hi all,

i created a sales order Buffer table in NAV and on the ONINSERT() trigger i have written codes to insert the same data into Sales Header table , now i open SQL server and insert data into the SALES order Buffer table but the code written will not work and the insertion to sales header doesnt happen.

when i do the same in Cside Client it works .

i want to insert data into Sales orders form once it gets inserted in the Buffer table..


any help on this please

Thanks in advance

matttrax

SQL has no idea about the business logic (code) in NAV. You'll need to run some process from within a NAV client to use that.

KYDutchie

Hi,

beware that the OnInsert trigger is only executed if you do an Record.INSERT(TRUE) from within Navision
It is not executed because you insert a record in the table in SQL from outside Navision.

To be able to do that is a completely different and difficult process.

Regards,

Willy

schoolkid

thanks for the replies ..

Now i have a SalesTable in SQL and i want to push the Data into NAV ..


can u suggest me which is the best possible solution that i can do to insert the data into NAVISION



Thanks in Advance...

matttrax

The best solution is to insert into your temp table and then run a process from within NAV. That can be done manually, on a timer, through a NAS with job scheduler, etc.

There is a ton of business logic and data validation that goes into creating orders.

KYDutchie

Hi,

We have done something similar, for Freight amounts . We have several third party applications write to a view of our table. We have a SQL trigger on that table that inserts a message into a Message queue. This message is picked up by the NAS and it will add a sales line to an existing sales order that has the freight amount and package tracking number. But be careful, this is not an easy process!

Inserting a Sales Header record through the NAS could be a tricky thing to do. What are you going to do when the Customer is blocked or over the Credit limit? You cannot have "Confirm" messages or any kind of form opening when the code is executed through the NAS.

I would suggest that you create a process that is run periodically by a user to transfer the information from your temporary table to the real Sales tables. This is by far the easiest solution.

I hope this helps,

Regards,

Willy

jlandeen

As outlined here there are many different approaches that you can take to get this data into NAV. If you are running in version 5.0 the Job Scheduler that comes with NAV out of the box is a useful tool to automate these kinds of tasks.'

I recently worked on a project that had some similar requirements only it was working with Purchase Documents. The staging table approach works well when integrating with external systems and if you write to some sort of inbound sales invoice table and then read records out of that with something like a process only report or a codeunit to generate the necessary sales invoices.

I would suggest that your process only report has the following features:
- is fault tolerant (i.e. when it processes 10 records and 8 of them work and 2 error out, customer blocked, customer does not exist it successfully creates 8 invoices and leaves 2 in the staging table)
- You will probably need to use the if codeunit.run syntax to make it fault tolerant
- can be run by a user or via NAS -> use the if GUI allowed to check and determine if you can accept input from a user or provide status messages about progress
- use a progress bar if the process will loop through a lot of data and will be run by a user (this will ensure they know the process is still running and don't think the NAV client has hung)
- invoke as much base NAV logic as possible so orders are properly created (i.e. validate fields instead of just updating their value)
- the less custom code that you have to write the easier it is to support and maintain the solution

One final suggestion when building an integration process like this - think about what you would manually do to build up an order from the data that is passed to NAV from the staging table and use that as a plan for what you are trying to build. I find these integration projects a lot simpler when you think of them as just automating a manual process.

David_Singleton

jlandeen wrote:

One final suggestion when building an integration process like this - think about what you would manually do to build up an order from the data that is passed to NAV from the staging table and use that as a plan for what you are trying to build. I find these integration projects a lot simpler when you think of them as just automating a manual process.

=D>

actually this is how I tell all developers to think when they develop anything. Start by doing it all manually (even going to the tables and typing data in new fields if necessary) and once you can do it manually THEN start coding.


It great to see more people thinking like this, and I wish everyone would.

jlandeen

:oops:

thanks David!

David_Singleton

When I am with a customer and they start asking for some silly modification, I get them to show me how they would do it manually step by step. Taking screen shots along the way. In the end either they realize its a crazy requirement, OR I have a fully document work flow ready to give to a developer.

jlandeen

I find that it's also a very good way to minimize customization -> therefore the solution is more maintainable going forward.

Also if most of the task automation steps can be completed in a new codeunit or custom report then the base object, new code stays nice and modular and won't impact upgrades too much.

schoolkid

Thank you all for your replies .i will work on the suggestions given by you and get this job done in a good way....

David_Singleton

schoolkid wrote:

Thank you all for your replies .i will work on the suggestions given by you and get this job done in a good way....

=D>