Skip to content

1. SOP Despatch

The order import created orders. In this section you despatch them, so that 13.2 can invoice them.

Sage 200 generates two of the numbers involved: the order line id and the despatch number. You capture a generated value the same way each time. You have already done it once, with Sage200OrderNo in Step 4, which comes back out of the connector holding the order number Sage 200 assigned.

Map > Field Mapping

The order lines need to carry the line id Sage 200 assigns them, so that Sage 200 can match the despatch to the right line. They also need to ask Sage 200 to allocate stock, because a line with no allocation cannot be despatched.

  1. Re-open the integration and press the Transform Setup tab.
  2. Open the Map transform and press Field Mapping.
  3. Set Current Transaction Id to OrderDetails.

  4. Press Add and fill the dialog:

    1. New Field Name
      • For training, enter: Sage200LineId
    2. Type
      • For training, select: Text
    3. Evaluate
      • For training: unticked.
    4. Evaluate String
      • Leave empty. The field is blank on purpose. It gives the connector somewhere to put the value Sage 200 generates.

    The Field Mapping dialog creating a new field called Sage200LineId, of type Text, with Evaluate unticked and no formula

  5. Press the green tick, then Add again for the second field:

    1. New Field Name
      • For training, enter: AllocationQty
    2. Type
      • For training, select: Decimal
    3. Evaluate
      • For training: ticked.
    4. Evaluate String
      • %Qty

  6. Press the green tick. Both fields appear at the end of the grid.

    The OrderDetails field grid with Sage200LineId added as a Text field with no formula, and AllocationQty as a Decimal with Evaluate ticked and the formula %Qty

  7. Press Refresh, then Close to commit the transform, and save the integration.

Allocate the whole quantity, or the despatch despatches less

AllocationQty is %Qty because you allocate the whole quantity ordered. If you allocate less, the despatch despatches less.

Sage 200 Order Connector > Field Mapping

  1. Open the OrderImport connector and press Field Mapping.
  2. Set Current Transaction Id to OrderDetails.

  3. Press Edit and map the two new rows:

    Incoming Field Map To Sage 200 Extra Field
    Sage200LineId SOPOrder Return Line
    AllocationQty To Allocate Quantity

  4. Press Save on the grid.

    The order connector's OrderDetails mapping with Sage200LineId mapped to SOPOrder Return Line and AllocationQty to To Allocate Quantity, both with their Import boxes ticked

Choosing a Sage 200 Extra Field ticks that row's Import box for you. Import makes the field flow out of the connector to the transforms downstream.

The connector renames fields to their Sage 200 names on the way out

The connector renames fields to their Sage 200 names on the way out, as Step 7 described. Downstream of this connector the transactions are SOPOrder and SOPOrderLine, and the field you just created appears as SOPOrderReturnLine, not Sage200LineId. The name you chose appears only here.

That is why you need the mapping. Without it, SOPOrderReturnLine is not in the connector's output, and the despatch has nothing to match its lines against.

Press Refresh to re-generate the orders. Scroll the preview to its right-hand end and expand an order. Both fields have come back filled in: a Sage 200 line id against each line, and the quantity it allocated:

The order line preview showing SOPOrderReturnLine populated with a Sage 200 line id for every line, and ToAllocateQuantity matching each line's quantity

Press Close and save.

Without To Allocate Quantity, Sage 200 allocates on its own

Without To Allocate Quantity mapped, Sage 200 allocates what it can on its own and reports a warning for anything it cannot:

StockItemHoldingInsufficientException
20180 - The specified quantity exceeds the stock available at the location.

The job still completes and Sage 200 still creates the orders, so you might not notice for several runs. But an unallocated line cannot be despatched, and the despatch below would skip it without reporting an error.

If you see that warning, the warehouse named in Step 6.1 does not hold enough stock. Check the free stock for the items in the workbook before you suspect the mapping.

Design > Transform Setup

You need two more fields: one to receive the despatch number, and one to tell Sage 200 to despatch. They go in a Map transform of their own, connected to the order connector.

  1. Open the Transforms group in the palette and drag a Map onto the design surface, below the order connector.
  2. Drag a Connector from the same group and join the order connector to it.
  3. Save the integration. You cannot open a newly dropped transform until you do.

