Question 1

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 Value 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.

If removed 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 2

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 3

How do Earnings Rates / Types / Pay Items work in UpSheets?

Accepted Answer

UpSheets uses the Pay Items set up in Xero when uploading data so that Xero can determine the correct rate to use. This is generally contained in the Type field of your upload file.

If the Type in your file matches the name in Xero exactly, (e.g. 'Ordinary Hours', 'Overtime', etc) it will pass validation automatically. If not, a validation error will be given and you are given the option to edit it or create a mapping which will then automatically map the values going forward.

The following options may be used when importing a file into UpSheets:

Include the Type in your file. This requires a column to be present in your file containing the Type (it is recommended this column is named type so it is identified autmatically). If used, every line must have a valid value in this column.
Use the default Earnings Rate set for each Employee. This does not require a type column to be present in the file, but it does require all Employees to have a default Earnings Rate set for them in Xero.
Select a default Earnings Rate. This does not require a type column to be present in the file and will apply the selected Earnings Rate to all records. This does not require an Earnings Rate column to be present in the file or set up against your employees.
Use Smart Formulas Smart Formulas allow you to set rules to determine which Types should be used when. When used, a Type field is not required in your file, but rules must first be set up and start/end times are required - learn more.

Question 4

Does UpSheets support Tracking Codes?

Accepted Answer

Yes - like Xero, UpSheets uses Tracking Codes to categorise transactions in Xero and can optionally be used for Timesheets.

In order to use Tracking Codes in UpSheets, your organisation must be set up to use them in Xero (for instructions see here).

Note: The Tracking Code must always match the value in Xero, or a validation error will be given, (e.g. 'Northern Region', 'Eastside', etc).

There are two options you may use when importing a file into UpSheets:

Include the Tracking Codes in your file. This requires a column to be present in your file containing Tracking Codes (it is recommended this column is named tracking_code). Every timesheet line must have a valid value in this column.
Select a default Tracking Code. This will apply your selected Tracking Code to all records in the file and does not require a Tracking Code column to be present in the file.

Please Note: As in Xero, tracking codes only work with timesheets, not when uploading Payslip Adjustments

Question 5

How do I enable Tracking Codes in UpSheets and Xero?

Accepted Answer

In order to use Tracking Codes in UpSheets, they must first be enabled in Xero, as follows:

Select (from top left) Org > Settings > Payroll Settings

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

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

Under Payroll Tracking, select the Tracking Code you wish to use in the Timesheet Categories field and save.

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

You must disconnect Xero from Upsheets and reconnect for the change to take effect.

You will then be able to select Tracking Codes in UpSheets.