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).
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
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.
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.
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,