Skip to content

Step 7 – Sage 300 Order Import

This is the second Sage 300 connector in the integration. It takes the filtered orders and writes them into Sage 300 as O/E Orders, header and lines together.

It reuses the System Connector defined in step 5. Any number of connectors can choose the same System Connector by name.

What a connector does to the dataset

Connectors perform destructive mapping

Connectors perform destructive mapping.

  1. Only mapped fields flow out of a connector. A field you do not map is not part of the dataset downstream, and you cannot refer to it after the connector. Log Keys get round this, and 8 – Auditing shows how.
  2. A connector renames the transaction ids and the fields to the Sage 300 view and field names. After this transform the transaction is OE0520, not Orders, and ShipCity is SHPCITY. That is why step 8's audit summaries use %OE0520.ORDNUMBER and not the earlier names.

For the full description see Push Connector > Field Mapping in the IMan User Guide.

Design > Transform Setup

  1. Open the Connectors group in the palette and drag a Sage 300 connector onto the design surface.

  2. Drag a Connector from the Transforms group and join the Filter transform to it.

    The design surface with the completed integration: Excel Reader, Hierarchise and Map in the top row feeding the customer import, and Aggregate, Filter and the new Sage 300 order connector in the second

Connector > Setup

  1. Double-click the new connector to open it.

    1. Transform Id
      • Identifies the transform in the audit report, in its Process Id column.
      • This is the second connector in the integration, so it arrives named Connector_1. Two connectors called Connector and Connector_1 tell you nothing when you read a report, so rename it.
      • For training, enter: OrderImport
    2. Sage 300 Connector
      • The System Connector defined in step 5, listed by its description.
      • For training, choose: Sage 300 SAMLTD training company
    3. Sage 300 Import Type
      • What you are importing. The list holds 92 types.
      • For training, choose: O/E Order
    4. Update Operation
      • For training, leave as: Insert

    The connector's Setup tab with Transform Id set to OrderImport, the Sage 300 SAMLTD training company connector selected, the import type set to O/E Order and Update Operation on Insert

Every run of this connector writes a new set of orders

Every run of this connector writes a new set of orders into Sage 300. That includes every press of Refresh while you are building it. The customer import in step 5 has a lookup to recognise a record it has already sent. This connector has none, so a second run gives you a second copy.

Connector > Field Mapping — the order header

The incoming data is hierarchical: Orders with OrderDetails beneath it. You map each transaction to a Sage 300 view, and then map its fields. Start with the header.

  1. Press the Field Mapping tab.
  2. Check that Orders is selected in Current Transaction Id.

  3. Choose Orders (OE0520) from Sage 300 Transaction Type.

    The Field Mapping tab with Orders selected as the current transaction id and the Sage 300 transaction type set to Orders (OE0520)

  4. Press Edit above the grid, then set the Sage 300 Field on each of these rows:

    Incoming Field Sage 300 Field
    OrderId Order Reference (REFERENCE)
    ShipName Ship-To Name (SHPNAME)
    ShipAddress1 Ship-To Address Line 1 (SHPADDR1)
    ShipAddress2 Ship-To Address Line 2 (SHPADDR2)
    ShipCity Ship-To City (SHPCITY)
    ShipCounty Ship-To State/Province (SHPSTATE)
    ShipCountry Ship-To Country (SHPCOUNTRY)
    DateTime Order Date (ORDDATE)
    CustomerComments Order Comment (COMMENT)
    CustomerNo Customer Number (CUSTOMER)
    Sage300OrderNumber Order Number (ORDNUMBER)
    • The name in brackets is the Sage 300 view field that receives the value. Match on that name, not on the label. The list offers an Optional Field - Order Date (ORDERDATE) a few rows from Order Date (ORDDATE), and they are different fields.
    • Sage300OrderNumber is the empty field created in 6.1. When you map it to Order Number, Sage 300 returns the number it allocates into the dataset.
    • You do not need to tick Import. Choosing a Sage 300 field ticks the row for you.

  5. Press Save above the grid.

    The order header field grid after saving, with Import ticked and a Sage 300 field set on the mapped rows and (not mapped) on the rest

Connector > Field Mapping — the order lines

  1. Change Current Transaction Id to OrderDetails.

  2. Choose Order Details (OE0500) from Sage 300 Transaction Type.

    • It is the only type offered. Because the parent maps to OE0520, the list offers only the views that can sit beneath it.
    • Transaction Level Mapping now shows both levels mapped. This confirms the shape is right before you map any fields.

    The Field Mapping tab with OrderDetails selected, its transaction type set to Order Details (OE0500), and the Transaction Level Mapping panel showing Orders mapped to OE0520 and OrderDetails to OE0500

    The two drop-downs do different jobs

    The two drop-downs do different jobs. The top one, Current Transaction Id, chooses which transaction's grid you are editing. The bottom one, Sage 300 Transaction Type, sets what that transaction is mapped to. If you change the bottom one to see a different grid, you re-map the transaction.

  3. Press Edit above the grid, then set the Sage 300 Field on each of these rows:

    Incoming Field Sage 300 Field
    Qty Quantity Ordered (QTYORDERED)
    SkuCode Item (ITEM)
    Description Description (DESC)
    UnitPrice Pricing Unit Price (PRIUNTPRC)
    LineType Line Type (LINETYPE)
    ExtendedPrice Extended Amount (EXTINVMISC)
    MiscChargeId Miscellaneous Charges Code (MISCCHARGE)
    • 6.1 created the last three fields and 6.2 filled them in on the charge lines. LineType tells Sage 300 whether a line is an item or a miscellaneous charge. The other two apply only to a charge line.

  4. Press Save above the grid.

    The order detail field grid after saving, with the seven mapped rows and OrderId and LineNo left unmapped

  5. Press Close at the bottom of the transform, then Save the integration.

Running the import

  1. Re-open the connector and press Refresh at the bottom of the pane.
  2. IMan writes the orders into Sage 300. The preview reads Progress - Completed and shows the orders as Sage 300 received them: under the Sage 300 field names, and headed Transaction Id: OE0520, not Orders.

  3. Scroll the preview to the right. ORDNUMBER carries the order number Sage 300 allocated to each order.

    The connector preview reading Progress - Completed, headed Transaction Id OE0520, with the three orders and the ORDNUMBER Sage 300 allocated to each

    • This is why 6.1 created the empty Sage300OrderNumber field. The number does not exist until Sage 300 writes the order, and it comes back in the field mapped to it.
    • ORDNUMBER is wider than its default column. Drag the column edge to read the whole value.

The orders are now in Sage 300, and step 9 writes these numbers back out to a file.

Your order and customer numbers will not match the screenshots

The order numbers, and the customer numbers beside them, will not match the ones in these screenshots. Both are allocated in sequence as records are created, so they depend on the records already in the company.

Step 8: Auditing >