QuickBooks Time Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.9039 of the connector.

Get Started

TSheets Version Support

The connector leverages the TSheets API to enable bidirectional access to TSheets.

Establish a Connection

Connect to TSheets

TSheets supports the OAuth standard for user authentication. To enable this authentication from all OAuth flows, you must create a custom OAuth application, and set AuthScheme to OAuth.

The following subsections describe how to authenticate to TSheets from three common authentication flows:

• Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping.
• Web: access to data via a shared website.
• Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard.

For information about how to create a custom OAuth application, see Creating a Custom OAuth Application.

For a complete list of connection string properties available in TSheets, see Connection.

Desktop Applications

To authenticate with the credentials for a custom OAuth application, you must get and refresh the OAuth access token. After you do that, you are ready to connect.

Get and refresh the OAuth access token:

• InitiateOAuth: GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
• OAuthClientId: The client ID assigned when you registered your application.
• OAuthClientSecret: The client secret that was assigned when you registered your application.
• CallbackURL: The redirect URI that was defined when you registered your application.

When you connect, the connector opens TSheets's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector completes the OAuth process:

1. The connector obtains an access token from TSheets and uses it to request data.
2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the connector automatically refresh the OAuth access token:

1. Before connecting to data for the first time, set these connection parameters:
   • InitiateOAuth: REFRESH.
   • OAuthClientId: The client ID in your application settings.
   • OAuthClientSecret: The client secret in your application settings.
   • OAuthAccessToken: The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: The path where you want the connector to save the OAuth values, which persist across connections.
2. On subsequent data connections, set:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAuth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set these connection properties:
   • OAuthClientId: The Client ID in your application settings.
   • OAuthClientSecret: The Client Secret in your application settings.
3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Create a Custom OAuth Application

Create a Custom OAuth Application

Creating a custom OAuth application enables you to generate OAuth credentials that are passed during user authentication.

Procedure

1. Log in to your TSheets Account.

2. At the left navigation pane, select Feature Add-ons > Manage Add-ons.

3. Select API Add-On.

4. Click Install.

5. Select Add a new application at bottom left. If a followup window does not display, return to the left

   navigation pane's Feature Add-ons area and select API.

6. Enter a short name and a meaningful description for the new application.

7. Set the Redirect URL to http://localhost:33333 (the connector's default) OR set a different port of your choice.
   Record this value; you will supply it as the CallbackURL.

8. Click Save. The admin tool generates the OAuth Client ID and Client Secret.
   Record these values for future use.

9. (Optional:) If you want to generate your access token now and connect with InitiateOAuth set to OFF, click Add Token at the bottom left portion of the display. This generates a token for immediate use.

You can access the custom OAuth application (referred to as the API Add-On) from the left navigation pane's Feature Add-ons section.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the TSheets connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to TSheets and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

The Jitterbit Connector for TSheets allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Timesheets WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to TSheets APIs.

Key Features

• The connector models TSheets entities like documents, folders, and groups as relational views, allowing you to write SQL to query TSheets data.
• Stored procedures allow you to execute operations to TSheets
• Live connectivity to these objects means any changes to your TSheets account are immediately reflected when using the connector.

Views

Views describes the available views. Views are statically defined to model Timesheets, Users, Files, and more.

Tables

The connector models the data in TSheets as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for TSheets Tables

CustomFieldItemFilters

Create, Update and Query the Custom Field Item Filters in TSheets.

Table Specific Information

Select

Query the CustomFieldItemFilters table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• Active supports the '='
• JobCodeId supports the '='
• UserId supports the '='
• LastModified supports the '<,<=,>,>='
• GroupId supports the '='
• IncludeUserGroup supports the '='
• IncludeJobCodeFilters supports the '='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM CustomFieldItemFilters
SELECT * FROM CustomFieldItemFilters WHERE ID = 11531340

Insert

Insert can be executed by specifying CustomFieldId, CustomFieldItemId, Active, AppliesTo, and AppliesToId column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO CustomFieldItemFilters (CustomFieldId, Name, ShortCode) VALUES (1140610, 15027812, true, 'jobcodes', 73209298)

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE CustomFieldItemFilters SET Active = true WHERE ID = '15011650'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomFieldItems

