FQL is used to construct Lists in the helpdesk, here is a guide detailing how to use FQL to create these fields.
Creating an FQL Query
An FQL Query must contain at least two clauses.
Field - you must select a field that you wish to base your query on.
Value - you must specify a value that you wish that field to match.
To create a query that returns all Tickets that are in the resolved status.
Find the field that holds Ticket status infomation
ticket.statusYou then need to find an operator that selects all Tickets where this field shows the Ticket is resolved. If you look on the field reference guide next to the tickets.status field we'll see the operator listed as String comparison.
Check the operators available for a string comparison. You'll see the options available are
=, !=, in, not in.You'll then need to match the string 'resolved' to the contents of
ticket.status. Per the operators list we can use eitherINOR=
Using IN
ticket.status IN ('resolved') copyUsing =
ticket.status = 'resolved' copyIn this instance either of these operators work. However, using IN gives you greater flexibility, as if you decided you wanted to include additional statuses in the query (e.g. archived) it would be very easy to adjust it:
ticket.status IN ('resolved', 'archived') copyAdding Multiple Arguments to a Query
It is possible to add multiple arguments to a query using the logical operator AND.
We can use AND to make a query more specific as it allows us to apply the criteria from multiple fields.
To create a query for all resolved Tickets that have been resolved from one department.
Take the query from the previous example to return resolved Tickets
ticket.status IN ('resolved').Find the field that holds the details of the Ticket department
ticket.departmentCheck the operator for this field in the field reference guide (in this instance it is 'ID ref') and check the operators guide to see the options available (we will use
ticket.department =in this instance).Get the ID number for your Ticket department.
Add this to your Ticket department query so we have
ticket.department = 1and the originalticket.status IN ('resolved'). We can then simply combine these with AND so our query returns just resolved Tickets from department ID 1.
ticket.status = 'resolved' AND ticket.department = 1 copyTo do this we can use the DATE function which allows us to compare fields with date values to our specified dates.
Using FQL with Dates
If you want to generate queries based on dates, you can specify dates using FQL in three different ways:
Absolute Dates
You can simply use the YYYY-MM-DD format to specify a date.
If you wanted to see every ticket created in a specific month (in November 2021) you could use:
ticket.date_created >= DATE("2021-10-31") copyShortcuts
Shortcuts are an easy to read and write format for dates hard coded into the system.
The options available are as follows:
Shortcut |
|---|
today |
yesterday |
thisWeek |
lastWeek |
thisMonth |
lastMonth |
thisYear |
lastYear |
1h |
2h |
6h |
12h |
If we wanted see every Ticket created in this month we could use:
ticket.date_created >= DATE("thisMonth") copyOperations
Operations are an easy way to reference relative dates in the past or in the future.
They helpful for creating rolling queries e.g. Tickets created in the last 30 days.
The units available are:
day/days
week/weeks
month/months
year/years
You can use positive and negative integers alongside them so the format for an operation would be written as 1 day or -3 weeks.
If you wanted to create a list that shows tickets created in the last 30 days you could use the following
ticket.date_created >= DATE("-30days") copyDate Ranges
The full list of date operators in operators guide below.
For dates a particularly useful operator to be aware of is the BETWEEN operator as this allows us to construct date ranges.
You can use this with all three date formats but it's perhaps most useful to use with absolute dates.
If you'd like to generate a list of all tickets created in November 2021 you would just need to specify the dates those days fall between.
ticket.date_created BETWEEN DATE('2021-11-01') AND DATE('2021-12-01') copyNote tickets from the after date (so in this instance 2021-11-01) will be included in the query but those in the before date after the AND (2021-12-01) will not be.
Time Zones
When using a date in a query it is also possible to specify the time zone you want that date to be based on.
The time zones available are the same as those used throughout the rest of the system so check the full list in your Agent preferences if you want to see what is available.
You might want to view all Tickets created in the last 3 days but base it on the LA time zone to be sure your count is accurate:
ticket.date_created `>= DATE("-3 days", "America/Los_Angeles") copyField References
Articles
Name | Operator | Values |
|---|---|---|
article | ID | |
article.status | String comparison | published, archived, hidden |
article.hidden_status | String | unpublished, draft, deleted, spam |
article.person | ID ref | Ref to people |
article.language | ID ref | Ref to languages |
article.categories | ID ref | Ref list to article_categories |
article.labels | String comparison | Sub-search from labels_articles |
article.title | String equation | |
article.content | String equation | |
article.date_created | Date | DATE() with allowed params |
article.date_updated | Date | DATE() with allowed params |
article.date_published | Date | DATE() with allowed params |
article.data.* | CustomField |
Chats
Name | Operator | Values |
|---|---|---|
chat.id | ID | |
chat.status | String comparison | open, ended |
chat.department | ID ref | Ref to departments |
chat.brand | ID ref | Ref to brands |
chat.agent | ID ref | Ref to people |
chat.person | ID ref | Ref to people |
chat.person_name | String equation | |
chat.person_email | String equation | |
chat.subject | String equation | |
chat.date_created | Date | DATE() with allowed params |
chat.data.* | CustomField |
Community
Name | Operator | Values |
|---|---|---|
community_topic.id | ID | |
community_topic.status | String comparison | published, archived, hidden |
community_topic.hidden_status | String comparison | unpublished, draft, deleted, spam |
community_topic.person | ID ref | Ref to people |
community_topic.language | ID ref | Ref to languages |
community_topic.forum | ID ref | Ref to community_forums |
community_topic.title | String equation | |
community_topic.content | String equation | |
community_topic.labels | String | Sub-search from labels_community_topics |
community_topic.date_created | Date | DATE() with allowed params |
community_topic.date_updated | Date | DATE() with allowed params |
community_topic.date_published | Date | DATE() with allowed params |
community_topic.data.* | CustomField |
Files
Name | Operator | Values |
|---|---|---|
download.id | ID | |
download.status | String comparison | published, archived, hidden |
download.hidden_status | String comparison | unpublished, draft, deleted, spam |
download.person | ID ref | Ref to people |
download.language | ID ref | Ref to languages |
download.category | ID ref | Ref to download_categories |
download.labels | String | Sub-search from labels_downloads |
download.title | String equation | |
download.content | String equation | |
download.date_created | Date | DATE() with allowed params |
download.date_updated | Date | DATE() with allowed params |
download.date_published | Date | DATE() with allowed params |
download.data.* | CustomField |
News
Name | Operator | Values |
|---|---|---|
news.id | ID | |
news.status | String comparison | published, archived , hidden |
news.hidden_status | String comparison | unpublished, draft, deleted, spam |
news.person | ID ref | Ref to people |
news.language | ID ref | Ref to languages |
news.category | ID ref | Ref to news_categories |
news.labels | String | Sub-search from labels_news |
news.title | String equation | |
news.content | String equation | |
news.date_created | Date | DATE() with allowed params |
news.date_updated | Date | DATE() with allowed params |
news.date_published | Date | DATE() with allowed params |
Organizations
Name | Operator | Values |
|---|---|---|
organization.id | ID | |
organization.name | String equation | |
organization.summary | String equation | |
organization.importance | String | |
organization.members | ID ref | Ref to people |
organization.email_domains | String comparison | Sub-search from organization_email_domains |
organization.labels | String comparison | Sub-search from labels_organizations |
organization.date_created | Date | DATE() with allowed params |
organization.data.* | CustomField |
Person
Name | Operator | Values |
|---|---|---|
person.id | ID | |
person.is_agent | Boolean | true, false, 1, 0 |
person.organization | ID ref | Ref to organizations |
person.language | ID ref | Ref to languages |
person.brand | ID ref | Ref to brands |
person.user_groups | ID ref | List ref to person2usergroups |
person.name | ||
person.first_name | String equation | |
person.last_name | String equation | |
person.primary_email | String comparison | Sub-search from people_emails |
person.phones | String comparison | Sub-search from phone_numbers |
person.emails | String comparison | Sub-search from people_emails |
person.labels | String comparison | Sub-search from labels_people |
person.date_created | Date | DATE() with allowed params |
person.date_last_login | Date | DATE() with allowed params |
person.data.* | CustomField |
Tickets
Name | Operator | Values |
|---|---|---|
ticket.id | ID | |
ticket.ref | String equation | |
ticket.status | String comparison | |
ticket.ticket_status_id | ID ref | Ref to ticket_statuses |
ticket.is_hold | Boolean | true, false, 1, 0 |
ticket.urgency | String comparison | number casted to string |
ticket.organization | ID ref | Ref to organizations |
ticket.department | ID ref | Ref to departments |
ticket.person | ID ref | Ref to people |
ticket.agent | ID ref | Ref to people |
ticket.agent_team | ID ref | Ref to agent_teams |
ticket.followers | ID ref | Ref to people |
ticket.participants | ID ref | Ref to people |
ticket.language | ID ref | Ref to languages |
ticket.product | ID ref | Ref to products |
ticket.category | ID ref | Ref to ticket_categories |
ticket.priority | ID ref | Ref to ticket_priorities |
ticket.workflow | ID ref | Ref to ticket_workflows |
ticket.brand | ID ref | Ref to brands |
ticket.email_account | ID ref | Ref to email_accounts |
ticket.labels | String | Sub-search from labels_tickets |
ticket.problems | ID ref | Ref to problems |
ticket.person.email_address | String comparison | Sub-search from people_emails |
ticket.subject | String equation | |
ticket.messages | String equation | Sub-search from tickets_messages |
ticket.starred | String comparison | String color from tickets_flagged |
ticket.date_created | Date | DATE() with params |
ticket.date_resolved | Date | DATE() with params |
ticket.date_archived | Date | DATE() with params |
ticket.date_last_agent_reply | Date | DATE() with params |
ticket.date_last_user_reply | Date | DATE() with params |
ticket.agent_waiting | Date | DATE() with params |
ticket.user_waiting | Date | DATE() with params |
ticket.slas | ID ref | Ref to ticket_slas via SLAs functions |
passingSlas() | Function | Predicate function, does not accept params |
warningSlas() | Function | Predicate function, does not accept params |
failedSlas() | Function | Predicate function, does not accept params |
ticket.data.* | CustomField | |
ticket.organization.data.* | CustomField | |
ticket.person.data.* | CustomField |
Operators
To check the operator type you can refer to the operator column in the field references table.
Once you've found the operator type check the name in the table below to see what operators are available to use with your field:
Name | Operators | Value |
|---|---|---|
ID | =, !=, >, >=, <, <=, between, not between, in, not in, has | |
ID ref | =, !=, >, >=, <, <=, between, not between, in, not in, has, exists, not exists, empty, not empty | |
String comparison | =, !=, in, not in | |
String equation | =, !=, HAS ANYWHERE('param'), HAS STARTS_WITH('param'), HAS ENDS_WITH('param') | |
Boolean value | =, != | 1, 0, true, false |
Date value | =, !=, >, >=, <, <=, between, not between, empty, not empty | DATE('param') Param MUST be quoted. |
Function | has, not has | FUNC(param) |
CustomField |
Operator details
Operator | Description | Example |
|---|---|---|
= | Equal to | ticket.id = 10 |
!= | Not equal to | ticket.id != 10 |
> | Greater Than | ticket.id >10 |
>= | Greater than or equal to | ticket.id >=10 |
< | Less than | ticket.id < 10 |
<= | Less than or equal to | ticket.id <= 10 |
between | between two stated values | ticket.id BETWEEN 7 and 10 |
not between | not between two stated values | ticket.id NOT BETWEEN 7 and 10 |
IN | Value matches any of the values in a list | ticket.id IN (7, 8,9,10) |
NOT IN | Value does not match any of the values in a list | ticket.id NOT IN (7, 8,9,10) |
EMPTY | Field does not contain a value | ticket.language IS EMPTY |
NOT EMPTY | Field does contain a value | ticket.language IS NOT EMPTY |
EXISTS | Value exists | ticket.language EXISTS |
NOT EXISTS | Value does not exists | ticket.language NOT EXISTS |
FQL Examples
Here is a list of queries you can generate for each of the different types of content in the helpdesk. These can also help you to understand queries are constructed.
Tickets
Title | Description | FQL |
|---|---|---|
Resolved | Returns all resolved tickets | ticket.status IN ('resolved') |
Archived | Returns all archived tickets | ticket.status IN ('archived') |
Recycle Bin | Returns all tickets in the recycle bin | ticket.status IN ('hidden.1') |
Spam | Returns all tickets that have been marked as Spam | ticket.status IN ('hidden.2') |
Users
Title | Description | FQL |
|---|---|---|
All agents | Returns all people in the CRM who are agents | person.is_agent = 1 |
All agents with name John | Returns all people in the CRM who are both agents AND their name is John | person.is_agent = 1 AND person.name = 'John' |
All agents with name Steve | Returns all people in the CRM who are both agents AND their name is Steve | person.is_agent = 1 AND person.name = 'Steve' |
Organizations
Title | Description | FQL |
|---|---|---|
All Organizations with Inc in their name | Returns all Orgs where their name IS the string 'Inc' | organization.name = 'Inc' |
All Organizations with Ltd in their name | Returns all Orgs where their name IS the string 'Ltd' | organization.name = 'Ltd' |
Publish
Articles
Title | Description | FQL |
|---|---|---|
All articles where title contains 'Example' | Returns all articles where the article title IS the string 'example' | article.title = 'Example' |
All articles published last day by Los Angeles timezone | Returns all articles that were published within the last day based on the Los Angeles timezone | article.date_published < DATE(" 1 day", "America/Los_Angeles") |
All articles published last month | Returns all articles that were published in the last month | article.date_published < DATE(" 1 month") |
All articles published last three months | Returns all articles that were published in the last three months | article.date_published < DATE(" 3 month")' |
News
Title | Description | FQL |
|---|---|---|
All news posts where title contains 'Example' | Returns all news posts where the title IS the string 'example' | news.title = 'Example' |
All news posts published last day by UTC timezone | Returns all news posts that were published within the last day based on the UTC timezone | news.date_published < DATE(" 1 day", "UTC") |
All news posts published last month by UTC timezone | Returns all news posts that were published in the last month based on the UTC timezone | news.date_published < DATE(" 1 month", "UTC") |
All news posts published in the last three months by UTC timezone | Returns all news posts that were published in the last three months based on the UTC timezone | news.date_published < DATE(" 3 month", "UTC") |
Files
Title | Description | FQL |
|---|---|---|
All files with title containing 'Example' | Returns all files where the title IS the string 'example' | download.title = 'Example' |
All files uploaded today | Returns all files that were uploaded today | download.date_created = DATE("today") |
All files uploaded yesterday | Returns all files that were uploaded yesterday | download.date_created = DATE("yesterday") |
Community
Title | Description | FQL |
|---|---|---|
All community topics with title containing 'Example' | Returns all community topics where the title IS the string 'example' | community_topic.title = 'Example' |
All community topics published today | Returns all community topics that were uploaded today | community_topic.date_published = DATE("today") |
All community topics published yesterday | Returns all community topics that were uploaded yesterday | community_topic.date_published = DATE("yesterday") |
Please log in or register to submit a comment.