JDBC Driver for Xero

Build 22.0.8462

Timesheets

Query, insert and update Timesheets for a Xero organisation.

Table Specific Information

The Timesheets table allows you to SELECT, INSERT and UPDATE timesheet lines for a Xero organization. The Id column is generated by the driver; the value of this field combines the index of the line item with the unique, Xero-generated TimesheetID.

SELECT

The Xero API allows extensive filtering and ordering. You can define WHERE and ORDER BY clauses in a query to filter and order the results using any non-line-item column.

Note: The Xero API does not support filtering on line items or journal lines.

INSERT

Inserting a single row is straightforward, even with line item tables. Simply set the required fields and a new table object will be created as a single entry. To insert a new timesheet, the EmployeeID, StartDate and EndDate fields are required.

In addition to inserting a single row, line item tables offer two additional ways to insert into a table.

  1. You can insert a new object with multiple line items using XML aggregates. The elements supported here are the same as the Line columns with the Line_ prefix removed, except when providing numbers of units. See the Xero documentation for the Timesheets API endpoint for more details.
    INSERT INTO Timesheets(EmployeeID, StartDate, EndDate, TimesheetLineAggregate) VALUES (
      'XXXXX-XXXXX-XXXXX-XXXXX',
      '2021-01-01',
      '2021-01-07',
      '<TimesheetLine><EarningsRateID>YYYYY-YYYYY-YYYYY-YYYYY</EarningsRateID><NumberOfUnits><NumberOfUnit>8</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>8</NumberOfUnit><NumberOfUnit>6</NumberOfUnit><NumberOfUnit>9</NumberOfUnit></TimesheetLine> <TimesheetLine><EarningsRateID>ZZZZZ-ZZZZZ-ZZZZZ-ZZZZZ</EarningsRateID><NumberOfUnits><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>7</NumberOfUnit><NumberOfUnit>6</NumberOfUnit><NumberOfUnit>9</NumberOfUnit></TimesheetLine>'
    )
  2. You can insert a new line item on an existing object by specifying the Xero-generated identifier of the existing record. For example, to add a line item to an existing timesheet:
    INSERT INTO Timesheets(TimesheetID, Line_EarningsRateID, Line_NumberOfUnits_NumberOfUnit1, Line_NumberOfUnits_NumberOfUnit2, ...) VALUES ('XXXXX-XXXXX-XXXXX-XXXXX', 'YYYYY-YYYYY-YYYYY-YYYYY', 8, 7, ...)

Note: Inserting into existing records will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Update

You can update any field that is not read-only.

Note: Update operations will count as two operations against the Xero API. One is required to retrieve the existing record, and another to update the record with new data.

Delete

The Xero API has limited support for deleting any object. In order to delete objects, you must update their Status to an appropriate value.

Applicable values for this field may be state-dependent. For example, invoices can only have their status set to DELETED when the invoice status is currently DRAFT or SUBMITTED.

Columns

Name Type ReadOnly Filterable Description
Id [KEY] String True

The timesheet line-item combined with the unique Xero identifier of the timesheet.

TimesheetID String True

The unique identifier of the Timesheet. This field is assigned by Xero.

EmployeeID String False

The Xero identifier for an employee.

StartDate Date False

Period start date.

EndDate Date False

Period end date.

Status String False

The status code for the timesheet. The valid values are DRAFT, PROCESSED, and APPROVED.

Hours Double False

Timesheet total hours.

Line_EarningsRateId String False

The Xero identifier for an Earnings Type.

Line_TrackingItemId String False

The Xero identifier for a Tracking Category TrackingOptionID. The TrackingOptionID must belong to the TrackingCategory selected as TimesheetCategories under Payroll Settings.

Line_WorkLocationId String False

The Xero identifier for a Work Location.

Line_NumberOfUnits_NumberOfUnit1 String False

Number of units of a Timesheet line.

Line_NumberOfUnits_NumberOfUnit2 String False

Number of units of a Timesheet line.

Line_NumberOfUnits_NumberOfUnit3 String False

Number of units of a Timesheet line.

Line_NumberOfUnits_NumberOfUnit4 String False

Number of units of a Timesheet line.

Line_NumberOfUnits_NumberOfUnit5 String False

Number of units of a Timesheet line.

Line_NumberOfUnits_NumberOfUnit6 String False

Number of units of a Timesheet line.

Line_NumberOfUnits_NumberOfUnit7 String False

Number of units of a Timesheet line.

Line_UpdatedDateUTC Datetime True

Timestamp of the last change to the Timesheet Line record.

TimesheetLineAggregate String False

Used to define TimesheetLine rows using XML values. Should be provided on INSERT only.

TenantId String False

The ID of the tenant to query instead of the connection tenant

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462