Create, Update and Query the Custom Field Items in TSheets.

Table Specific Information

Select

Query the CustomFieldItems table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• CustomFieldId supports the '=,IN'
• Name supports the '=,IN'
• Active supports the '='
• LastModified supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM CustomFieldItems WHERE CustomFieldId=1140610
SELECT * FROM CustomFieldItems WHERE CustomFieldId=1140610 AND ID = 11531340

Insert

Insert can be executed by specifying CustomFieldId, Name and ShortCode column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO CustomFieldItems (CustomFieldId, Name, ShortCode) VALUES (1140610, 'testField', 'cf')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE CustomFieldItems SET Name = 'yogesh_customfield', ShortCode='ycf' WHERE ID = '15011650'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomFields

Create, Update and Query the Custom Fields in TSheets.

Table Specific Information

Select

Query the CustomFields table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• Active supports the '='
• AppliesTo supports the '='
• Type supports the '='
• LastModified supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM CustomFields
SELECT * FROM CustomFields WHERE ID = 24145

Insert

Insert can be executed by specifying Name, Required, Active, AppliesTo, Type, ShowToAll and ShortCode column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO CustomFields (Name, Required, Active, AppliesTo, Type, ShowToAll, ShortCode) VALUES ('Test', true, true, 'timesheet', 'managed-list', true, 'cstm')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE CustomFields SET Name = 'Updated name' WHERE ID = '1140410'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

EstimateItems

Create, Update and Query the Estimate Item in TSheets.

Table Specific Information

Select

Query the EstimateItems table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• EstimateId supports the '=,IN'
• Active supports the '='
• LastModified supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM EstimateItems
SELECT * FROM EstimateItems WHERE ID = '11531340'

Insert

Insert can be executed by specifying EstimateId, Type and TypeId column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO EstimateItems (EstimateId, Type, TypeId) VALUES (47482, 'tag_clouds', 15011650)

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE EstimateItems SET EstimatedSeconds = 2 WHERE ID = '11531340'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Estimates

Create, Update and Query the Estimates in TSheets.

Table Specific Information

Select

Query the Estimates table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• Active supports the '='
• LastModified supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM Estimates
SELECT * FROM Estimates WHERE ID = '11531340'

Insert

Insert can be executed by specifying ProjectId and EstimatedBy column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Estimates (ProjectId, EstimatedBy) VALUES (2064876, 'none')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Estimates SET Active = true WHERE ID = '11531340'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Files

This table contains a list of file objects.

Table Specific Information

Select

Query the Files table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id and Name fields support the '=' and IN operators.
• ActiveStatus supports the = operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Files WHERE Name IN ('IMG_20181004_214839.png', 'healthy.jpg')

SELECT * FROM Files WHERE LastModified = '2019-01-01 15:30' AND ActiveStatus = 'both'

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Files SET FileDescription = 'test' WHERE ID = '3688644'

Delete

Delete can be executed by specifying the ID in the WHERE Clause. For example:

Delete FROM Files WHERE ID = '3688644'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GeoLocations

Retrieves a list of all geofence configs.

Table Specific Information

Select

Query the GeoLocations table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id, UserId and GroupId fields support the '=' and IN operators.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following query is processed server side:

SELECT * FROM GeoLocations WHERE GroupId IN ('29474', '29474') AND LastModified <= '2020-01-01 00:00'

Insert

Insert can be executed by specifying the Created, UserId, Accuracy, Altitude, Latitude and Longitude columns. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table.

INSERT INTO Geolocations (Created, UserId, Accuracy, Altitude, Latitude, Longitude) VALUES ('2018-08-19T11:30:09-06:00', '1242515', '20.375', '0', '43.68662580', '-116.35166460')

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Groups

This table contains a list of all groups associated with your company.

Table Specific Information

Select

Query the Groups table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id and Name fields support the '=' and IN operators.
• ActiveStatus supports the = operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Groups WHERE Name IN ('Group 1', 'Group 2') AND LastModified > '2019-01-01 15:30'

