SOP Order Import

Sage 200 SOP Order Import (SAMS200IMP)

This is typical of an order import from a web-commerce application. A real web store would usually send XML. The sample uses an Excel spreadsheet to keep it readable.

The integration:

1. Imports data from an Excel spreadsheet
2. Applies transforms to the data
3. Creates or updates sales ledger customers
4. Creates sales orders in Sage 200, with a payment against each order
5. Writes a status file recording the order number Sage 200 returned

The map feeds two connectors. The first creates the customers and the second creates the orders. They run in that order because each connector transform has a Priority: 1 on the customer connector and 2 on the order connector. Sage 200 cannot create a sales order for a customer that does not exist yet.

Read

The read transform points at the file and extracts its data.

Under Options, the reader takes the first worksheet and treats the first row as headings. Mapping Style is By Position, so the reader takes the fields in column order and does not match them on their heading text.

Hierarchy

Sage 200 expects an order as a header with lines beneath it, and the spreadsheet is flat: one row per order line, repeating the order's own details on every row. The Hierarchy transform gives it the structure.

You can repeat the transform to nest more deeply, for example to add shipping details. One level is enough here.

The grid below the tree sets which fields go into which transaction, and how the two transactions are linked:

• Import says which fields belong in the transaction being built. Here the order-level fields are ticked and the line-level ones are not.
• Key says which field identifies a record, and so how the two transactions relate. OrderId is Key 1, so the transform creates one Orders record per order, not one per spreadsheet row. On the child, LineNo takes Key 2.

Keys are the part of this transform to understand

Take the time to understand how keys work. See Hierarchy in the User Guide.

Map

The Map transform applies an expression to each field: a static value or a formula that manipulates the data. Almost every integration has one. Add one even when nothing needs it yet, so later changes have somewhere to go.

The Map adds what Sage 200 needs that the spreadsheet does not carry: the warehouse, the postal name and the four fields that give the order a payment. Sage200OrderNumber is added empty. It gives the connector a field to write back the order number Sage 200 generates.

VBScript

IMan formulas are written in VBScript. IMan adds one thing to the language: you refer to a field of the current transaction by its name with a % in front, as %CustomerName does above.

Two rows are commented out, so the literal runs

Two rows in the screenshot, CustomerCountry and ShipCountry, have a two-line formula, and the grid shows only the first line. That line, 'FuzzyCountryNameLookup(%CustomerCountry, "ENG", "ISO2", True), starts with an apostrophe. The apostrophe makes the whole line a VBScript comment. The second line, the literal "GB", is what runs.

The lookup would normalise whatever the web site sent into an ISO country code, but it needs a populated ISO country table. See FuzzyCountryNameLookup. The sample as installed calls the lookup. The copy in the screenshot has it commented out.

A full VBScript function reference is in the Reference Guide.

Aggregate

The Aggregate transform sits between the map and the order connector. It emits one extra OrderDetail record per order: a comment line whose Description is %OrderComments and whose LineType is 3. This carries the order's free-text comment into Sage 200 as a line on the order.

Connectors

A connector defines how the dataset's fields map to the application. Every connector has the same setup screen.

Setup tab

The Setup tab sets the system to import into, the type of data to import and the update operation. The customer connector:

Insert/Update lets the same integration run again the next day. The connector updates a customer who is already in Sage 200 instead of rejecting it as a duplicate. The order connector is set to Insert, because every run should create a new order:

Field Mapping tab

The Field Mapping tab matches the dataset to the import type chosen on the Setup tab, at two levels. Transaction Level Mapping pairs the transactions the earlier transforms built with the transaction types the import type expects:

Beneath it, a grid maps the individual fields. Each row has an Import tick, the field's name and the Sage 200 field it is written to. A field left as (not mapped) is carried along but not sent. OrderTotal is one, because Sage 200 calculates the total itself.

Running it

Refresh on a connector does not simulate the import. It runs it. When it finishes, the preview shows the dataset as it now stands, including anything Sage 200 wrote back. Scroll right to see DocumentNo:

CSV Write

IMan can write CSV and text files, XML, Excel and ODBC/OleDb databases. This transform exports the result of the import to a CSV file. A step like this tells the source system that a transaction has been processed, by passing back the identifier the target application generated.

The file name is a formula, not a fixed string. Ticking Evaluate FileName makes IMan evaluate it, so each run writes its own dated file. Press Refresh to generate the file in the folder named by File Path:

OrderId,DocumentNo
WEB0234,0000006051
WEB0237,0000006052
WEB0238,0000006053