Timesheets
This table contains a list of file objects.
Table Specific Information
Select
The connector 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 connector.- 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
SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。
| 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. |