SOP Order Import

Sample Sage200 SOP Order Import (SAMS200IMP)

These sample integrations are very typical of an order import from a web-commerce application. In a real scenario the data-source would be XML, however to simplify the integration we have substituted this for an Excel data-source.

The order import integration sample for Sage200:

  1. Import data from an Excel spreadsheet
  2. Apply transforms to the data
  3. Create/update sales ledger customers
  4. Create sales orders in the relevant application.

This integration will create a corresponding Payment against Order.

Read Transform

The read transform is directed to the relevant file and here extracts data from the specified Excel spreadsheet.

Hierarchy Transform

To import data into Sage200 the data must be in a structure that corresponds to the type of data the Sage application expects.

Due to the nature of data within Sales / Orders, a hierarchical structure is required here.

The hierarchy transform can apply hierarchical structure to a data set. The transform can then be used to repeat this structure to create deeply nested children. Deeper nesting can be useful when adding fields such as shipping details.

For this sample integration, however, a structure 1 level deep is enough.

  1. To see the effect of the hierarchical transform:
    1. Press the Refresh button on the Field Mapping tab.
    2. Click '+' to expand the record.

  1. To view the hierarchical transform setup for the selected transaction type (in this case, 'Orders'):
    1. Press the 'Edit' button (pencil).

  1. In the screen shot below:
    • The import check box indicates which fields are to be included in the resultant 'Orders' transaction type.
    • The Key column is used to specify the field(s) that will be used to define the relationship between the transaction types.

Example

The 'Order Id' field uniquely identifies each 'Orders' transaction.

  1. Press either the green tick or the red cross to return to the previous screen.
  2. Then, to view the child transaction type setup:
    1. Change the drop-down from 'Orders' to 'OrderDetails'
    2. Press Edit.

  1. This is where the Key field becomes important. To get the correct structure, make sure:
    1. The first page of fields has only the ‘Order Id’ import box selected, with Key column set to 1.
    2. The second page has only the detail fields selected, with the ‘LineNo’ Key value set to 2.

Refer to the IMan User Guide for more on Keys.

Map Transform

The Map transform allows you to apply an expression to each field. The expression can be either a static value, or a formula. A formula will manipulate the value of the selected field.

Due to the Map transform's flexibility, almost every job has one.

We recommend that a Map transform is inserted into every integration even if it is not initially required, as it will make future changes and maintenance much easier.

VBScript

VBScript is the underlying language for formulae within IMan.

IMan augments VBscript by allowing fields from the dataset to be referenced, prefixing the field name with a percentage ‘%’ sign to indicate the field reference, as shown below.

Example

The function uses the FuzzyCountryNameLookup function, normalising the CountryName field to make it consistent when it is imported.

  1. To view the results of Map transform press the 'Refresh' button on the Field Mapping tab.
  2. On refresh, there should be several new fields added to the data set.
A full VBScript function reference is available in Appendix A of the in the IMan User Guide.

Connector Transforms

The connector transform defines how the dataset fields are mapped into the application.

Each of the connector transforms use a common setup screen.

Options Tab

The Options tab defines:

  • The System to import into.
  • The type of data that is being imported.
  • How updates are made.

Field Mapping Tab

The Field Mapping tab defines:

  • How the transactions and fields are mapped to the ‘Import Type’ selected on the Options tab.
  1. To map the fields:
    1. Press the Edit button.
    2. Select the field from the field drop-down.

For the ‘Orders’ connector (as opposed to the 'customer' connector):

  • The Transaction Id dropdown is matched against the Transaction type of the Import Type.

Example

In the screenshot below, the ‘OrderDetails’ transaction id is matched against the ‘SOP Sales Order Line’ transaction type of the Sales Order Import type.

  1. Pressing Refresh will import the transactions into Sage200.
  2. When the import is complete:
    • Scroll the preview area to the right and you will find the DocumentNo field populated with Sage200’s auto-generated value.

CSV Write Transform

IMan has facility to write to a number of data formats including CSV/Text, Xml, Excel & ODBC/OleDb databases.

This transform exports the results of the order import to a CSV file.

This type of operation is often required to update the 'data source' application with a flag or identifier (such as an auto-generated Order number or entry reference) to indicate that the transaction has been successfully processed.

Example

  1. Press Refresh to generate the file.
    • The file will be created in the Folder selected through the File Path field in the File Options: