Using the TrackTik Query Language (TQL) Playground

Learn about the TQL Playground and how to use it in Data Lab.

Overview

TQL is the foundation of all widgets and dashboards in the Data Lab. Once you have mastered the fundamentals of TQL, you can use the same syntax to filter, modify and tweak the data you generate in widgets and dashboards. You can write queries in the Playground or use a TQL query widget.

TQL queries must be designed to not take more than 1 minute to execute or they will time out and no data will be returned.

A TQL query's maximum length, measured in characters including white space and carriage returns, should be kept under 10,000 characters or the query parsing engine may become overloaded. While the technical limit is 16,000 characters, the query parsing engine will convert special characters to the HTML Entities format, which adds even more characters and can cause a query less than 16,000 characters to reach the limit anyway.

TQL use case table

Here are some common uses and examples for TQL:

SELECT
createdBy.firstName,
createdBy.lastName,
number  
FROM
app_licenses

SELECT
firstName,
lastName,
employmentProfile.terminationDate
from
employees
where
employmentProfile.terminationDate <> ''

SELECT
applicationVersion,
deviceModel,
deviceOs,
deviceIMEI
FROM
mobile_devices

SELECT
payRateType,
employee.firstName,
employee.lastName,
hourlyRate,
yearlyPayRate
from
employment_profiles
where
hourlyRate   = ""
or yearlyPayRate   = ""

SELECT
employee.firstName,
employee.lastName,
startDateTime,
endDateTime,
shiftId.startDateTime,
shiftId.endDateTime
From
work_sessions

SELECT
count(id) as number,
date
from
invoices
group by
date

SELECT
billItem.name,
sum(invoiceLine.lineSubTotal) as sum
from
billable_items
group by
billItem.name
order by
sum(invoiceLine.lineSubTotal) desc

SELECT
billingSetting.type,
name,
id,
beginDate,
endDate,
account.name,
billingSetting.rate,
billingSetting.billItem,
payrollSetting.payPositionRate,
billingSetting.overtimeRule.name,
billingSetting.holidayMultiplier,
billingSetting.holidayGroup.name,
payrollSetting.positionHourlyRate,
payrollSetting.holidayRate,
payrollSetting.holidayMultiplier
from
positions

SELECT
updatedBy.firstName,
updatedBy.lastName,
employee.firstName,
employee.lastName,
updatedOn
from
employee_skills

SELECT
user.firstName,
user.lastName,
loggedInOn,
ip
from
user_login_logs

SELECT
firstName,
lastName,
email,
username,
status,
client.name
from
client_users

SELECT
shift.approvedHours,
shift.clockedHours,
shift.payableHours,
shift.billableHours
from
payroll_payrun_items

Using the TQL Playground

The TQL data structure is similar to a SQL database. The endpoints you can use to query data are organized into folders that make it intuitive to navigate and query the information you need.

Data Models

The endpoints and fields in each endpoint are grouped into folders. Select the arrows to expand the folders, endpoints, and fields.

Entities

Here, you can find the basic data entities in the TrackTik system. For example, clients, positions, regions, and zones:

• Accounts
  Accounts include all sites, zones, departments, and regions.
• Addresses 
  Here, you can query addresses belonging to sites, zones, departments, regions, and employees.
• App_Licenses
  This endpoint contains information about app licenses and cloud storage associated with sites, zones, and departments. 
• Client_Users
  Here, you will find information about users accessing the system using client portal access.
• Clients 
  This endpoint contains information about the sites in your system.
• Comments
  Query user and system-generated comments under this endpoint. Some examples are when report statuses are changed when ticket statuses are updated, when patrol statuses are updated, and when shifts are closed automatically when the user did not punch out.
• Contacts
  With this endpoint, you can query a list of your customer contacts.
• Departments
  Query a list of departments in your TrackTik portal.
• Employee Roles
  Here, you can query a list of roles to which employees are assigned.
• Employees
  Query a list of your employees.
• Event_Activities
  Query a list of system events by account and/or by an employee. Some examples of events include a report being created, a tour being started, a user clocking in, clocking out, or the panic button being activated.
• Files
  Query-specific data on the files you have uploaded to the system. Note: You will not see the actual file. For example, if you have uploaded a picture, the picture will not be visible, but you will see the image URL.
• Geo_Locations 
  Query a list of geocoded addresses in the system.
• Mobile_Devices 
  Query information about mobile devices being used in the TrackTik system.