The order connector now feeds two branches: the CSV writer built in step 9, and this one.

Post Order Map > Field Mapping

Double click the new transform to open it. On its Setup tab set Transform Id to POSTORDERMAP. A new Map is named Map_1, and the audit report shows whatever id you give it.

Press Field Mapping and leave Current Transaction Id on SOPOrder, the order header. Press Add twice:

  1. Name
    • DespatchNumber
  2. Type
    • Text
  3. Evaluate
    • Unticked
  4. Evaluate String
    • Empty
  1. Name
    • DoDespatches
  2. Type
    • Boolean
  3. Evaluate
    • Ticked
  4. Evaluate String
    • True

The POSTORDERMAP field grid on transaction SOPOrder, showing the fourteen inherited fields followed by DespatchNumber and DoDespatches

DespatchNumber is empty for the same reason as Sage200LineId. DoDespatches carries a value. It tells Sage 200 to despatch the order, instead of recording the despatch as unconfirmed.

Press Refresh. The two new columns appear at the end of the preview, the first empty and the second True.

The POSTORDERMAP preview scrolled to its last columns, DespatchNumber empty and DoDespatches True on all three orders

Press Close and save the integration.

Filter the non-stock lines

The orders carry three kinds of line: items, charges and comments, set up in 6.1 and 6.2. Only items can be despatched. If a charge line reaches the despatch, it has no stock item to despatch and the run fails.

  1. Drag a Filter from the Transforms group onto the surface, to the right of POSTORDERMAP, join them with a Connector, and save.
  2. Open it. On its Setup tab, set Transform Id to STOCKLINES. This is the second Filter in the integration, so it is named Filter_1, as 6.3 said it would be.
  3. Press Field Mapping and set Current Transaction Id to SOPOrderLine.

  4. Enter the Record Evaluation formula:

    %SOPLineType = 0

    The Filter's Field Mapping tab with SOPOrderLine selected, the formula %SOPLineType = 0 entered, and the syntax check reporting No Errors Found

  5. Press Refresh and expand the first order. It keeps its two item lines and loses the Carriage charge and the comment line:

    The expanded order line grid after filtering, showing two item lines with SOPLineType 0 and a row count of two

  6. Press Close and save.

Why the filter goes here

The formula uses SOPLineType, not LineType, because this filter is downstream of the connector and the field carries its Sage 200 name. The three values are the ones 6.1 set: 0 for an item, 2 for a charge and 3 for a comment.

The filter goes here, not earlier, because the charges and comments must reach the order connector: they belong on the order. They must not reach the despatch.

Sage 200 Despatch Connector > Setup

  1. Open the Connectors group in the palette and drag a Sage 200 connector onto the surface, to the right of the Filter.
  2. Join the Filter to it with a Connector, and save.
  3. Double click it to open it.
  1. Transform Id
    • For training, enter: DESPATCH
  2. Sage 200 Extra Connector
    • The Sage 200 company to write to. Choose this first. Until it is set, the Import Type list below is empty.
    • For training, select: Sage200 Demo Data
  3. Sage 200 Extra Import Type
    • For training, select: SOP Despatch
  4. Update Operation
    • For training, leave as: Insert. It is the only option this import type offers.

The despatch connector's Setup tab with Transform Id DESPATCH, the Sage200 Demo Data connection, Sage 200 Extra Import Type SOP Despatch and Update Operation Insert

Set the import type before you build the mapping

Set the import type before you build the mapping. Once the connector is configured the field is disabled, because changing the type would invalidate every mapping underneath it. If you pick the wrong one, delete the transform and start it again.

Sage 200 Despatch Connector > Field Mapping

The connector receives two transactions from upstream: SOPOrder, the order header, and SOPOrderLine, the order lines. Map each one to the Sage 200 transaction it becomes.

  1. Press Field Mapping. Current Transaction Id is on SOPOrder.

  2. Set Sage 200 Extra Transaction Type to SOPDespatchReceiptAdjustment.

    The Transaction Level Mapping panel underneath summarises what is mapped to what. Check it as you go. It should read SOPOrder → SOPDespatchReceiptAdjustment, SOPOrderLine → Not mapped.

  3. Press Edit and map the order header fields:

    Incoming Field Map To Sage 200 Extra Field
    DocumentNo Order Return No
    DespatchNumber Despatch Receipt Number
    DoDespatches Do Despatches

  4. Press Save on the grid.

    The despatch header mapping with DocumentNo mapped to Order Return No, DespatchNumber to Despatch Receipt Number and DoDespatches to Do Despatches

  5. Change Current Transaction Id to SOPOrderLine, then set Sage 200 Extra Transaction Type to SOPDespatchReceiptItem.

The two drop-downs do different jobs

To change which grid you are looking at, use the top drop-down, Current Transaction Id. The bottom one, Sage 200 Extra Transaction Type, changes what the top one is mapped to.

  1. Press Edit and map the order line fields:

    Incoming Field Map To Sage 200 Extra Field
    LineQuantity Quantity
    SOPOrderReturnLine SOPOrder Return Line

  2. Press Save on the grid.

    The despatch line mapping with LineQuantity mapped to Quantity and SOPOrderReturnLine mapped to SOPOrder Return Line

This mapping ties a despatch line to its order line

SOPOrderReturnLine → SOPOrder Return Line ties each despatch line to the order line it fulfils. You created Sage200LineId for it at the start of this section. Without this mapping, the despatch has a quantity but no line to put it against.

Running it

Press Refresh. Sage 200 creates the despatches, and the number it generated for each one comes back onto the preview:

The despatch connector's preview on transaction SOPDespatchReceiptAdjustment, showing three orders each with a generated despatch receipt number and DoDespatches True

Press Close and save the integration.

Every Refresh of this connector despatches again

Every Refresh of this connector despatches again. A connector's Refresh is not a preview: it writes to Sage 200. A Refresh also replays a cached copy of the dataset from the transform above, so this connector receives the same orders it has already despatched.

Refresh from the Excel Read downwards, as Step 9 describes. A run that starts at the reader creates a fresh set of orders, and every transform after it works on those.

Preparing to generate the invoice

The next section prints each invoice to PDF and posts it. It needs five fields that the dataset does not yet carry.

  1. Add another Map to the right of the despatch connector, join it, and save.
  2. Open it, and on its Setup tab set Transform Id to INVOICEPREP.
  3. Press Field Mapping, leave Current Transaction Id on SOPDespatchReceiptAdjustment, and press Add five times:
  1. Name
    • DocumentType
  2. Type
    • Integer
  3. Evaluate
    • Unticked
  4. Evaluate String
    • 0, which means an invoice, not a credit.
  1. Name
    • ExportPath
  2. Type
    • Text
  3. Evaluate
    • Ticked
  4. Evaluate String
    • "C:\IMan\OutputData\" & %DespatchReceiptNumber & ".pdf"
  1. Name
    • InvoiceNo
  2. Type
    • Text
  3. Evaluate
    • Unticked
  4. Evaluate String
    • Empty. 13.2 fills it in, in the same way as Sage200LineId.
  1. Name
    • PostInvoice
  2. Type
    • Boolean
  3. Evaluate
    • Unticked
  4. Evaluate String
    • True
  1. Name
    • Layout
  2. Type
    • Text
  3. Evaluate
    • Ticked
  4. Evaluate String
    • "Layouts\SOP Invoice (Single).layout"

The INVOICEPREP field grid on SOPDespatchReceiptAdjustment, showing the three inherited despatch fields followed by DocumentType, ExportPath, InvoiceNo, PostInvoice and Layout

The file is named after the despatch number, not the invoice

Replace the first part of ExportPath with your own IMan shared data directory if it differs. C:\IMan\OutputData is the folder step 9 wrote the order status file to.

The file is named after the despatch number, not the invoice number. The invoice does not exist yet, because 13.2 creates it, so its number cannot be part of a path that 13.2 receives as an input. There is one despatch per invoice here, so the despatch number identifies the file just as well.

Press Refresh. Because ExportPath is evaluated, each row now carries the full path its invoice will be written to:

The INVOICEPREP preview showing ExportPath evaluated to a full path ending in the despatch number and .pdf, PostInvoice True and the invoice layout on every row

Press Close and save the integration. The branch now looks like this:

The completed branch below the order connector: POSTORDERMAP, the STOCKLINES filter, the Sage 200 despatch connector and INVOICEPREP, running left to right

Step 13.2: OE Invoice >