SELECT * FROM Groups WHERE ActiveStatus = 'both'

Insert

Insert can be executed by specifying the Name column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Groups (Name) VALUES ('Group 1')
INSERT INTO Groups (Name, ManagerIds) VALUES ('Group 1', '300, 316')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Groups SET Name = 'Group 1a', Active = 'false'  WHERE ID = '10055'

Columns

JobcodeAssignments

Returns a list of all jobcode assignments associated with users.

Table Specific Information

Select

Query the JobcodeAssignments table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id and UserId fields support the '=' and IN operators.
• JobcodeId, JobCodeType, JobCodeParentId fields and ActiveStatus filter all support the '=' operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM JobcodeAssignments WHERE UserId IN (1242515, 1242515)

SELECT * FROM JobcodeAssignments WHERE JobCodeParentId = 17285791

Insert

Insert can be executed by specifying the UserId and JobcodeId columns. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO JobcodeAssignments (UserId, JobcodeId) VALUES ('1234', '39')

Delete

Delete can be executed by specifying the ID in the WHERE Clause. For example:

DELETE FROM JobcodeAssignments WHERE Id = '41321421'

DELETE FROM JobcodeAssignments WHERE ID IN ('41321421', '41321435')

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

JobCodes

This table contains a list of jobcodes associated with your company

Table Specific Information

Select

Query the Jobcodes table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id and Name fields support the '=' and IN operators.
• Type field and ActiveStatus filter support the '=' operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Jobcodes WHERE Id IN (1242515, 1242515)

SELECT * FROM Jobcodes WHERE Type = 'unpaid_break'

Insert

Insert can be executed by specifying the Name and Type columns. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Jobcodes (Name, Type) VALUES ('Group 1', 'pto')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Jobcodes SET Name = 'Group 2', Type = 'pto'  WHERE ID = '10055'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Locations

Retrieves a list of all locations associated with your company.

Table Specific Information

Select

Query the Locations table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id and GeoCodingStatus fields support the '=' and IN operators.
• ActiveStatus supports the = operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Locations WHERE GeoCodingStatus IN ('in_progress', 'retry')

SELECT * FROM Locations WHERE ByJobcodeAssignment = true

Insert

Insert can be executed by specifying any columns. Following is an example of how to insert into this table

INSERT INTO Locations (Address1, City, State, Country) VALUES (' office', 'Bangalore', 'KA', 'IN')

INSERT INTO Locations (Address1, City, State, Country, Zip) VALUES (' office', 'Bangalore', 'KA', 'IN', '560100')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Locations SET Address1 = 'Microsoft' WHERE ID = '10055'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Notifications

Retrieves a list of notifications associated with your company.

Table Specific Information

Select

Query the Notifications table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id field supports the '=' and IN operators.
• MessageTrackingId and UserId fields support the '=' operator.
• DeliveryTime field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Notifications WHERE Id IN (94140223, 94140225) AND UserId = 37

SELECT * FROM Notifications WHERE MessageTrackingId = 'baabeb0ab03d62ce'

SELECT * FROM Notifications WHERE DeliveryTime = '2019-12-25 19:00'

Insert

Insert can be executed by specifying the Message column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Notifications (Message) VALUES ('Please clock Out!')

INSERT INTO Notifications (Message, UserId) VALUES ('Please clock Out!', '56329')

Delete

Delete can be executed by specifying the ID in the WHERE Clause. For example:

DELETE FROM Notifications WHERE Id = '41321421'

DELETE FROM Notifications WHERE ID IN ('41321421', '4132567')

Columns

ProjectActivityReplies

Create, Update and Query the Project Activity Replies in TSheets.

Table Specific Information

Select

Query the ProjectActivityReplies table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• UserId supports the '=,IN'
• ProjectActivityId supports the '='
• Active supports the '='
• LastModified supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM ProjectActivityReplies WHERE ProjectActivityId = 8348378
SELECT * FROM ProjectActivityReplies WHERE ProjectActivityId = 8348378 AND ID = 2062894

Insert