• Notes 
  Query information on notes entered in the system, for example, notes by or on employees or notes associated with sites.
• Positions 
  Find information about zones, sites, and department positions throughout the TrackTik portal.
• Regions 
  Get information about your TrackTik regions.
• Role_Access_Policies
  For each role in TrackTik, you can get information on the conditions under which your access is granted or blocked. In TrackTik, you can find this information under Roles and Permissions > IP Block Scenario.
• Role_Permissions 
  Query the permissions associated with each role you have created in TrackTik.
• Roles 
  Get information about the roles in your TrackTik system.
• User_Roles 
  Get information about which roles are associated with which users.
• Users 
  Query a list of users and other information, such as when the user last logged in.
• Vehicles 
  Get information about the vehicles in your TrackTik database.
• Work_Sessions 
  Query information about each time a user works. Get their clock-in and clock-out times, how they accessed the system, associated shift information, where the work session took place, and more.
• Zones 
  Query a list of zones and associated zone information.
  

Common 

• Back_Office_Settings 
  Get information about scheduling, billing, and payroll settings.
• Holiday_Groups 
  Query information about the holiday groups you have set up.
• Site_Locations 
  Query information on locations that you have added to individual sites.
  

Billing

• Bill_Items 
  Query information about the bill items you have set up in TrackTik. These are the bill items you create under Settings > Bill Items in your TrackTik portal.
• Billable_Items 
  Billable items have appeared or are ready to appear on an invoice. You can query them under this endpoint.
• Billing_Adhoc_Items 
  If you have created ad hoc billing items at the site, they will appear under this endpoint. Ad hoc items appear on invoices once and are not included in the regular billing cycle of the contract.
• Client_Billing_Settings 
  Query information about the billing configurations you have set up under the site billing tab.
• Contract_Service_Models 
  Get information on the service models that appear on your contracts.
• Contract_Occurrences
  Get information on individual instances of your contracts, like the contractId and the occurrenceDate.
• Contract_Occurrence_logs
  • Contracts 
    Get information about the contracts you have set up at your sites.
  • Invoice_Line_Taxes 
    Get information on the taxes that appear on the lines of your invoices.
  • Invoice_Lines 
    Get specific information from the individual lines of your invoices.
  • Invoices 
    Query information about invoices you have generated.
  • Master_Service_Types 
    This endpoint returns information about the Job/Service Types in your system.
  • Payment_Methods 
    Query information about the different methods your clients can use to pay their invoices.
  • Position_Billing_Settings 
    Query the billing information associated with positions. Examples include the position, holiday billing rate, the billing overtime rule, the holiday rate, and the holiday rate multiplier.
  • Price_Tiers 
    Query a list of price tiers in your TrackTik portal.
  • Task_Price_Tiers 
    Get information on the price tiers associated with dispatch and patrol jobs.
  • Tax_Class_Items 
    Query a list of individual tax class components.
  • Tax_Classes 
    Query a list of tax classes. They determine which taxes and how much are applied to your invoices. Each tax class may consist of individual tax class items.
    

Scheduling

• Break_Rule_Complaince_Logs 
  This endpoint shows whether or not employees took breaks when and how they were supposed to.
• Break_Rule_Conditions 
  Get information about pre-defined breaks, such as break duration and break reminders.
• Break_Rule_Exceptions 
  Query information about breaks that were not taken as planned.
• Break_Rule_Relations 
  Get information about break rule relations, such as whether a break rule is associated with a position.
• Break_Rules
  Query a list of break rules.
• Break_Sessions 
  You can see when breaks were started and ended, the work session with which the break was associated, and the method the employee used to begin and end the break session.
• Holiday_Date_To_Classes 
  Use this endpoint to view a list of holidays in the system, see which dates they fall, and see the type of holidays they are.
• Holiday_Dates 
  Use this endpoint to query a list of dates on which your holidays fall.
• Holiday_Group_Date_Relations 
  See the dates on which your holidays fall and the groups to which those holidays are assigned.
• Holiday_Recurrents 
  Get a list of the recurrent holidays in your system. Recurrent holidays fall on the same day each year.
• Open_Shift_Requests 
  Get information on who requested which open shifts.
• Overtime_Rule_Items 
  Get information about custom overtime rules in the portal.
• Overtime_Rules 
  Get a list of all overtime rules in your portal.
• Position _Skills 
  Get information on the skills that are associated with positions. These are skills that employees need to have to be able to work shifts at these positions.
• Schedule_Off_Periods 
  View information about the employee’s scheduled time off periods, as entered in their HR profile.
• Schedule_Time_Off_Request_Types 
  Query information on the types of time off requests employees can make.
  
• Schedule_Time_Off_Requests
  
  • Shift_Logs 
    Get information about which changes were made to scheduled shifts and who made those changes.
  • Shift_Notes 
    See shift notes' contents and shift note information for both system and user-generated shift notes.
  • Shift_Proposal_Queue_Items 
    See information associated with shifts logged to the proposal queue.
  • Shift_Templates 
    Get information on shifts in the schedule template.
  • Shifts 
    See specific information about scheduled shifts.

Checkpoint

• Checkpoint_Question_Answers
  Query information on answers that guards have given to checkpoint questions.
• Checkpoint_Scans 
  Get information about all checkpoint scans performed.
• Checkpoint_Tour_Assignments 
  See which checkpoints are assigned to which tours.
• Checkpoint_Tour_Schedule_Occurrences 
  See when tours are assigned to checkpoint tour schedules.
• Checkpoint_Tour_Schedules 
  Get a list of checkpoint tour schedules.
• Checkpoint_Tour_Session_Comments 
  See the comments entered by guards each time a tour session is performed.
• Checkpoint_Tour_Sessions 
  See information on each tour session your guards complete.
• Checkpoint_Tours 
  See a list of checkpoint tours.
• Checkpoints 
  See a list of checkpoints in your TrackTik portal.
  

Dispatch

• Cities
  If you have saved cities in your portal for dispatch purposes, you can query them under this endpoint.
• Dispatch_Custom_Locations 
  See a list of tasks and a list of custom locations to which they were dispatched, if any.
• Dispatch_Slas 
  Get a list of dispatch SLAs and their parameters under this endpoint.
• Dispatch_Tasks 
  Under this endpoint, you will find a list of tasks you have dispatched.
• Task_Site_Instructions 
  Instructions you have entered for a specific task at a specific site can be found under this endpoint.
• Task_Site_Settings
  Under this endpoint, you will find the default site instructions you entered under the Mobile Dispatch tab at the site. You will also see whether the site is an alarm organization and whether it is a banned site and should not receive dispatch services.
• Task_Types 
  Here is a list of the jobs/service types saved in the system and whether they can be dispatched, schedulable, prioritized, and more.
• Vendors 
  If you have entered vendors into TrackTik, you can query them here.
• Workflow_Instance_Logs 
  Every time a dispatch job is performed and linked to a workflow, you will query this endpoint and see information about the workflow nodes.
• Workflow_Instances 
  Get the current status of a workflow, as well as its configurations and settings.
• Workflow_Statuses 
  Get information about workflow status nodes, including background color, text color, and alert thresholds.
• Workflows 
  Query information about workflows.

Config

• Data_View_Categories
  You can query information about the folders you create for widgets and dashboards in Data Lab under this endpoint.
• Data_View_Modular_Hooks 
  Use this endpoint to view information about the modular hooks you have created to inject dashboards and widgets into user portals.
• Data_Views 
  You can see information about the widgets and dashboards you have created in Data Lab under this endpoint.
• Guid_Resources 
  Use this endpoint to get information about custom IDs and the IDs they are associated with.
• System_Settings 
  See information about which modules are active.
• Twilio_Accounts 
  Use this endpoint to query Twilio account information.
• User_Notification_Settings 
  See information about user notification configurations in the system.
• Whitelisted_Ips 
  Query information about IPs you have whitelisted in the system.
  

HR

• Employee_Account_Assignments 
  View information on which employees have been assigned to which accounts.
• Employee_Account_Bans 
  See which employees have been banned from which accounts.
• Employee_Availabilities 
  View the employee availability information as saved in the availabilities tab in the employee file.
• Employee_Classes 
  Use this endpoint to get a list of employee classes and associated information.
• Employee_Skills 
  See which skills and attributes are assigned to which employees.
• Employment_Profiles 
  See specific information about the employee contained in their employment profile.
• Employment_Termination_Reasons 
  Query the termination reasons for terminated employees.
• Skill_Categories 
  See a list of the categories to which employee skills are assigned.
• Skills 
  Use this endpoint to get a list of skills and associated information, such as skill category, region, and field labels in the skill setup screen.
  

Patrols

• Geo_Fences
  Get information about the geofence configurations in your portal.
• Mobile_Runsheet_Occurrences 
  Query the instances where a runsheet is used (not performed by a guard) for a job within a zone.
