Question 1

Update Employee Earnings Rates from CSV File

Accepted Answer

Overview

A CSV file can be used to bulk update Ordinary Earnings Rates for Employees. This is useful for situations where you may be updating the base rates for multiple employees after an annual pay review or similar.

This functionality is currently only available for :

Users with an active subscription
Users in Australia (please contact us if you need this in UK/NZ)

This function only updates the ordinary earnings rate for the employee and does not change any other settings (such as the number of hours or calculation type)

File Format

The file format required can contain any number of columns, but it must contain the following with the header row:

If the file has multiple columns for first_name, last_name they can be easily combined using a formula in Excel such as =CONCAT(A2," ",B2)

Download a sample file

Updating

The process works as follows:

Select the file and click Validate File.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/4gmeAow8Vn/update_er_file.png (update_er_file)

The validation results are displayed. If there are any errors, these records will not be loaded. To resolve them, update the file and repeat step 1.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/yvHKy6mX3G/update_er_validated.png (update_er_validated)

Once all records are validated OK, click Update Earnings Rates to update Xero.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/0JTnVhVFYr/update_er_done.png (update_er_done)

Question 2

Managing Automated Allowances with Smart Formulas

Accepted Answer

Managing Automated Allowances with Smart Formulas

Some awards have allowances that are applied automatically when a file is validated (minimum engagement, laundry allowances etc.)

When Assigning Smart Formulas

By default, any automated allowances are enabled when assigning a smart formula to employees.

To disable all automated allowances so that they are not created when validating a file, turn the Use Automatic Allowances switch off. This will set all such allowances being assigned to manual.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/0lvdTgAhOz/auto_allowance_setting.png (auto_allowance_setting)

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/VYNklaqoTk/auto_allowance_setting_off.png (auto_allowance_setting_off)

After Assigning Smart Formulas

After assigning smart formulas, Automated Allowances can optionally be enabled/disabled for each employee by selecting the award line, clicking Edit and editing Apply Automatically and clicking Update to save the changes.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/bHkCgudvwm/smart_formula_auto_allowance.png (smart_formula_auto_allowance)

Question 3

Calculate Leave Hours Automatically

Accepted Answer

Overview

Enabling this function will let UpSheets calculate leave hours using the employee setup in Xero and the working days in the given period. The hours column becomes optional and if included will override the calculated value.

Setup

To enable this function, select the Leave Hours option in your user settings and save.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/UhoZiF9KHf/leave_hours_setting.png (leave_hours_setting)

Note: In order for this function to work, the Pay Item for Annual Leave must be named Annual Leave. If you have modified this value, please contact us.

Defaulting Leave Hours

When importing a file that includes Leave, the Hours column will display as Hours* and be optional.

If a value is provided in this field for any lines in a file, UpSheets will use this value. For all lines that do not have a value, UpSheets will calculate the hours automatically.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/YNZgfZHkE8/leave_hours_select.png (leave_hours_select)

When the file is validated, the number of hours is calculated automatically.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/kfep1UO5xp/leave_hours_validated.png (leave_hours_validated)

The calculation is made using the employee setup in Xero and uses the number of working days between the date and end_date of the imported line.

Question 4

Exclude Lines from Files Automatically

Accepted Answer

Overview

Some files contain rows of data that are either incomplete or do not need to be loaded for various reasons. UpSheets provides the ability to create mappings to automatically remove these lines when you validate your file.

Methods

There are two ways to create Exclude Mappings:

Create an Exclude Mapping when importing a file
Define an Exclude Mapping from the Mapping Menu

Creating an Exclude Mapping

Select Mapping from the menu:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/4fVPPKVG6L/mapping.png (mapping)

Excluding Empty Values

Checkboxes are available to ignore lines with no value. If selected, any lines that do not have a value will automatically be deleted. This is available for:

Employee Name
Date
Hours
Tracking Code (if enabled in Xero)

The below options would exclude any lines that do not have a value for Employee, Date or Tracking Code.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/8FRXztJMA3/mapping_flags.png (mapping_flags)

Excluding Specific Values

Specific values can also be excluded by:

Select Create Mapping
Select the Mapping type to exclude (Employee, Type, Tracking Code, Project, User)
In the File Value field enter the value in your file to ignore
Select *EXCLUDE* in the** Xero Valu**e field and click Save Mapping

In the below example, UpSheets will exclude any lines where the Employee Name is Jimmy Lebron

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/qZs8PleqEk/mapping_exclude_employee.png (mapping_exclude_employee)

In the below example, UpSheets will exclude any lines that have a Tracking Code of XXXX

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/zspktaMYmW/mapping_exclude_new.png (mapping_exclude_new)