Insert can be executed by specifying ProjectActivityId and Note column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO ProjectActivityReplies (ProjectActivityId, Note) VALUES (8348382, 'Test')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE ProjectActivityReplies SET Note = 'updated' WHERE ID = '2062894'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProjectNotes

Create, Update and Query the Project Notes in TSheets.

Table Specific Information

Select

Query the ProjectNotes table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• UserId supports the '=,IN'
• ProjectId supports the '='
• Active supports the '='
• LastModified supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM ProjectNotes
SELECT * FROM ProjectNotes WHERE ProjectId = 203282

Insert

Insert can be executed by specifying ProjectId and Note column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO ProjectNotes (ProjectId, Note) VALUES (203282, 'Test note'))

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE ProjectNotes SET Note = 'Updated value' WHERE ID = '1675236'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Projects

Create, Update and Query the Projects in TSheets.

Table Specific Information

Select

Query the Projects table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• JobCodeId supports the '=,IN'
• ParentJobCodeId supports the '='
• Name supports the '='
• Active supports the '='
• ByJobCodeAssignment supports the '='

For example, the following queries are processed server side:

SELECT * FROM Projects
SELECT * FROM Projects WHERE ID = 2056676

Insert

Insert can be executed by specifying Name column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Projects (Name, Description) VALUES ('ProjectTest', 'This is a test project')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Projects SET Status = 'complete' WHERE ID = '2062894'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Reminders

Retrieves a list of reminders associated with your employees or company.

Table Specific Information

Select

Query the Reminders table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id and UserId fields support the '=' and IN operators.
• ReminderType field and ActiveStatus filter support the '=' operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Reminders WHERE Id IN (72595, 72599) AND UserId = 37

SELECT * FROM Reminders WHERE ReminderType = 'clock-in'

Insert

Insert can be executed by specifying UserId, ReminderType, DueTime, DueDaysOfWeek, DistributionMethods, Active and Enabled columns. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Reminders (UserId, ReminderType, DueTime, DueDaysOfWeek, DistributionMethods, Active, Enabled) VALUES ('37', 'clock-in', '08:10:00', 'Mon, Tue, Sat', 'Push', 'true', 'true')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Reminders SET DueTime = '08:10:00', DistributionMethods = 'Email'  WHERE ID = '10055'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	Id of the reminder.
UserId	Integer	True	User ID for the user that this reminder pertains to. 0 indicates that this is a company-wide reminder.
ReminderType	String	True	The type of this reminder object. Supported reminder_types are 'clock-in' and 'clock-out'.
DueTime	String	False	The 24-hour time that the reminder should be sent, expressed as 'hhss'.
DueDaysOfWeek	String	False	A comma-separated list of the days of the week when the reminder should be sent. The value can be any combination of 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri' and 'Sat'.
DistributionMethods	String	False	The message text of the notification. The maximum message length is 2000 characters.
Active	Boolean	False	If true, this reminder is active and will be evaluated at the 'due_time' and 'due_days_of_week'. If false, this reminder is inactive and will not be evaluated. If active=false for user-specific reminders, then the company-wide reminder of the same reminder type will apply.
Enabled	Boolean	False	If true, the reminder is enabled and will be sent at the 'due_time' and 'due_days_of_week'. If false, the reminder is disabled and will not be sent. A user with an active (active = true), but disabled (enabled = false) reminder will not receive that reminder type regardless of how company-wide reminders are configured.
Created	Datetime	True	Date/time when this reminder was created
LastModified	Datetime	True	Date/time when this reminder was last modified,

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ScheduleEvents

Retrieves a list of schedule events associated with your employees or company.

Table Specific Information

Select

Query the ScheduleEvents table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id and ScheduleCalendarId fields support the '=' and IN operators.
• Start supports the >= operator.
• End supports the <= operator.
• ActiveStatus filter, and Draft and TeamEvents fields support the '=' operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM ScheduleEvents WHERE TeamEvents = 'instance'

SELECT * FROM ScheduleEvents WHERE LastModified > '2019-01-01 18:30' AND Id IN (1, 2, 3)

SELECT * FROM ScheduleEvents WHERE End <= '2019-12-31 18:00'

