Skip to content

Canadian Payroll Import

Sage 300 Canadian Payroll Import (SAMACCCPTIME)

This imports a simple weekly timesheet from Excel into Canadian Payroll.

A column per day in, a record per day out

The spreadsheet has a column for each day of the week: Monday, Tuesday and so on. Canadian Payroll needs a record per day. This sample shows how the Hierarchy, Map and Aggregate transforms together turn one shape into the other.

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

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

Read

An Excel reader pulls the data from CPTimeCard.xlsx in G:\IMan\InputData.

Hierarchy

The Hierarchy transform gives the flat rows two levels: the timecard header and the detail beneath it. At this point the detail still has a column per day.

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

Map

On the Timecard transaction the Map adds one field, Timecard, whose value is a counter:

The Map transform's Field Mapping tab with Current Transaction Id set to Timecard: a grid of ID, Employee, PeriodEnd and Timecard, where Timecard alone has its Evaluate box ticked and an Evaluate String of GetCounterSequence("S300CPTIME")

GetCounterSequence("S300CPTIME")

GetCounterSequence calls the S300CPTIME counter. The counter is defined under Setup > Counters, and the samples setup creates it. Each call returns the next number in the sequence, so every timecard gets its own id.

Without the counter, the spreadsheet would need its own unique timecard number on every row. Counters are not only for timecards. The same function supplies document numbers, customer numbers and any other id that must be unique and sequential.

On the TimecardDetail transaction the Map adds the four fields the Aggregate needs to turn a column per day into a record per day. EarningType and Rate are static values, SALARY and 10. Hours and Date are empty here, and the Aggregate fills them in.

The Map transform's Field Mapping tab with Current Transaction Id set to TimecardDetail: a grid of ID, Employee, the five weekday columns, then Hours, EarningType with the value SALARY, Rate with the value 10, PeriodEnd and Date

Aggregate

The Aggregate transform turns the columns into records. Its Field Mapping tab holds a list of Calc Records for the selected transaction. Each calc record emits a record. There are five here, one per weekday.

The Aggregate transform's Field Mapping tab with Current Transaction Id set to TimecardDetail: Delete Child Records ticked, a Calc Records drop-down showing Monday with add and remove buttons, a Description of Monday, a Record Inclusion Condition of True, and a field grid in which Hours evaluates %Monday and Date evaluates CDate(%PeriodEnd) - 4

One screen holds everything about a calc record: its description, the condition under which it is emitted and the value of every field it produces. For Monday:

  • Hours is %Monday, so the record takes that day's column.
  • Date is CDate(%PeriodEnd) - 4. The period ends on the Friday, and Monday is four days before it.

Tuesday is the same record with %Tuesday and - 3, and so on to Friday, which subtracts nothing. Select a different day from the Calc Records drop-down to show that record on the screen.

Delete Child Records is ticked, so the incoming rows are consumed

Delete Child Records is ticked. The transform consumes the incoming TimecardDetail rows, which still hold five columns, and passes only the five records it emits on to the connector.

Connector

The Sage 300 connector creates the timecards with the C/P Payroll Timecard import type.