Viewing Excluded Lines

When importing a file that matches an exclude mapping, a message is displayed indicating the number of excluded lines. Hovering over the number will also show a breakdown of which types have been excluded.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/BIjPUXdq25/exclude_links.png (exclude_links)

Removing an Exclude Mapping

To remove an exclude mapping navigate to the Mapping screen and click Delete. Then click Confirm to delete it.

The mapping will not apply to any future files imported. You can also click Reload Data from File from a file that is already validated to process it without any deleted rules - any other edits since importing the file will also be deleted.

Question 5

Using UpSheets to Import Data from Subway / LiveIQ

Accepted Answer

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/iesv2R3Nh3/subway_upsheets_t.png (subway_upsheets_t)

Using Subway Live IQ to Roster Your Staff?

UpSheets Can Upload Your Data to Xero Payroll in Just a Few Clicks

Easy to Setup and Use
Export from Live IQ + Upload to Xero
Predefined Fast Food Industry Award (FFIA)
Assign Awards to Employees
Automatic Calculations + Overtime/Allowances
No Additional Xero Setup
Multiple Locations on a Single Account
Faster, Easier and Lower Cost than Alternatives
Get Rid of Expensive 3rd Party Rostering Software
Save Time and Money
30 Day Free Trial
Excellent Support

Overview

If you are using Subway LiveIQ Labour to roster your staff, your data can be exported and uploaded to Xero using UpSheets in just a few clicks.

This guide explains the steps required to get started:

Register for an UpSheets Account
Configure settings in UpSheets
Assign awards to employees
Extract file from Subway LiveIQ Labour
Validate the file in UpSheets
Upload data to Xero

1. Register for an UpSheets Account

You can register for an UpSheets account at https://app.upsheets.com/register

No credit card is required and it is free to use for 30 days. You can either create an account using your email address, or log in using your Xero account.

After creating your account, you will be asked to connect UpSheets to Xero. This requires a Xero account with Payroll Administrator access and only needs to be done the first time you use an organisation.

We recommend using a free Xero Demo Company to try things out

https://help.upsheets.com/faqs/using-a-xero-demo-company

2. Configure Settings in UpSheets

To import the Subway LiveIQ export file without having to modify it, configure your settings as below:

Enable Smart Formulas

Navigate to your settings https://app.upsheets.com/settings

Select the Enable Smart Formulas and select Save

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/btzqWLq0CC/enable_smart_formula.png (enable_smart_formula)

You will be shown a warning, read it and click I Agree to continue.

Set Default Start Time

A new Start Hour option will now be available. This value is used to calculate start and end times using the single hours column from the export

For example:

Set the Start Hour value for your operation and Save.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/VgH3HcqkUb/startHour.png (startHour)

3. Assign awards to employees

To determine the correct earnings rate and automatic allowances, each employee must have the required smart formula assigned to them.

Please note that this assignment will create a Pay Item in Xero and update the employee pay template and ordinary earnings rate to use it.

Select Smart Formulas from the menu and select Save to accept the default settings:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/47l1coBXeP/smart_formula_settings.png (smart_formula_settings)

Select Smart Formulas from the menu and then select an employee row, then click Assign.

You can then select an award and enter the base rate for the employee. Click Assign to assign the award/rate to the employee.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/SvenOjBr1s/SMART_FORMULA_ASSIGN_FFIA.png (SMART_FORMULA_ASSIGN_FFIA)

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/y4NnKw9Ar1/assigned_ffia_smart_formula.png (assigned_ffia_smart_formula)

Automated allowances (Minimum Engagement, Laundry etc) can be endabled/disabled br following these instructions.

Repeat these steps for all employees you wish to upload data for.

4. Extract File from Subway LiveIQ Labour

The below assumes that you are using Subway Live IQ Labour as your rostering program and have made and published a roster for your stores. If you are unsure or just starting please reach out to your Business Consultant as they have User Guides to help you.

Log into Subway Live IQ Labour
Go into Payroll – Processing
Action your team members' hours as to pay by timesheet or roster etc
Once actioned and no errors showing, click Save then Finalise at the bottom of the page
Return to the Payroll Processing screen and press Process
Then go into Payroll – Breakdown
At the bottom of this page click on Accounting Software Export (CSV)
This is saved into Downloads (or choose where to save it). If you resave this file, ensure it is saved as a CSV file.

If you are unsure how to do any of the above tasks, please reach out to your Business Consultant for help.

5. Validate the file in UpSheets

