Skip to content

Invoice Import

Sage 300 A/R Invoice Import (SAMACCARINV)

This imports A/R Invoices from an Excel file into Sage 300 as an A/R Invoice batch. The invoices are a mixture of item and summary invoices. The data in the file decides which type each one becomes.

The integration follows a flow that most imports share:

Four nodes joined left to right on the design surface: an Excel reader, a hierarchy, a map, and a Sage 300 connector

  1. Read
  2. Hierarchy
  3. Map
  4. Connector

Read

An Excel reader pulls the data from ARInvoice.xlsx in G:\IMan\InputData, taking the first worksheet and treating the first row as headings.

Hierarchy

Sage 300 expects an invoice batch as a three-level structure, and the spreadsheet is flat. The Hierarchy transform supplies the missing levels:

  1. Invoice batch (Invoices)
  2. Invoice header (Header)
  3. Invoice details (Detail)

You set up the whole transform on its Field Mapping tab. The left side names the transaction to hierarchise. The right side shows the structure the transform produces, one row per level.

The Hierarchy transform's Field Mapping tab: Transaction Id to Hierarchise set to Invoices and an empty New Transaction Id box on the left, and on the right a Hierarchy tree showing Invoices marked root, with Header indented beneath it and Detail beneath that

Re-parenting is locked once downstream transforms exist

The tree is read-only here, and shows the message Re-parenting is locked while downstream transforms exist. The Map and the connector below both use these three transactions, and moving a level would break them without any warning. Add and rearrange levels before you build the rest of the integration.

Map

The Map transform adds the fields Sage 300 needs but the spreadsheet does not carry. Four of them go onto the Invoices transaction, which is the batch itself: Description, Batch Total, Batch Count and Batch Number.

Example

On the Header transaction, the InvoiceType field decides whether each invoice is an item invoice or a summary invoice. It holds a formula, IIf(%Item <> "", 1, 2): an item invoice where the row names an item, and a summary invoice where it does not.

The Map transform's Field Mapping tab with Current Transaction Id set to Header: a grid of Customer, Reference, InvoiceDate, DueDate, SYS.INPUTFILE, InvoiceType and Item, in which only InvoiceType has its Evaluate box ticked and an Evaluate String of IIf(%Item <> "", 1, 2)

IMan evaluates a field with Evaluate ticked as a formula. To refer to another field in the same transaction, a formula uses that field's name prefixed with %.

Connector

The Sage 300 connector writes the result to Sage 300. Its Setup tab sets three things: the Sage 300 company, the Sage 300 import type and whether rows are inserted or updated.

The Sage 300 connector's Setup tab: Sage 300 Connector set to Sage 300 SAMLTD training company, Sage 300 Import Type set to A/R Invoice Batch, and Update Operation set to Insert

Its Field Mapping tab then matches the three transactions the hierarchy built (Invoices, Header and Detail) against the three levels of the A/R Invoice Batch import type, and the fields within each.