Skip to content

Receipt Import

Sage 300 A/R Receipt Import (SAMACCARRCP)

This imports A/R Receipts from a CSV file into Sage 300 as an A/R Receipt batch.

The data decides which receipts are prepayments

The receipts are a mixture of applied receipts and prepayments. The data in the file decides which type each one becomes.

The integration has the same four-step flow as the invoice import:

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

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

Read

A CSV reader pulls the comma-separated data from ARAccountRcpt.csv in G:\IMan\InputData, treating the first row as headings.

Hierarchy

The Hierarchy transform turns the flat file into the three levels a receipt batch needs:

  1. Receipt batch (Receipts)
  2. Receipt header (Receipt)
  3. Receipt details (Allocation)

The Hierarchy transform's Field Mapping tab: Transaction Id to Hierarchise set to Receipts on the left, and on the right a Hierarchy tree showing Receipts marked root, with Receipt indented beneath it and Allocation beneath that

Map

On the Receipts transaction, which is the batch itself, the Map transform adds the fields Sage 300 needs but the file does not carry: Bank, Batch Description, Batch Total, Batch Count and Batch Number. Bank is the fixed value CCB. Batch Description is a formula, "Realisable IMan A/R Batch - " & %Date.

Two more fields are on the Receipt transaction, and both are formulas:

The Map transform's Field Mapping tab with Current Transaction Id set to Receipt: a grid of Reference, Memo, Date, ReceiptType and ReceiptAmount, where ReceiptType and ReceiptAmount have their Evaluate boxes ticked and carry the formulas shown below

  • ReceiptType decides whether the receipt is a prepayment or an applied receipt, from the Memo field: IIf(%Memo = "CASH", 3, 1).
  • ReceiptAmount totals the receipt from its own detail lines: Sum("Amount", "Allocation"). That total then sets the Receipt Total field in Sage 300.

Sum reaches down into a child transaction

Sum reads a child transaction, here the Allocation lines, so that a parent field can be calculated from the rows beneath it. The aggregate functions reference covers Sum and the related functions.

Connector

The Sage 300 connector writes the batch to Sage 300.

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 Receipt Batch, and Update Operation set to Insert

Its Field Mapping tab then matches Receipts, Receipt and Allocation against the three levels of the A/R Receipt Batch import type.