Kantree Query Language (KQL)

Search through cards using our simple & powerful query language

Kantree is shipped with a powerful query langage, that lets you filter or search for cards or compute things from them.

You can use it in multiple places:

  • in a workspace, filter or search for cards,
  • search cards globally,
  • in dashboards (workspace or global), compute values based cards data
  • in cards, compute values based on other card data, in a formula field

Filter cards

To search through card titles, just use the keywords you want to look for. There are three keywords which you cannot use directly as they are reserved: and, or, not. If you want to look for them in titles, you can surround them by double quotes (eg. oranges "and" berries).

To search for cards not matching keywords, prepend the keyword by not. (eg. not oranges). If you need to exclude cards matching a sentence, surround the keywords with double quotes (eg. not "oranges berries").

To search for a card with a specific reference, use # followed by the reference number (eg. #123).

To search for a card which is in a group, use # followed by the group name. If your group name contains spaces, just ignore them (eg. search for cards in “List 2”, use #list2).

To search for a card which has a member in any of its fields, use @ followed by the username (eg. @username). You can use @me to reference the currently logged in user.

Usernames and group names cannot contain dots or spaces. To use names with these characters, surround the name by curly braces (eg. @{user name} and #{List 2}).

Fields

You can search cards by their fields using either of the following syntax: field=value or {field}=value. The former can only be used if there are no spaces or special characters in the field name. Otherwise use the latter (eg. {field with space}=value).

Available operators are:

  • =: equals (eg. {Contact} = "john@doe.com")
  • !=: different (eg. {Estimated hours} != 10)
  • > or >=: greater than or greater or equal than (eg. {Estimated hours} > 10)
  • < or <=: less than or less or equal than (eg. {Estimated hours} <= 10)
  • ~=: fuzzy match (for text values). (eg. description ~= orange)

If the field is a list of values (eg. members field), use the in operator: @username in Assignees.

When comparing a field, values can be:

  • an empty value (meaning the fields has not been set): empty (eg. finding all cards with an empty description: description=empty)
  • a number: 10 or 12.1 or -3.4
  • a string: word or "multiple words are surrounded by double quotes"
  • yes or no (for yes/no fields)
  • a date: a double quoted string using the format: YYYY-MM-DD (eg. "2017-02-05"). A few special keywords which can be used as a date exist: today, tomorrow, yesterday. (eg. {due date}<today)
  • a member: @username
  • a card reference: #123
  • an object reference (see further)

In a formula field, you can also use arithmetic expressions + - / *

example: 3 + 4

If you want to make calculations based on multiple formula fields then, consider using as_number() to convert into “number” data. Indeed, the default results of formula fields are considered by Kantree as text.

I want to multiply the result of a formula field by the result of a formula field, I indicate in the 3rd formula field that will carry the result of the operation:

as_number({formule n°1})*as_number({formule n°2})

A few special fields exist:

  • ref: card reference (eg. ref=123)
  • title: card title (eg. title="my task")
  • created at: card creation date (eg. {created at}="2017-02-01")
  • created by: user who created the card (eg. {created by}=@me)
  • updated at: date of the last update

  • state: card state (eg. state=completed) and some aliases

    • state=todo: referring to undecided, accepted or waiting
    • state=doing: referring to in_progress
    • state=finished: referring to completed, closed or dropped
  • started at: when a card has a state of in progress, the date at which it was started.
  • finished at: when a card has a state of dropped, completed or closed, the date at which it was finished.
  • resolution time: the time between finished at and started at
  • archived: if the card is archived (yes/no value: archived=yes)
  • parent: parent card id (used in query like {parent} in {relationship field 1} or {parent}=#14)
  • hlevel: level of the card in the hierarchy of the project. For instance, for a sub-card, it will return 2
  • nb children: number of children
  • nb comments: number of comments
  • last comment at: date of the last comment
  • model or type: model name (eg. model=bug)
  • workspace or project: workspace name (eg. workspace="My Workspace")
  • org or organization: organization’s name
  • team: team’s name
  • form: form’s name (if the card has been created through a [project form](/help/guides/forms))
  • form submitted by: either {form submitted by}=@username or {form submitted by}="email"
  • shared: whether the card has a public shared link or not

You can use the keyword not in front of any condition to match for cards not meeting the condition (eg. not @me in assignees).

Combining conditions

You can use multiple conditions in your queries. Just put them one after the other. All conditions must be met for a card to match.

For more flexible queries, you can combine conditions using logical operators:

  • and: the different operands are all mandatory
  • or: any of the operand can match

Like mentioned before, if no logical operators are provided, and is used. You can group conditions between parentheses. and has priority over or.

Advanced usage

Functions

There exist 3 types of functions:

  • functions which return a value (eg. now())
  • functions which aggregate values (eg. avg())
  • conditional functions which are used as a condition (eg. date?("week"))

Functions which return a value :

  • now(): returns the current date and time
  • today(): returns today’s date (without time)
  • period(interval): returns an interval to do operations on dates, for instance now() + period("1 week"). The value is a number followed by a time frame (year, month, week, day, hour).
  • ago(interval): returns the date back at the specific interval
    Example: get the cards which have a due date 3 days ago: {due date} <= ago("3 days")
  • ahead(interval): returns the date in the future at the specified interval
  • week_start([date]): returns the date for the first day of the week. If no parameter is provided, current week is used.
  • week_end([date]): returns the date for the last day of the week. If no parameter is provided, current week is used.
  • workweek_start([date]): returns the date for the first working day of the week. If no parameter is provided, current week is used.
  • workweek_end([date]): returns the date for the last working day of the week. If no parameter is provided, current week is used.
  • week_number([date]): returns the number of the week
  • month_start([date]): returns the date for the first day of the month. If no parameter is provided, current month is used.
  • month_end([date]): returns the date for the last day of the month. If no parameter is provided, current month is used.
  • month_number([date]): returns the number of the month
  • year_start([date]): returns the date for the first day of the year. If no parameter is provided, current year is used.
  • year_end([date]): returns the date for the last day of the year. If no date is provided, current year is used.
  • year([date]): returns the year
  • count_days([start], [stop]): returns the number of days between between two dates. If parameters are omitted, current month start and end dates are used.
  • count_working_days([start], [stop]): returns the number of working days between two dates (all days except weekends). If parameters are omitted, current month start and end dates are used.
  • last_moved_in_group_at(group_name, [type_name]): returns the date the card was last moved in the specified group (type_name is optional, it allows to specify the field or context from which an attribute is derived duplicated in the wokspace)
  • if(expression, value, elseValue): returns value if expression is true and elseValue if expression is false
  • as_string(value): converts a value to a string, for instance as_string({Points})
  • as_date(value): converts a value to a date, for instance as_date({Text field})

  • as_daterange({date value 1}, {date value 2}): converts two date fields into one date period value

  • date value : start:select the start date of a date field containing a period example: {date n°2:start}
  • date value : end:select the start date of a date field containing a period example: {date n°2:end}
  • as_number(value): converts a value to a number, for instance as_number({Text field})
  • size(list): returns the size of a list of items, for instance size({assignees})
  • first(value):Get the first value that has been selected in a field with a list of values (Labels)
  • substring(start, count): subtracts the portion of a string from another one substring("hello world", 6, 5) = "world"
  • concat(value1, value2, ...): concatenates multiple values together
  • round(value, [decimal]): rounds a number to the number of decimals (default: no decimals)

  • card(project_title, card_ref): returns the card id from another project

    Example: Thanks to an automation with the action “Set field from formula” associate a card from another workspace through a relationships card field: (“Workspace name”,$(card:Name of the relationships field to be completed with the id})

Functions which aggregate:

  • max(): returns the highest value - Example: from a Dashboard view display the highest value of a number field

  • min(): returns the lowest value - Example: from a Dashboard view display the lowest value of a date field. In this case, it is the oldest date.

    You can also use min() or max() with the card states \

    Examples:\

    max({updated at})

    min({created at}

    max ({started at})

    min ({finished at})

  • avg(): returns the average of the values
  • sum_logs(category_name, field_name, [user], [from_date], [to_date]): sums the value of the specified field in logs from the category. Can be limited to a user and/or to a period. When summing fields of type “Time”, the output will be seconds.
  • count_logs(category_name, [user], [from_date], [to_date]): counts the number of logs in a category. Can be limited to a user and/or to a period.


Conditional functions:

  • full_text_search?(text): search though titles, fields and comments for text
  • search_comments?(text): search only through comments
  • mentions?(@username): search for mentions of the specified user through fields and comments
  • is_in_active_group?(): checks if the card is in a group which has dates and where today is in the date range.
  • is_in_group?(group_name, type_name): checks if the card is in group group_name (type_name is optional, it allows to specify the field or context from which an attribute is derived duplicated in the wokspace)
  • was_in_group?(group_name, type_name): checks if the card has already been in group group_name (type_name is optional, it allows to specify the field or context from which an attribute is derived duplicated in the wokspace)
  • is_in_group_type?(type_name): checks if the card is in group type type_name. (type_name is optional, it allows to specify the field or context from which an attribute is derived duplicated in the wokspace)
  • date?(date): checks if any field of type date matches the specified date. Examples of valid date queries: “05-27-2018” (MM-DD-YYYY format), “week”, “month”, “year”, “week+1” (for next week), “week-1” (for last week), “week-2” (for second to last week)
  • contains_member?(@username): checks if any fields of type member contains @username
  • is_pii?(identifier): checks if any field marked as pii has value, and if an identifier is provided (whether a @username or a string), checks if any of this pii fields contains the identifier (identifier is optional)
  • is_subscribed?(): checks if you are subscribed to the card
  • parent?(kql_expression): checks if any parent of the card matches the KQL expression
  • children?(kql_expression): checks if any direct child card of the card matches the KQL expression
  • descendants?(kql_expression): checks if any descendant card of the card matches the KQL expression

Object references

Object references can be used to fetch a value from a card or a user. It can be used in arithmetic expressions or in field comparisons.

To get a field value from a card, use the syntax: {#ref:field} (eg. {#123:Estimated hours}).

To get a field value from a user, use the syntax: {@username:field} (eg. {@me:email}). The following fields are available for users: email, username.

Variables


The relationship between the fields used in KQL and the variables:
KQL fields : used to filter your views, create conditions to trigger automations, filter in an automation query and in the queries of your Dashboard views,
Calculate in a formula field (create a value with other values, not necessarily numbers)
The form = >{field}
See the list above for “Special fields”

-Variables: used only in automations

How to write
=> ${variable} or ${card: variable} or ${parent:card:variable}

In the automation, it is possible to combine KQL fields and variables but the reverse is not possible.

The following list of variables is only usable in a
automation in:

  • the filter conditions
  • in the query filter (scan cards)
  • Post a comment
  • Send an email -Fill a field from a formula
  • Create a variable

parent must be added when you configure a rule with parallel sequences, parent refers to the card in question in the main sequence. Please note that this does not necessarily mean the “parent” card compared to the “child” card.

These variables can be combined with special KQL fields to process other cards and not only the one that is in progress (= the one that triggers the action.)

The variables always return text by default.

Depending on the kind of expected result, as_number or as_date must be given first
Exemple: filter cards by comparing two number fields.

as_number(${parent:card:attr_number}) >= {attr_number}

For example, you can act on a set of cards while retrieving information from other cards.

Name of the organization ${organization}

Name of the team

${team}

Workspace name

${workspace:title}

Workspace URL ${workspace:url}

Name of user triggering send action ${author}

Card ID ${card:id}

Card reference (#ref) ${card:ref}

Card URL ${card:url}

Card sharing url ${card:share_url}

Title of the c ard ${card:title}

card request URL ${card:request_url}

Card field ${card:field name}

List of children cards ${children}

With the action “create a variable” you can create your own variable that can be used in an automation rule.

Examples

Find all cards which are assigned to me (using the Assignees field):

@me in assignees

Find all cards assigned to me in the Backlog group:

@me in assignees #backlog

Find all cards which are due tomorrow:

{due date}=tomorrow

Find all cards which have “issue” in their description:

description ~= issue

Find all cards which I’m assigned to or that I’ve created:

@me in assignees or {created by}=@me

Find all cards which are overdue and that have no assignees:

assignees=empty {due date}<today

Find all cards which are more than 3 days old with no description:

description=empty {created at} > ago("3 days")

Find all cards using the Bug model which are more than 1 week old:

model=bug {created at} > ago("1 week")