Insert

Insert can be executed by specifying ScheduleCalendarId, Start, End and AllDay columns. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO ScheduleEvents (ScheduleCalendarId, Start, End, AllDay) VALUES ('37', '2018-12-05T16:00:00+00:00', '2020-12-05T16:00:00+00:00', 'true')

INSERT INTO ScheduleEvents (ScheduleCalendarId, Start, End, AllDay, AssignedUserIds) VALUES ('37', '2018-12-05T16:00:00+00:00', '2020-12-05T16:00:00+00:00', 'true', '11, 1365, 729')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE ScheduleEvents SET Title = 'New Title'  WHERE ID = '14055'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

TimeOffRequestEntries

Create, Update and Query the Time off Request Entries in TSheets.

Table Specific Information

Select

Query the TimeOffRequestEntries table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• TimeOffRequestId supports the '=,IN'
• Status supports the '=,IN'
• ApproverUserId supports the '=,IN'
• UserId supports the '=,IN'
• JobcodeId supports the '=,IN'
• Date supports the '='
• StartTime supports the '='
• EndTime supports the '='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM TimeOffRequestEntries
SELECT * FROM TimeOffRequestEntries WHERE ID = 11531340

Insert

Insert can be executed by specifying TimeOffRequestId, EntryMethod, Duration, JobcodeId and Date column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO TimeOffRequestEntries (TimeOffRequestId, EntryMethod, Duration, JobcodeId, Date) VALUES (12345, 'manual', 3600, 10984254, '2024-05-29')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE TimeOffRequestEntries SET Duration = 500 WHERE ID = '15011650'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

TimeOffRequests

Create, Update and Query the Time off Requests in TSheets.

Table Specific Information

Select

Query the TimeOffRequests table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• UserId supports the '=,IN'
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM TimeOffRequests
SELECT * FROM TimeOffRequests WHERE ID = 11531340

Insert

Insert can be executed by specifying TimeOffRequestNotes, and TimeOffRequestEntries column. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO TimeOffRequests (TimeOffRequestNotes, TimeOffRequestEntries) VALUES ('[{"note":"Taking a four day weekend to go on vacation."}]', '[{"date":"2024-05-29","start_time":"2024-01-17T00:00:00-06:00","end_time":"2024-04-17T00:00:00-06:00","entry_method":"regular","duration":28800,"jobcode_id":10984254}]')

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE TimeOffRequests SET TimeOffRequestNotes = '[{"note":"Taking a four day weekend to go on vacation."}]' WHERE ID = '15011650'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Timesheets

This table contains a list of file objects.

Table Specific Information

Select

Query the Timesheets table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id, UserId, JobcodeId, PayrollIds, GroupIds fields support the '=' and IN operators.
• OnTheClock and JobCodeType fields support the '=' operator.
• LastModified and Date fields support the <=,<=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Timesheets WHERE OnTheClock = false

SELECT * FROM Timesheets WHERE LastModified > '2019-01-01 18:30' AND GroupIds IN (1, 2, 3)

SELECT * FROM Timesheets WHERE JobcodeId IN (1, 2, 3)

Insert

Insert can be executed by specifying UserId, JobcodeId, Type and StartTime, EndTime columns in case of Type = regular or UserId, JobcodeId, Type and Duration, Date in case of Type = manual. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Timesheets (UserId, JobcodeId, Type, StartTime, EndTime) VALUES ('1242123', '17288283', 'regular', '2019-12-05', '2019-12-05')

INSERT INTO Timesheets (UserId, JobcodeId, Type, Date, Duration) VALUES ('1242123', '17288283', 'manual', '2019-12-05', 600)

Update

Update can be executed by specifying the ID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Timesheets SET Date = '2018-08-08', EndTime = '2018-08-08T14:00:00-07:00'  WHERE ID = '1242123'

Delete

Delete can be executed by specifying the ID in the WHERE Clause. For example:

DELETE FROM Timesheets WHERE ID = '41321421'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Users

Retrieves a list of all users associated with your company.

Table Specific Information

Select