From the Import screen in UpSheets, select your exported file and the Timesheets option and click Import File:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/AGU4Q0I5rO/subway_import.png (subway_import)

Make sure each column is mapped correctly and that US Date Format is on (tracking code is only required if enabled in Xero). These options will be remembered for future uploads.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/6vG8KT9Iwi/subway_validated.png (subway_validated (1).png)

Click Validate to check the data against Xero before uploading

The validated data is shown. If any employees or tracking codes do not match what is stored in Xero you can click Create Mapping to create an automatic mapping for future uploads. Alternatively, you can select rows and click Edit to update them. After changes, you may need to revalidate your file before uploading.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/XHOCWfthAQ/subway_upload.png (subway_upload)

6. Upload Data to Xero

Once complete simply click Upload to Xero. Any required pay items will be created and the timesheet data will be uploaded to Xero.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/21HOFhMiuT/subway_uploaded_xero.png (subway_uploaded_xero)

Useful Links

For more information on the below topics please refer to the links below:

Assigning Smart Formulas:

https://help.upsheets.com/faqs/smart-formulas-upsheets

Automatically Calculate Start and End Times Using Hours

https://help.upsheets.com/faqs/automatically-calculate-start-and-end-times-using-hours

Mapping values between systems:

https://help.upsheets.com/faqs/create-a-mapping-for-employee-type-or-tracking-code

Editing imported data:

https://help.upsheets.com/faqs/editing-imported-files-before-uploading-to-xero

Defining public holidays:

https://help.upsheets.com/faqs/using-public-holidays-with-smart-formulas

Question 6

File Formats with Multiple Pay Item / Earnings Rate columns

Accepted Answer

Single Pay Item Column

The standard file format for importing data using UpSheets is to have one row per employee/date/type and a single column with the number of hours. The Type column is used to contain the various Pay Items/Earnings Rates that are being imported.

For more information on the standard file formats see the following links:

Payroll File Formats (AU)

Payroll File Formats (NZ/UK)

Projects File Formats (All Countries)

Multiple Pay Item Columns

A common way many systems export data is to have a single row for each day and multiple columns for the Pay Items as shown below:

If your file is in a format similar to this, you can configure UpSheets to translate it into the standard format using the steps below.

Enable Multiple Pay Items

To enable multiple Pay Items, select Pay Items from the menu.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/bxPLDSVlt3/pay_item_menu.png (pay_item_menu)

The select box lists all of the Pay Items available in Xero and selecting them will add them to the list below. To remove a Pay Item, click the minus link (or select Clear All to remove them all).

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/vl0iFqWEL4/assigned_pay_items.png (assigned_pay_items)

Import a file

When importing a file, a new option File contains multiple Earnings Rate columns will now be the default value in the Earnings Rate field.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/h3RnSPMvm7/multi_column_option.png (multi_column_option)

You can select an alternative value if you wish to load a file using one of the other options.

Map Pay Items to Columns

Prior to validating the file, the enabled Pay Items can then be mapped to the required columns. Each Pay Item may only be selected once and at least one of the enabled Pay Items must be selected to proceed.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/jMPFFeVkgp/select_multi_columns.png (select_multi_columns)

Validate the file

When the file is validated, the data will automatically be translated into the required rows and can be edited/uploaded as usual.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/4mlFGzxTAx/validated_multi_column.png (validated_multi_column)

Billing

When using this feature, each of the translated lines will count as one line of usage when added to a subscription.

In the example below, if three columns are selected, three lines will be created and then billed when uploaded.

Question 7

Payroll Calendar not defined for Employee in Xero

Accepted Answer

If you see this error, it is because the setup of your employee in Xero is not complete. This is often the case with the default data provided when using a Xero Demo organisation.

To resolve it, simply navigate Payroll> Employees in Xero. Select the employee, assign a Pay Calendar on the Employment tab and save.

When you revalidate the file the error will be resolved.

Question 8

Automatically Calculate Start and End Times Using Hours

Accepted Answer

Overview

Sometimes your data may be in a format where you only have the total hours worked, but in order to use Smart Formulas, a start_time and end_time are required.

UpSheets allows you to define a default start_time that is used to generate the required start_time and end_time automatically.

For example, if your file has the following three lines:

If you set your default start_time to 06:00am, the following will automatically be generated when validating the file:

Setup

To enable this feature, you must first enable Smart Formulas in your user settings.

The below setting will then be available and you can set the desired start_time:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/VgH3HcqkUb/startHour.png (startHour)

When validating your file, only the hours column is required in your file:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/PoxH9moUra/start_hours_validate.png (start_hours_validate)

The start_time and end_time will be gererated automatically: