Timesheets
This table contains a list of file objects.
Table Specific Information
Select
The add-in uses the QuickBooks Time API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the add-in.- Id, UserId, JobcodeId, PayrollIds, GroupIds fields support the =, 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
When executing INSERT queries, specify the 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. The 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
When executing UPDATE queries, specify the Id in the WHERE clause.
For example:
UPDATE Timesheets SET Date = '2018-08-08', EndTime = '2018-08-08T14:00:00-07:00' WHERE Id = '1242123'
Delete
When executing DELETE queries, specify the Id in the WHERE clause.
For example:
DELETE FROM Timesheets WHERE Id = '41321421'
Columns
| Name | Type | ReadOnly | Description |
| Id [KEY] | String | True |
Id of the timesheet. |
| UserId | String | False |
User id for the user that this timesheet belongs to. |
| JobcodeId | Int | False |
Jobcode id for the jobcode that this timesheet is recorded against. |
| Locked | Boolean | True |
If greater than 0, the timesheet is locked for editing. A timesheet could be locked for various reasons. |
| Notes | String | False |
Notes associated with this timesheet. |
| LastModified | Datetime | True |
Date/time when this timesheet was last modified. |
| Type | String | False |
Either 'regular' or 'manual'. |
| OnTheClock | Boolean | True |
If true, the user is currently on the clock . If false the user is not currently working on this timesheet. Manual timesheets will always have this property set as false. |
| CreatedByUserId | String | True |
User id for the user that initially created this timesheet. |
| StartTime | Datetime | False |
Date/time that represents the start time of this timesheet. Will always be an empty string for manual timesheets. |
| EndTime | Datetime | False |
Date/time that represents the end time of this timesheet. Will always be an empty string for manual timesheets. |
| Date | Date | False |
The timesheet's date |
| Duration | Int | False |
The total number of seconds recorded for this timesheet. |
| OriginHint | String | False |
A string that will be logged as part of the timesheet history when someone is clocked in, clocked out, or a timesheet is created (with both in/out). Will always be an empty string for manual timesheets. |
| JobCodeType | String | True |
The type of the jobcode linked with this timesheet. Any of the 'regular', 'pto', 'paid_break', 'unpaid_break' |
| AttachedFiles | String | False |
Ids of files attached to this timesheet. |
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.
| Name | Type | Description |
| PayrollIds | Int |
A comma-separated string of payroll ids. Only time recorded against users with these payroll ids will be returned. |
| GroupIds | Int |
A comma-separated list of group ids. Only timesheets linked to users from these groups will be returned. |