• Mobile_Runsheet_Sessions 
  Each time a runsheet is performed, a mobile runsheet session is created. Query the mobile runsheet sessions here.
• Mobile_Runsheets 
  Query a list of runsheets and associated information.
• Mobile_Schedule_Occurrence_Logs 
  Under this endpoint, you will find information about what happened within a mobile schedule occurrence, such as when the user's status was en route, on-site, and when the user completed the job.
• Mobile_Schedule_Occurrence_Reassignments 
  Query information about mobile runsheet jobs that were re-assigned.
• Mobile_Schedule_Occurrences 
  Query information each time a mobile schedule is used. Mobile schedules are set up under the site's mobile dispatch tab and are used to schedule patrol hits. An occurrence is whether that mobile schedule is used when a job is assigned to it.
• Mobile_Schedules 
  Query information about the mobile schedules that have been created at the site to schedule patrol hits.
  

Lone Worker

• Lone_Worker_Check_In_Logs
  Use this endpoint to find information about when lone workers checked in.  See how and when they checked in to signal their presence.
• Lone_Worker_Check_In_Tickets 
  Use this endpoint to get information on the system exception tickets created with Lone Worker.
• Lone_Worker_Check_Ins 
  Under this endpoint, you will find information on Lone Worker check-ins. Unlike lone_worker_check_in_logs, you will find information under this endpoint relating to the guard’s shift, as well as a relation to the check-in log information.
• Lone_Worker_Phone_Calls 
  When a lone worker calls in, you will find information under this endpoint about the number they called and the number they were calling from.  You will also have access to user and account information.
• Lone_Worker_Phones 
  Query information about phones that are authorized to make Lone Worker check-in calls. You will find these phone numbers under call-in timekeeping settings in the TrackTik portal.
• Lone_Worker_Schedule_Check_Ins 
  View information about the scheduled Lone Worker check-ins for accounts and positions.
• Lone_Worker_Time_Keeping_Settings 
  Query additional information under call-in timekeeping settings at a site. For example, query whether Lone Worker check-ins are enabled, the number of minutes after which a check-in will be considered late, and whether check-ins should be restricted to specific phone numbers.

Message Board

• Message_Board_Post_Acknowledgements 
  See information on the employees who have acknowledged message board posts.
• Message_Board_Post_Categories
  Here is a list of message board post categories and associated information, including the category's name, description, and details.
• Message_Board_Post_Recipients 
  See a list of employees who have received specific message board posts.
• Message_Board_Posts
  See information on message board posts and associated information, such as the message content, the status, and when the message expires.

Payroll

• Payroll_Adhocs
  From this endpoint, you can query a list of ad hoc items associated with payrolls.
• Payroll_Codes
  Query a list of payroll codes and associated information, such as the type, the multiplier, and whether the code is payable or billable.
• Payroll_Deductions
  Use this endpoint to get information on deductions that appeared on payruns.
• Payroll_Payrun_Items 
  Get a list of the individual lines associated with invoices.
• Payroll_Payruns 
  Get information on the payruns you have generated in TrackTik.
• Payroll_Schedule_Occurrences 
  In this endpoint, you will find information on using payroll schedules when preparing payruns.
• Payroll_Schedules 
  Query a list of payroll schedules, including pay run period start date and time, overlapping strategy, and weighted overtime setting.
• Payroll_Position_Settings 
  Get information about the payroll settings specific to positions. Information includes break settings, the position pay rate, the holiday group, pay codes, and the holiday multiplier.
• Position_Premiums 
  Get information about pay premiums associated with the position. Information includes the premium name, the premium code, and the premium status. This information is set in the payroll section when setting up a position in the TrackTik portal.
  

Post Order

• Post_Order_Acknowledgements 
  See which employees had acknowledged post orders at sites, zones, and departments and when they made those acknowledgments.
• Post_Order_Subjects 
  Query a list of post-order subjects by account.
• Post_Orders 
  Get details about post orders at sites, zones, and departments, the post order subject, the post order details, who created the post order, and when.

Reporting

• Report_Fields 
  This endpoint contains information about the individual fields on the reports that guards have filed. You can get the report template information, the report information, such as where and when the user filed the report and the contents and labels of each field.
• Report_Flag_Categories 
  Under this endpoint, you will find information about the parent incident categories under settings > incident categories > parent categories.
• Report_Flag_Group_Assignments 
  Get information about the groups to which incident categories are assigned. You can find incident category groups in the TrackTik portal under settings > incident categories > groups/industries.
• Report_Flag_Groups 
  Query information on the incident category groups referenced in the previous point.
• Report_Flags 
  Query a list of incident categories and associated information, such as severity level, parent category, and whether the incident category will be shown in the default group.
• Report_Template_Fields 
  Get details on the fields contained in report templates. Unlike the report_fields endpoint, this endpoint contains information about fields that have not yet been filled out.
• Report_Templates 
  Query a list of report templates and associated information, such as when the template was created and by whom, the template details, and the template name.
• Reports 
  Under this endpoint, you will find information about filed reports. This includes information such as the account and position to which the report is associated, who submitted the report, and approval information.

Asset Management

• Schedulers: If you use asset management, you can use this endpoint to find information about service schedules for existing assets.

Logs

• System_Usage_Logs 
  Here, you will find information on runsheet route optimization and sending SMS messages. See when these activities were performed and who performed them.
• User_Login_Logs 
  Use this endpoint to get information about when users log into the system, their IP, their method of accessing the system, and the browser they were using.

Functions

Under the data models, you will find a list of available TQL functions.

TQL function table

In TQL, you can use functions to help you do things, such as performing math operations, applying conditions when displaying data in fields, filtering data, and much more.  Use this section to help you decide which functions are appropriate in which situations.

TQL function	TQL function example
AVG Displays an average when a column is numeric.
COUNT Returns the number of rows that meet the conditions you enter.
SUM Calculates the sum of a column or values.
CONCAT This function combines two or more strings.
MAX This function returns the largest value of the selected data.
MIN Min returns the smallest value of the selected data.
IF  Adds a condition that returns one value if the condition is true and another value if the condition is false. The first value in quotations is returned if true, and the second if false.
YEAR  Extract the year part of a date or date/time field.
DATE  Extract the date part of a date/time field.
MONTH  Extract the month from a date or date/time field.
MONTHNAME  Get the month's name from a date or date/time field.
QUARTER Extract the quarter from a date or date/time field.
MINUTE  Extract the minute part of a date/time field.
RIGHT_STR  Extract a specified number of characters starting from the right of the string.
LEFT_STR  Extract a specified number of characters starting from the left of the string.
NOW  Displays the current date and time.
REVERSE  Reverses the contents of a specified column.
ROUND  Round the specified column to the indicated number of decimal places.
SECOND  Extract the seconds from a date/time field.
WEEK  Extract the number of weeks of the year from a date or date/time field.
HOUR  Extract the hours from a date/time field.
REPLACE  Replace a selected string with a different string. The first string is what to replace. The second string will replace the first.
AddMinutes  Add the specified number of minutes to a date/time.
AddHours Add hours to a date/time.
MinutesBetween  Returns the difference between two date/time fields.
HoursBetween  Returns the difference in hours between two date/time fields in hours.
TimeZonify Converts a timestamp from UTC to a time zone.
DATE_FORMAT  Generates a specific date format while using the TimeZonify function.
FROM_UNIXTIME Converts a UNIX timestamp to a human-readable timestamp in UTC.
CONVERT_TZ This function converts a date/time field from one time zone to another.
LOCATE  This function returns the position of the first occurrence of a substring within a string. If the substring is not found in the string, 0 is returned.
GROUP_CONCAT  This function returns a string with concatenated values from a group. For example, it would return a concatenated string of user first names in a single column.
NOW  Now is a function you can add to the select statement that will output the date stamp of the current moment when you execute the query. This function takes no arguments.
CHAR_LENGTH  This function returns the number of characters in a string. In this example, the query will return the number of characters.

Relative Dates

Relative dates help save time by eliminating the need to write out dates as a string, like ‘2019-06-01’. Use relative dates when adding where statements to TQL queries:

• YESTERDAY_START
  The start of the day before. It will appear as yesterday’s date if selected in a query.
• YESTERDAY_END
  The end of the day before. It will appear as today’s date if selected in a query.
• TODAY_START
  The beginning of today. It will appear as today’s date in a query.
• TODAY_END
  The end of today. It will appear as tomorrow’s date in a query.
• TOMORROW_START
  The beginning of tomorrow. It will appear as tomorrow’s date if selected in a query.
• TOMORROW_END
  The end of today. It will appear as tomorrow’s date in a query.
• LAST_WEEK_START
  The first day of the previous week. It will appear on the first Sunday of the week.
• LAST_WEEK_END
  The last day of the previous week. It will appear in the query as the last Sunday of the previous week.