Query the Users table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id, GroupId, Username, PayrollId and EmployeeNumber fields support the '=' and IN operators.
• FirstName and LastName fields support the '=' and LIKE operators.
• ActiveStatus supports the = operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM Users WHERE ActiveStatus = 'both'

SELECT * FROM Users WHERE LastModified > '2019-01-01 18:30' AND GroupId IN (1, 2, 3)

SELECT * FROM Users WHERE FirstName LIKE 'josh%' AND PayrollId IN ('562348', '45457')

Insert

Insert can be executed by specifying UserName, FirstName and LastName columns. The columns that are not required can be inserted optionally. Following is an example of how to insert into this table

INSERT INTO Users (UserName, FirstName, LastName) VALUES ('cdatagroup@123', 'cdata', 'group')

Update

Update can be executed by specifying the ID or UserName in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Users SET FirstName = 'New Name'  WHERE Id = '14055'

UPDATE Users SET FirstName = 'New User Name', LastName = 'New Title' WHERE UserName = 'cdatagroup@123' 

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Jitterbit Connector for TSheets Views

CurrentTotalsReport

Report for the current totals (shift and day) along with additional information provided for those who are currently on the clock.

Table Specific Information

Select

Query the CurrentTotalsReport table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• GroupId, UserId and OnTheClock fields support the '=' and IN operators.

For example, the following query is processed server side:

SELECT * FROM CurrentTotalsReport

SELECT * FROM CurrentTotalsReport WHERE GroupId IN ('29474', '29474') AND OnTheClock IN (true, false)

SELECT * FROM CurrentTotalsReport WHERE UserId IN (1751136, 1738864) AND OnTheClock = false

Columns

CurrentUser

Query the current user details in TSheets.

Table Specific Information

Select

Query the CurrentUser table. Here, the WHERE clause conditions are not supported on the API side, only a simple SELECT query works on the API side. The filters are executed client side within the connector.

For example, the following query is processed server side:

SELECT * FROM CurrentUser

Columns

CustomFieldItemJobCodeFilters

Query the Custom Field Item JobCode Filters in TSheets.

Table Specific Information

Select

Query the CustomFieldItemJobCodeFilters table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• JobCodeId supports the '=,IN'
• LastModified supports the '<,<=,>,>='

For example, the following queries are processed server side:

SELECT * FROM CustomFieldItemJobCodeFilters
SELECT * FROM CustomFieldItemJobCodeFilters WHERE JobCodeId = '679721'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CustomFieldItemUserFilters

Query the Custom Field Item User Filters in TSheets.

Table Specific Information

Select

Query the CustomFieldItemUserFilters table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• UserId supports the '='
• GroupId supports the '='
• IncludeUserGroup supports the '='
• LastModified supports the '<,<=,>,>='

For example, the following queries are processed server side:

SELECT * FROM CustomFieldItemUserFilters
SELECT * FROM CustomFieldItemUserFilters WHERE JobCodeId = '679721'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

GeneralSettings

Retrieves a list of all effective settings associated with a single user.

Table Specific Information

Select

Query the GeneralSettings table. All the filters are executed client side within the connector.

Columns

GeofenceConfigs

Retrieves a list of all geofence configs.

Table Specific Information

Select

Query the GeofenceConfigs table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id, TypeId and Type fields support the '=' and IN operators.
• Enabled field and ActiveStatus filter support the '=' operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM GeofenceConfigs WHERE TypeId IN (282316, 323445) AND Type = 'Locations'

SELECT * FROM GeofenceConfigs WHERE Enabled = false

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

LastModifiedTimestamps

Retrieves a list of last_modified timestamps associated with each requested API endpoint.

Table Specific Information

Select

Query a list of last modified timestamps associated with each requested API endpoint.

For example, the following queries are processed server side:

SELECT * FROM LastModifiedTimestamps WHERE endpoints = 'timesheets,current_user,groups'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

LocationMaps

Retrieves a list of all locations maps associated with your company.

Table Specific Information

Select

Query the LocationMaps table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id field supports the '=' and IN operators.
• ByJobcodeAssignment supports the = operator.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM LocationMaps WHERE Id IN ('102839', '110761')

SELECT * FROM LocationMaps WHERE ByJobcodeAssignment = true

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ManagedClients

Retrieves a list of managed clients available from your account.

Table Specific Information

Select

Query the ManagedClients table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• ActiveStatus supports the = operator.

For example, the following queries are processed server side:

SELECT * FROM ManagedClients WHERE ActiveStatus = 'both'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PayrollReport

Payroll Report associated with a timeframe.

Table Specific Information

Select

Query the PayrollReport table to retrieve payrolls associated with a timeframe, with filters to narrow down the results. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. By default the report will include results for a time range within the last 30 days. You can filter by StartDate and EndDate filters to get a payroll report over a different time range.

• UserId and GroupId fields support the '=' and IN operators.
• StartDate, EndDate and IncludeZeroTime fields support the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PayrollReport WHERE GroupId IN ('29474', '29475') AND IncludeZeroTime = false
SELECT * FROM PayrollReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PayrollReportByJobCode

Payroll Report broken down by jobcode

Table Specific Information

Select

Query the PayrollReportByJobCode table to retrieve a payroll report, broken down by jobcode, with filters to narrow down the results. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. By default the report will include results for a time range within the last 30 days. You can filter by StartDate and EndDate filters to get a payroll report over a different time range. The date range defined by StartDate and EndDate must not exceed 31 days.

• GroupId field supports the '=' and IN operators.
• StartDate, EndDate and IncludeAdvancedOvertime fields support the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PayrollReportByJobCode WHERE GroupId IN ('29474', '29474') AND IncludeAdvancedOvertime = false
SELECT * FROM PayrollReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'

Based on the columns selected in projection explicitly, the aggregated data displayed may change. Aggregate payroll metrics broken down by jobcode id:

SELECT * FROM PayrollReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'

Aggregate payroll metrics broken down by jobcode ID per each user:

SELECT UserId, JobCodeId, TotalWorkSeconds, TotalReSeconds, TotalPtoSeconds, OvertimeSeconds FROM PayrollReportByJobCode

Aggregate payroll metrics broken down by jobcode ID and date per each user:

SELECT Date, UserId, JobCodeId, TotalWorkSeconds, TotalReSeconds, TotalPtoSeconds, OvertimeSeconds FROM PayrollReportByJobCode

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProjectActivities

Query the Project Activities in TSheets.

Table Specific Information

Select

Query the ProjectActivities table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• ProjectId supports the '='
• ActivityType supports the '='
• LastModified supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM ProjectActivities WHERE ProjectId = 2056676
SELECT * FROM ProjectActivities WHERE ProjectId = 2056676 AND ID = 8375474

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProjectActivityReadTimes

Update and Query the ProjectActivity read time in TSheets.

Table Specific Information

Select

Query the ProjectActivityReadTimes table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• LatestReadTime supports the '<,<=,>,>='
• SupplementalData supports the '='

For example, the following queries are processed server side:

SELECT * FROM ProjectActivityReadTimes
SELECT * FROM ProjectActivities WHERE ID = 8375474

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProjectEstimateDetailReport

Retrieves a project estimate detail report.

Table Specific Information

Select

Query the ProjectEstimateDetailReport table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '='
• SupplementalData supports the '='
• ResultsAsArray supports the '='

For example, the following queries are processed server side:

SELECT * FROM ProjectEstimateDetailReport
SELECT * FROM ProjectEstimateDetailReport WHERE ID = 8375474

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProjectEstimateReport

Retrieves a project estimate report.

Table Specific Information

Select

Query the ProjectEstimateReport table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• JobcodeId supports the '=,IN'
• ProjectId supports the '=,IN'
• ParentJobcodeId supports the '='
• Status supports the '='
• Active supports the '='
• SupplementalData supports the '='
• ResultsAsArray supports the '='
• HasTimeTracked supports the '='
• TimeTrackedStartDate supports the '='
• TimeTrackedEndDate supports the '='

For example, the following queries are processed server side:

SELECT * FROM ProjectEstimateReport
SELECT * FROM ProjectEstimateReport WHERE ProjectId = 8375474

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ProjectReport

