HR data export: setup guide for the Poplee Core HR export to Sage Payroll

Before getting started

We believe that an HR file management tool needs to take the lead for the payroll tool. The frequency of data updates and use, and the onboarding, warning management, and employee data update campaign features all justify this stance.

For each pay period, the HR administrator needs to make sure that they send up-to-date and useful employee HR data to their payroll manager for the production of payslips.

This help sheet has been created for HR administrators and consultants who want to automate the creation or editing of employee files in Sage i7 using a data export from the Poplee Core HR app.

The aim of this document is to support you through setting up your Lucca environment and your Sage Payroll tool to avoid re-entering data across the two systems.

Prerequisites

To guarantee consistency in the management of employee data between the Lucca apps and your payroll management in Sage Payroll, we recommend using the following principle:

One Lucca establishment should correspond to one payroll establishment (Siret) in Sage Payroll.

Otherwise, please contact the Lucca help desk (https://support.lucca.fr/hc/fr/requests/new) for an impact analysis and to plan a reorganization of your establishments in Lucca.

Payroll number management

The payroll number is the unique ID that lets you identify employees in your payroll system. To facilitate exports of employee data, absences, and payroll variables to your payroll system, you need to save the payroll number in Lucca when you create the employee in Poplee Core HR.

For existing employees, the import module for Lucca users lets you save payroll numbers in each employee's HR file.

For future employees, there is a Poplee Core HR feature that lets you automate the generation of the payroll number when a new employee is created in Lucca, in accordance with the payroll number format required by your payroll software.

It's easy to activate this feature: in the setup for the default values in the HR file, you need to set up the payroll number format required and associate it with establishments. For further details, refer to the help sheet on this topic.

Identifying and setting up data for export

List of data items in the Sage employee file - Lucca employee file:

The table below lists the format and characteristics of each data item in order to create or match the data item with the corresponding format in Poplee Core HR.

Sage data item	Sage format	Data item example	Lucca data item
Payroll number	10 char. max.	00001	employeeNumber
Title	*table	Ms.	civilTitle
Last name	30 char. max.	Alibert	lastName
First name	20 char. max.	Chloe	firstName
Birth name	30 char. max.	Bouvet	Text-type DE to be created
Marital status	*table	Married	List-type DE to be created
Date of birth	10 char. max.	19/02/1975	birthDate
Birth municipality	26 char. max.	Paris	Text-type DE to be created
Birth region	2 char.	75	Text- or list-type DE to be created
Birth country code	2 char.	FR	Text- or list-type DE to be created
Nationality (code)	3 char.	FRA	nationality
Social security number	13 char. (without key)	2750275119047	insuranceNumber
Visa - Number	10 char. max.	1234567890	Text-type DE to be created
Visa - Expiry date	10 char. max.	12/10/2027	Date-type DE to be created
Visa - Issued by	20 char. max.	Pref. of Evry	Text-type DE to be created
Address	32 char. max.	13, rue Martin Bernard	Address
Address line 2	32 char. max.	Build. A	Text-type DE to be created
Zip code	5 char.	75013	Text-type DE to be created
Municipality	26 char. max.	Paris	Text-type DE to be created
Country code	2 char.	FR	Text- or list-type DE to be created
Bank name	30 char. max.	Crédit Agricole	bankName
BIC code	11 char. max.	BOUSFRPPXXX	bic
IBAN code	34 char.	FR7640618802…	iban
Payment method	*table	Bank transfer	List-type DE to be created
Contract type	*table	Permanent contract	List-type DE to be created
Contract start date	10 char. max.	01/09/2014	dtContractStart
Trial period end date	10 char. max.	30/11/2014	Date-type DE to be created
Contract end date	10 char. max.	31/12/2020	dtContractEnd
Contract ref. no.	20 char. max.	CA-2014/53	Text-type DE to be created
Seniority date	10 char. max.	01/03/2014	seniorityDate
Establishment entry type	*table	Recruitment	List-type DE to be created
Employee's establishment	5 char. max.	Lucca Nantes	legalEntity
Collective bargaining agreement code	*table	Syntec	List-type DE to be created
Employee's department	*table	Sales	department
Qualification	15 char. max.	Sales Engineer	jobTitle
Level	4 char. max.	2.2	Text-type DE to be created
Coefficient	10 char. max.	280	Text-type DE to be created
Basic schedule in hours	12 char. max.	151.67	Text-type DE to be created
Working time mode	*table	Full-time	List-type DE to be created

* Some data items, like "Marital status", refer to a table of predefined values. In Poplee Core HR, this data needs to be set up with a "list of values" format which includes the values referenced by Sage (the list of predefined tables can be found at the end of this document).

This table is not exhaustive; it lists the standard data shared across all payroll files. You have the option of adding to this table by creating customized data items in Poplee Core HR, like the free zones attached to the employee file in Sage.

We recommend creating a dedicated section in Poplee Core HR that groups together all of the data to be exported, which will simplify the settings to be adjusted for the export format.

Setup of an HR data export to the Sage Payroll format (xlsx)

In the "Export" menu of the "Employees" module (Employees > Export)

I - Click on "New configuration" and select the output format "Generic csv format"

II - Enter the general settings of your export format:

1. Name your export configuration. 

2. Match the corresponding establishment with your establishment in Sage Payroll

If your Lucca instance hosts multiple establishments that you want to export, it's recommended that you create one export configuration for each establishment.

3. Define the population concerned by the export using the occupation category criterion

4. Name the export file that will be generated. Do not use spaces in the file name.

5. Select the required date format: dd/MM/yyyy or dd/MM/yy

III - Select the data to be exported from the data catalog in your HR file

Click on the Add data item button; a drop-down list allows you to select an entire section with all the data it contains or a single piece of data from the HR file.

The first column must correspond to the Payroll number data item.

This icon lets you customize the layout of the data items selected using a drag-and-drop function.

IV - Access correspondence tables and advanced formatting 

If the lists of values of list-type data items saved in Poplee Core HR do not comply with the values expected by Sage, click on the data item for formatting options:

1. The Correspondence Table tab allows you to view data values for the employees within the scope of your export.

In the event of a correspondence that hasn't been entered, you can set up the action that will be performed during the export:

If there is no correspondence set up, by default, it will be the value of the data item, as it is stored in your Lucca database , which will be generated in the export file.

• Block the export: on each occurrence, the data item must match a correspondence value. If a correspondence is missing for one of the data items to be exported, a warning pauses the processes and asks you to complete the missing correspondence.

• Use a default value: this lets you attribute a value that will be assigned by default to all occurrences of the list of values that are not associated with a correspondence for the configured data item.

2. The Formatting & Advanced options tab

If needed, you can customize the formatting of the value of each data item exported in the export file generated.

The length (1) of the value of the data item that will be generated in the file, its framing (2), and the completion characters (3) can be set up.

If the value exported in the file exceeds the size defined in the Sage import format, only this portion of the imported data item will be processed; the rest will be cut off.

The advanced option Export code (4) lets you customize the name of the header of the column generated in the first line of your file. By default, this header is populated with the code assigned by Lucca for each data item as it is stored in the database.

Setting up a CSV import format in Sage Payroll

To import the Excel file obtained from the Poplee Core HR export configuration, you need to create an AM import format in Sage Payroll.

In the AM/Import format menu, select the Import format item.

1. Add an import format using the icon "+"
2. Select the Employee file item in the "Root file/Document" window and click on OK.

3. In the Advanced management - defining a list window, select the employee file tab in which the data item to be processed during import is located. The catalog of fields available is sorted by employee file tab.

You need to select the payroll number field for the first position. 

4. Select the payroll number data item located in the Civil status item.

5. Keep the settings for the field and click on OK.

Continue to follow this process, repeating steps 3 through 5 to add all of the fields configured in the Poplee Core HR export file, identically reproducing the column order.

6. Once you've finished configuring your list of data item fields to be imported, close the Advanced Management - Defining a list window and save your import template, giving it a name.

Lucca export - Sage import procedure

I. Generate the export for edited data from the Export menu or the Employees module.

1. Click on the box corresponding to the format set up for Sage and set the date from which you want to export the edited HR data.

This date will be automatically calculated with future exports to only show the most recent edits made since the date of the last export.

2. Check the details of the data most recently edited; a preview will let you see:

• The payroll number, first name, and last name of the employee concerned;
• The name of the data field that has been edited;
• The new value for the data item;
• The date on which the data item was edited.

3. Click on Generate export files

The files generated can be accessed in the history section, in the form of a download link: 

• An Excel turn-around file. This is a summary of all data edited since the last export in the Lucca format.
• The file to download in the Sage format, taking into account any formatting set up (correspondences and data item formats).

You can automate the scheduling of the export generation and its upload to an FTP server; refer to the help sheet on the topic Automating HR data exports.

II - Import the employee export file from Poplee Core HR into Sage Payroll

In the Lists > Employee files menu, right-click in the List of employees window and select Import.

1. Click on the Excel spreadsheet import type

2. Select the Customized format type

3. Select the customized format previously created from the list

4. Select the import mode that enables employee file editing and click on OK.

5. Select the Excel file to import and print the import report to PDF.

Appendix

To import information from a drop-down list into Sage, the value that you need to enter in the Excel sheet is the index for this information. Generally, the first item in the drop-down list has the
index 0. For example, for the Title, Sage requires the value 0 for Mr. or 1 for Ms.

Below is the list of Sage reference tables for list-type data item fields:

Please note that these tables can be added to or customized by the payroll manager.

Table - Title:

value	Code for import
Mr.	0
Ms.	1
Miss	2

Table - Marital status:

Value	Code for import
Single	0
Married	1
Widower/widow	2
Divorced	3
Separated	4
Cohabitating	5
Civil union	6
Unknown	7

Table - Payment method:

Value	Code for import
Bank transfer	0
Check	1
Cash	2

Table - Contract type:

Value	Code for import
Permanent contract	Permanent contract
Fixed-term contract	Fixed-term contract
Fixed-term work-study contract	PROFC
Permanent work-study contract	PROFC1
Apprenticeship contract	APC10
Seasonal contract	SC
Elected representative mandate	MANDATE
Internship agreement	NC01
No contract or agreement	NC

Table - Establishment entry type:

Value	Code for import
Recruitment	RE
Transfer	TR
Mandate start date	MS

Table - Working time mode: 

Value	Code for import
Full-time	10
Part-time	20
Part-time medical leave	21
Alternate time (aeronautics)	30
Employee not concerned	90

Table - Collective bargaining agreement code: table created by payroll manager

Table - Employee's department: table created by payroll manager