| Table of Contents | ||||
|---|---|---|---|---|
|
This article explains how to use Import Spreadsheet routine as a powerful tool that allows to add various payroll related information to the batch of employees.
...
Primary Worksheet Name – is a free input text field where user can define the name of the source tab from excel template (.xls / .xlsx) to be used for import.
Batch Start / Batch End – numeric fields where identify first & last rows containing import information within the excel file (.xls / .xlsx). Only the records in this range will be processed. The row with headers should not be included.
Primary Column – here you define the letter of the column from the excel file which will be used as the primary key value. Usually it is a column with PIN.
The field is mandatory for input. If there is no value in this column, the transaction is stopped even if the Batch End is not reached.
Add / Delete buttons – are used to define the set of transactions that will be performed during import. Usually only one transaction is defined per each import.
Transaction nr - Integer value identifying the transaction number. The import utility will import transaction by transaction, the transaction number is used to group all the field assignments for one transaction.
Table Name - predefined list of database tables that can be used for import.
In total Import Spreadsheet contains 3 database tables:
- Table PW001PAY (Payroll Entries in current and future periods)
- Table PW001PAI (Payroll Entry Additional Info on the Entry Code).
- Table PW001P0Y (Person's Payroll information - mostly taken from Constant page in Payroll window).Info 1) PW001PAY and PW001P0Y tables can not be imported at the same time.
For each of them you need to make a separate setup each time you make the import.
If you make import only for one of those tables, then there is no need to delete the setup. You can re-use it the next time when you do the import.2) To import PW001PAI table you must import PW001PAY table as well. PW001PAI can not be imported by itself.
In order to add the link between PAI information and PAY entries you have to import PW001PAY.REF field with "Y" value.
Columns from both tables should be placed on the same worksheet. PAI information should be places in the same line as PAY Entry Code for which you import the Additional Payment Info.
Update if record exist - enables the input to the column "Key Fields" in the Details screen.
If this option is set, the system needs to know what are the key fields identifying the transaction that need to be updated.
...