Retrieves a project report.

Table Specific Information

Select

Query the ProjectReport table to retrieve a project report, broken down by user, group or jobcode. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector. By default the report will include results for a time range within the last 30 days. You can filter by StartDate and EndDate filters to get a payroll report over a different time range.

• UserId, JobcodeId and GroupId fields support the '=' and IN operators.
• StartDate and EndDate fields support the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM ProjectReport WHERE GroupId IN (29474, 29474)
SELECT * FROM ProjectReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'

Based on the columns selected in projection explicitly, the aggregated data displayed may change. Aggregate projects metrics broken down by user id:

SELECT * FROM ProjectReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'
SELECT UserId, TotalHours FROM ProjectReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'

Aggregate project metrics broken down by jobcode id:

SELECT JobCodeId, TotalHours FROM ProjectReport

Aggregate payroll metrics broken down by group id:

SELECT GroupId, TotalHours FROM ProjectReport

Columns

ScheduleCalendars

Retrieves a list of schedule calendars associated with your employees or company.

Table Specific Information

Select

Query the ScheduleCalendars table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id field supports the '=' and IN operators.
• LastModified field supports the <=,<,>=,>,= operators.

For example, the following queries are processed server side:

SELECT * FROM ScheduleCalendars WHERE Id IN (72595, 72597)

SELECT * FROM ScheduleCalendars WHERE LastModified < '2019-01-01 18:30'

Columns

TimesheetsDeleted

Query the Project Notes in TSheets.

Table Specific Information

Select

Query the TimesheetsDeleted table. The connector will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the connector.

• Id supports the '=,IN'
• UserId supports the '=,IN'
• JobcodeId supports the '=,IN'
• StartDate supports the '='
• EndDate supports the '='
• Type supports the '='
• LastModified supports the '<,<=,>,>='
• GroupId supports the '=,IN'
• Username supports the '='
• JobcodeType supports the '='
• SupplementalData supports the '='

Either StartDate and EndDate or ID is required to query the TimesheetsDeleted view. For example, the following queries are processed server side:

SELECT * FROM TimesheetsDeleted WHERE StartDate = '2023-02-01' AND EndDate = '2024-07-01'
SELECT * FROM TimesheetsDeleted WHERE ID = 8375474

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

UserPermissions

The rights assignable to an individual user.

Table Specific Information

Select

Query the UserPermissions table. All the filters are executed client side within the connector.

Columns

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with TSheets.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from TSheets, along with an indication of whether the procedure succeeded or failed.

Jitterbit Connector for TSheets Stored Procedures

CreateInvitation

Invite one or more users to your company.

Input

DownloadFile

Download the file in the raw binary format.

Stored Procedure Specific Information

TSheets allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE DownloadFile FileId = '3688662', DownloadLocation = 'D:\Desktop'

Input

Result Set Columns

GetOAuthAccessToken

Gets an authentication token from TSheets.

Input

Result Set Columns

GetOAuthAuthorizationURL

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the auth token from this URL.

Input

Result Set Columns

RefreshOAuthAccessToken

Refreshes the OAuth access token used for authentication with various MSTeams services.

Input

Result Set Columns

UpdateProjectActivityReadTime

Update the last read time for one or more projects.

Stored Procedure Specific Information

TSheets allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE UpdateProjectActivityReadTime ProjectId = '10001'

EXECUTE UpdateProjectActivityReadTime ProjectId = '10001, 10002'

Input

Result Set Columns

UploadFile

Uploads the file. Only .png, .jpeg, .jpg file formats are allowed.

Stored Procedure Specific Information

TSheets allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE UploadFile FileLocation = 'D:\\Desktop\\Test.png'

EXECUTE UploadFile FileLocation = 'D:\Desktop\Test.png', FileDescription = 'Test file'

Input

Result Set Columns

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for TSheets:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations:

• sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Timesheets table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Timesheets'

Columns

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the CreateInvitation stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='CreateInvitation' AND Direction=1 OR Direction=2

Columns

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Timesheets table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Timesheets'

Columns

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys

Columns

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:tsheets:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns