Skip to main content
Order Formats - Orders upload - Sage 500
Ruvisha Pillay avatar
Written by Ruvisha Pillay
Updated over 8 months ago

Orders upload - Sage 500

In order to make use of the order import interface from SIA into Sage 500, you need to have purchased and installed the Data Import Manager. This utility from Sage allows for the correct importing of the data into the Sage tables and deals with all the required aspects to validate the data being imported.

One tip that works quite well in setting this up is to ensure that you can export and import data using an excel interface before you attempt to configure the job using the instructions below. This additional check helps ensure that all security aspects and set-up tasks are properly loaded and working properly before you attempt to set this up for SIA imports.

Pre-requisites

In order to set-up the imports for SIA you will need to download the import format for Sage 500 into a folder you can access on the server. The zip file contains:

  • A configuration file

  • A sample file to help in the set-up of the import

  • A standard import job file (XML)

Step 1: set up the connection

Using the files above, set up a connection to the staging tables by selecting Maintain Source Connection from the Data Import Manager and completing the details as per the image below.

Remember to amend the location of the files to what you have used on your machine and remember this should be on the server itself. The source location for the order file should point to the ..\from_netstock folder created by the SIA install on the server. If this folder is not on the server you should map the drive to this server so that it is directly accessible.

Once the details have been entered, click the “Create” button on the main screen to create the XML and XSD files shown in the view (but greyed out).

sage_500_image_1.png

Move onto the “Tables” tab to set up the links between the tables.

In this screen, select the >> button to move both header and detail tables across for preview. Select one at a time and select the “Preview” button to see that the system is seeing the data correctly.

If you are seeing the data similar to this screen, then save and exit.

If there are any issues, please validate that the install of the Data Import Manager is working. Most problems displayed here are due to:

  • user rights

  • login issues

  • services that are not loaded for data import manager itself

sage_500_image_2.png

Step 2: configure the import job

To do this, select Maintain Import Job from the Data Import Manager. To make life a little easier than manually mapping each of the fields and columns, a standard import job has been created (it is in the zip file you downloaded).

After you have saved this link locally, open the Maintain Import Job screen and select the Import icon. A new screen will pop up that needs to be completed as shown.

Remember to amend any references to folders to point to your specific location(s) where data is stored.

sage_500_image_3.png

After the set-up above is completed, it is time to finalise this by creating an import job and saving these settings to allow this to be run easily next time.

You do this in the Process Import Job screen and the resulting set-up should look as shown.

sage_500_image_4.png

NOTE: In the above set-up screen you will need to amend the GL account to be used to be the suspense account. If you do test this import with the sample data you are likely to get errors when loading this as the products and warehouses are unlikely to match what you have configured in your environment.

Step 3: email your settings

Remember to send support.us@netstock.co an email to have the settings amended in SIA so that new orders are exported in the right format for your system, using this import set-up.

Order file format

Header detail

Position. Field Name

Description

1. HeaderLine

RecordTypeID - Character indicating the HeaderLine. Default to an H.

2. PurchaseOrderNo

TranNO - SIA generated unique order reference number. This will have to be set to a series number that will be unique on start of integration.

3. VendorNo

VendorID - Vendor code (not description) of the supplier as displayed in the PO summary.

4. PurchaseOrderDate

OriginationDate - Date the purchase order was created as shown in the PO summary. Use earliest, latest or average of due dates from line detail (MM/DD/YYYY format).

5. PurchaseOrderDate

IssueDate - Date the purchase order was created as shown in the PO summary. Use earliest,latest or average of due dates from line detail (MM/DD/YYYY format).

6. ApprovalStatus

ApprovalStatus - Indicator stating the approval status for the order - defaulted to Submitted.

7. OrderStatus

Status - Status flag indicating if the order is open or closed - default to "Open".

8. Blank

Format required a comma at the end of the header line so leave last field blank on export.

Line detail

Position. Field Name

Description

1. L.LineHeader

RecordTypeID - Character indicating the Line Detail Lines. Default to an L. Use same character “L” for each line detail row.

2. L.Status

Status - Status flag for line on oredr to define if line is open or closed. Default to "Open".

3. L.PurchaseOrderNo

TranNO - SIA generated unique order reference number. This will have to be set to a series number that will be unique on start of integration. This should link to the number used on the header row.

4. L.InvoiceStatusClosed

ClosedForInvc - Yes/No flag to indicate if the line is closed for invoicing or not - Default to "no".

5. L.ReceivingStatusClosed

ClosedForRcvg - Yes/No flag to indicate if the line is closed for receiving or not - Default to "no"

6. L.GLAccount

GLAcctNo - Internal GL account link needed in file layout, but flag on set-up allows this to link to suspense account. Inserted as a blank field.

7. L.ItemCode

ItemID - Product / SKU code

8. L.QuantityOrdered

OrigOrdered - Amended quantity ordered on SIA recommended order multiplied by the Purchase Factor if greater than 0, else multiply by 1 Express in terms of the “PURCHASE Unit of Measure”

9. L.RequiredDate

OrigPromiseDate - Expected Arrival Date of the item on SIA recommended order MM/DD/YYYY format.

10. L.LineNo

POLineNo - Incremental line counter for the lines on this specific PO.

11. L.RequiredDate

PromiseDate - Expected Arrival Date of the item on SIA recommended order MM/DD/YYYY format.

12. L.RequiredDate

RequestedDate - Expected Arrival Date of the item on SIA recommended order MM/DD/YYYY format.

13. L.QuantityOrdered

QtyOrd - Amended quantity ordered on SIA recommended order multiplied by the Purchase Factor if greater than 0, else multiply by 1 Express in terms of the “PURCHASE Unit of Measure”.

14. L.WarehouseCode

ShipToWhseID - Warehouse / branch code (not description)

15. L.UnitCost

UnitCost - Amended unit cost on SIA recommended order multiplied by the Purchase Factor if greater than 0, else multiply by 1.

16. L.UnitOfMeasure

UnitMeasureID - The purchasing unit of measure. Default to SIA unit of measure if not available

17. L.UnitCost * L.QuantityOrdered

ExtAmt - The value of this line on the order. The import will validate quantity * Cost and if there is a variance, fail the import.

18. L.Comment

ExtCmnt - Comment loaded into the PO file for loading as optional comment on order.

19. h.Blank

Format required a comma at the end of the order line so leave last field blank on export.

File layout example

H,4652,ACS,01/01/2013,01/01/2013,Submitted,Open,

L,Open,4652,No,No,,1.44 Floppy,100,04/01/2013,1,04/01/2013,04/02/2013,100,RIA,12.33,Each,1233,Comment about product abc,

Did this answer your question?