• LAST_MONTH_END
  The end of the previous month. It will appear in the query as the first of the current month.
• LAST_QUARTER_START
  The first day of the previous quarter. It will appear as the date of the first day of the previous quarter.
• LAST_QUARTER_END
  The last day of the previous quarter. In the query, it will appear on the first day of the current quarter.
• LAST_YEAR_START
  The first day of the previous year. It will appear in the query as January 1 of the previous year.
• LAST_YEAR_END
  The last day of the previous year. It will appear in the query as the first day of the current year.
• NEXT_WEEK_START
  The first day of next week. The query will show the date as the first Sunday of the week.
• NEXT_WEEK_END
  The last day of the following week. Appears in the query as the date of the last Sunday of the following week.
• NEXT_MONTH_START
  The first day of the following month. It will appear in the query as the date of the first day of the following month.
• NEXT_MONTH_END
  The last day of the following month. It will appear in the query as the first of the month following next month.
  
• NEXT_QUARTER_START
  The first day of the following quarter. It will appear in the query as the date of the first day of the next quarter.
  
• NEXT_QUARTER_END
  The last day of the next quarter. It will appear in the query as the first day of the quarter following the next quarter.
  
• NEXT_YEAR_START
  The first day of the following year will appear in the query as January 1.
  
• NEXT_YEAR_END
  The last day of the following year. It will appear in the query as the first day of the year after next.
  
• THIS_WEEK_START
  The first day of the current week. It will appear in the query as the date of the first Sunday of the week.
  
• THIS_WEEK_END
  The last day of the current week. It will appear on the last Sunday of the week.
  
• THIS_MONTH_START
  The first day of the current month. It will appear in the query as the first day of the current month.
  
• THIS_MONTH_END
  The last day of the current month. It will appear on the first day of the following month.
  
• THIS_QUARTER_START
  The first day of the current quarter. It will appear on the first day of the quarter.
  
• THIS_QUARTER_END
  The last day of the current quarter. In the query, the date will appear as the first day of the following quarter.
  
• THIS_YEAR_START
  The first day of the current year. The date will appear as January 1 of the current year.
  
• THIS_YEAR_END
  The last date of the current year.

Use TrackTik Query Language (TQL) to generate custom reports that contain the data you need when you need it. TQL is a simplified version of SQL that will allow you to get up and running in minutes. Use this section as a reference when writing TQL queries.

Remember that when you write TQL queries, you are creating API calls to TrackTik API endpoints. For more information on the TrackTik API, please visit ://{yourportalurl}/rest/v1, where {yourportalurl} is the URL you use to access TrackTik.

You can find the data structure of the TrackTik API on the left-hand side of the TQL Playground.

Common TQL keywords table

Use these keywords when building TQL queries.

TQL keyword	TQL keyword example
Select Use select to choose the fields from the endpoint you are querying.
From This keyword precedes the name of the endpoint you are querying.
Where Use where to filter the records returned by the query.
And Use and to combine conditions in where statements. When using and, all conditions must be true for the records to be returned.
Or Like the and keyword, the or keyword connects multiple conditions in a where statement. Only one of the conditions needs to be true for records to be returned.
Group By Use the group by keyword to group data with the same values into rows. For example, when you want to find the number of invoices by customer, you can group invoices by customer name. Therefore, it is often practical to use group by with functions such as AVG (average), SUM (sum), or COUNT (count).
Order By Use order by to put the query results in ascending or descending order by column. By default, records will be sorted in ascending order.
Having Use having if where with an aggregate function would not be possible.
As Allows you to add an alias to a given column. This can make the column easier to read and more intuitive to understand.

Joins

There is no need for JOINS in TQL. Instead, use the relations indicated by the down arrows in each endpoint. In this example, you can use the employmentProfile relation under employees to get the employee’s hourly rate and employment date.

Relations

When working with relations, it is best practice to work from more specific to less specific. For example, if you need a subtotal of each line on an invoice, query the invoice_lines endpoint and work towards invoices.

Requirement example	Query
I need a report showing the invoice date, the service date, the quantity or number of hours being billed, the price per hour (unit price), the total amount of tax per line, and the subtotal per line.

Remember that when you write TQL queries, you are creating API calls to TrackTik API endpoints. For more information on the TrackTik API, please visit ://{yourportalurl}/rest/v1, where {yourportalurl} is the URL you use to access TrackTik.

See also

• Using the Data Model Library
• Create a TrackTik Query Language (TQL) Query Widget