Create sales orders from external database

lloydf · 2005-05-17

I am relatively new to Navision and am not sure if it is possible to do the following:
I have a web site which takes sales orders. These orders are logged in a sql server database within two tables, OrderHeaders and OrderLines.

Is it possible to do one of the following:

write a script which would import the orders from SQLserver and process the orders through Navision

or

Insert the order data directly into the relevant database tables in Navision.

Any advice would be greatly appreciated

DenSter · 2005-05-17

Do not ever create Sales Orders directly into the Navision database. That is a sure way of missing important business logic that runs when users create sales orders in navision.

Other than that there's a few possibilities.

You can look into creating a view in your Navision SQL database into the external database and creating a linked table in navision. You could then write a routine that checks this view and process the data you find in there.

You could also send the sales orders as XML documents into a message queue and have a NAS listen to the queue.

Shenpen · 2005-05-17

If you don't mind buying objects for these two tables, and they are - or can be migrated - to the Navision SQL database, then you could integrate these tables into Navision as described in the "Viewing SQL tables from Navision" topic and then simply write OnInsert code to these tables to create the orders. The important thing is to do it as an exact mimic of what users do on forms:

CLEAR(SalesHeader); //forget RESET
SalesHeader.VALIDATE("Document Type",SalesHeader."Document Type"::Order);
SalesHeader.INSERT(TRUE); // now you got a No. automatically
SalesHeader.VALIDATE("Sell-to Customer No.", OrderHeader.CustomerNo);
SalesHeader.VALIDATE("Shipment Date", OrderHeader.DueDate);
.
.
.
SalesHeader.MODIFY(TRUE);

If you don't want to buy two tables, it is a little bit more tricky, but a very pretty challenge, if you consider yourself a hacker, you will really enjoy it.

First, install Navision Application Server. There is a quite lame official manual for it, but look around in the downloads sections, there are two helpful downloadable documents about it. One of these documents also tell about using Navision Timer OCX - you will need it. Just search downloads for Application Server or NAS.

Now, NAS works the following way: when an external program runs the EXE file, it receives a parameter provided by you, and then NASHandler function in Codeunit 1 checks this parameter and runs some Navision code once - only once - based on it. If you don't use Commerce Portal, User Portal or Commerce Gateway, simply comment all of this function out by { } braces, because otherwise you will have problems - I was too lazy to investigate why, but no need for this code if you don't use the above-mentioned functionality.

Then, add your code to this function, maybe based on the parameter like on the factory code, this code runs a codeunit you make. The codeunit should use Navision Timer OCX and run in an infinite loop, checking a directory, for example C:\IMPORT in every second looking for files there. For example, header files have a h_ and a GUID name, line files have a l_GUID name.
It loads them by FILE functions, and creates the Sales Order like the code above, and then erases the files.

Then, go to you SQL server, and make triggers to create these files when an order is inserted - write code to create files, maybe with a a shell call to bcp or other means. The file name can be generated be NEWID function, which makes GUIDs.

Why are GUID-s important? Because maybe orders arrive more quickly than 1 second. If you would make a new file with the same name before the previous one is deleted, obviously you would get an error message.

If you don't like the two above solutions, it is also a possibility to create SO-s in SQL, but it is usually not recommended. It is "unsupported" (

) and you could get strange results because you have to emulate business logic you maybe don't really know. But you can try it. If you try it, you have to manage the following:

- No. Series
- Reservations, if needed
- Item Tracking, if needed
- Unit Price, Line Discount and Invoice Discount Calculation, if needed
- Date calculations, if needed
- Order Tracking, if needed,
- Unit of Measure conversions, if needed
- anything else I forgot (perhaps not much)

so it is a hard task with many obstacles. I suggest to choose the first or second possibility and resort to it only if you are prepared for heavy swearing

kriki · 2005-05-18

You don't need the Navision Timer OCS in this case, you can also use the SLEEP-function.
You can also create a NAS-setup-record with the value for the SLEEP-function. And also put a boolean in it when the NAS has to stop.

And some NAS code like this:

recNASSetup.get();
if recNASSetup."Stop NAS" then
EXIT;
WHILE TRUE DO BEGIN
ProcessWhatYouHaveToProcess();

recNASSetup.get();
if recNASSetup."Stop NAS" then
EXIT;
SLEEP(recNASSetup.SleepInMilliseconds);
END;

For the rest I agree with the suggestion of Shenpen to use first or second possibility.

Create sales orders from external database

Comments

Categories