Kantree Query Language (KQL)

Kantree includes a powerful query language that lets you filter or search cards, and also compute values from them.

Here is the outline of this article. Click the section you need to get the most out of KQL:

1. Where to use it
2. Filter cards
3. Formula field
4. Referencing fields
5. Combining conditions
6. Advanced usage
7. The difference between KQL fields and variables
8. Examples

Where to use KQL

You can use KQL in several places:

• From a workspace
  • Filter menu, using either the KQL assistant or the editor.
  • Search, by entering your criteria in the KQL editor and pressing Enter to display matching cards.
• From global search
  • Search across all workspaces from the left sidebar.
• From a Dashboard view
  • to filter cards
  • to compute values based on card data
• From a card
  • to compute values from other data on the same card through a Formula field.

Filter cards

KQL itself uses English keywords. You can search for text or any kind of value stored in your card fields. There are also special fields available on every card.

From the Filter menu in a workspace, you can use the KQL assistant for the most common queries.

If the assistant does not cover what you need, switch to the KQL editor.

Different types of filters

Text filters

To search card titles, simply type the keywords you want.

To search for cards that do not match a keyword, switch to the KQL editor and add not before the search term, for example not oranges.

If you need to exclude cards that match a full phrase, wrap the words in double quotes, for example not "oranges berries".

Card reference filters

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

Group / Label field value filters

To search for a card in a group, use # followed by the group name. If the group name contains spaces, ignore them. For example, to search for cards in List 2, use #list2.

Member filters

To search for a card that contains a project member in at least one member field, use @ followed by the username, for example @username. You can use @me to search for your own cards.

Groups, labels, and members containing dots or spaces

Usernames, group names, and label values can contain . or spaces. To search for names with those characters, wrap them in braces: @{user name} and #{List 2}.

Formula field

The Formula field is a card field type that lets you write a KQL formula to compute a value automatically.

In this field, you can use:

• field references, for example {Amount} or {Delivery date}
• KQL functions, for example as_number(), as_date(), today(), or period()
• arithmetic operators +, -, /, and *

Simple example: 3 + 4

Tip: when you reuse the result of a Formula field inside another formula, pay attention to types. By default, the result of a Formula field is usually interpreted as text.

Use as_number() to perform a numeric calculation again, or as_date() to reuse a computed date.

Example: as_number({formula #1}) * as_number({formula #2})

Referencing fields

In your KQL filters and formulas, you can reference card fields using either of these syntaxes: field=value or {field}=value. The first form can only be used when the field name has no spaces or special characters.

Available operators are:

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

When comparing fields, values can be:

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

Special KQL fields

Here is the list of special fields available in all your KQL formulas and filters:

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

• state: the card state (eg. state=completed) and a few aliases

  state refers to Kantree internal states, which you may have mapped to custom workflow states.

  • state=todo: maps to undecided, accepted, or waiting
  • state=doing: maps to in_progress
  • state=finished: maps to completed, closed, or dropped
• started at: the last date when the card entered the in progress state
• finished at: the last date when the card entered one of these states: dropped, completed, or closed
• resolution time: the time elapsed between finished at and started at
• archived: whether the card is archived (yes/no value: archived=yes)
• archived at: archive date (eg. {archived at}>="2025-01-03")
• parent: id of the parent card (usable in queries such as {parent} in {card relationship field #1} or {parent}=#14)
• hlevel: the hierarchy level of the card in the workspace. For example, a sub-card returns 2; to filter sub-cards: hlevel=2
• nb children: number of child cards
• nb comments: number of comments
• last comment at: date of the last comment
• model or type: card model name (eg. model=bug)
• workspace or project: workspace name (eg. workspace="My Workspace")
• org or organization: organization name
• team: team name
• form: form name (if the card was created through a project form)
• form submitted by: either {form submitted by}=@username or {form submitted by}="email" if the form submitter does not have a Kantree account
• shared: whether the card has a public sharing link

You can use the not keyword in front of any of these conditions to search for cards that do not match them, for example not @me in assignees.

Combining conditions

You can use multiple conditions in the same query by placing them one after another. All conditions must be satisfied for a card to match.

For more flexible queries, combine conditions with logical operators:

• and: all conditions must be satisfied
• or: at least one condition must be satisfied

If you want to create an exclusion rule, use the KQL editor with one of these forms:

• and not
• or not

For example, to keep only cards with the commercial offer label but exclude cards assigned to Flora, combine a positive condition with and not.

Advanced usage

Functions

There are 3 kinds of functions:

• functions that return a value (eg. now())
• aggregation functions that compute statistics over a set of values (eg. avg())
• functions used as conditions (eg. date?("week"))

Functions that return a value

• now(): returns the current date and time
• today(): returns today’s date without the time
• period(interval): returns an interval that can be used in date arithmetic, for example now() + period("1 week"). The value is followed by a unit of time such as year, month, week, day, or hour.

• ago(interval): returns the date obtained by subtracting the interval from the current date

  Example: find cards whose due date was 3 days ago or earlier: {due date} <= ago("3 days")

• ahead(interval): returns the date obtained by adding the interval to the current date

  Example: find cards whose due date is 3 days after today: {due date} = ahead("3 days")

• week_start([date]): returns the first day of the week containing the given date. If no parameter is provided, the current week is used.
• week_end([date]): returns the last day of the week containing the given date. If no parameter is provided, the current week is used.
• workweek_start([date]): returns the first working day of the week containing the given date. If no parameter is provided, the current week is used.
• workweek_end([date]): returns the last working day of the week containing the given date. If no parameter is provided, the current week is used.
• week_number([date]): returns the week number
• month_start([date]): returns the first day of the month containing the given date. If no parameter is provided, the current month is used.
• month_end([date]): returns the last day of the month containing the given date. If no parameter is provided, the current month is used.
• month_number([date]): returns the month number
• year_start([date]): returns the first day of the year containing the given date. If no parameter is provided, the current year is used.
• year_end([date]): returns the last day of the year containing the given date. If no parameter is provided, the current year is used.
• year([date]): returns the year
• count_days([start], [stop]): returns the number of days in the interval between start and stop. If no parameters are given, it returns the number of days in the current month.
• count_working_days([start], [stop]): returns the number of working days in the interval between start and stop. If no parameters are given, it returns the number of working days in the current month.
• last_moved_in_group_at(group_name, [type_name]): returns the date when the card was last moved into the given group (type_name is optional and can be used to disambiguate a duplicated attribute in the workspace)
• 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 example as_string({Points})
• as_date(value): converts a value to a date, for example as_date({Text field})

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

  

• date value:start: gets the start date from a date field containing a range, for example {date #2:start}
• date value:end: gets the end date from a date field containing a range, for example {date #2:end}
• as_number(value): converts a value to a number, for example as_number({Text field})
• size(list): returns the number of items in a list, for example size({assignees})
• first(value): returns the first selected value in a field containing a list of values, such as Labels
• substring(node, start, count): returns part of a string, for example substring("hello world", 6, 5) = "world"
• concat(value1, value2, ...): concatenates several values together
• round(value, [decimal]): rounds a number to the chosen decimal place (by default, it returns an integer)

• card(project_title, card_ref): returns the id of a card from another workspace

  Example: using an automation with the Fill a field from a formula action, you can link a card from another workspace through a Card relationship field: card("Workspace name", ${card:Name of the relationship field to fill with the id})

Aggregation functions

• 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 that case, it is the oldest date.

  You can also use min() or max() with card state dates.

  Examples:

  max({updated at})
  min({created at})
  max({started at})
  min({finished at})
  

  Example to filter completed cards with the most recent update:

  query(state=done, max({updated at}))
  

• avg(): returns the average value
• sum(): returns a sum
• sum_logs(category_name, field_name, [user], [from_date], [to_date]): returns the sum of field values from logs in the given category. It can be limited to a user and/or a date range. When summing fields of type Time, the result is returned in seconds.
• count(): returns a count
• count_logs(category_name, [user], [from_date], [to_date]): returns the number of logs in a given category. It can be limited to a user and/or a date range.

Conditional functions

• full_text_search?(text): checks whether the card title or any card field value contains this text
• is_in_group_type?(type_name): checks whether the card belongs to the given group type (type_name is optional and can be used to disambiguate a duplicated attribute in the workspace)
• is_in_active_group?(): checks whether the card is in a group that has start and end dates and whether today falls within that range
• is_in_group?(group_name, type_name): checks whether the card is in group group_name (type_name is optional and can be used to disambiguate a duplicated attribute in the workspace)
• was_in_group?(group_name, type_name): checks whether the card has already been in group_name (type_name is optional and can be used to disambiguate a duplicated attribute in the workspace)
• date?(date): checks whether at least one date field on the card matches the specified date. Examples of valid formats: "05-27-2018" (MM-DD-YYYY), "week", "month", "year", "week+1" for next week, "week-1" for last week, "week-2" for the week before last
• contains_member?(@username): checks whether any Member field on the card contains @username
• is_pii?(identifier): checks whether at least one field marked as PII has a value, and if an identifier is provided (either @username or a string), checks whether at least one PII field contains it
• is_subscribed?(): checks whether you are subscribed to the card
• parent?(kql_expression): checks whether at least one parent of the card matches the given KQL expression
• children?(kql_expression): checks whether at least one direct child card matches the given KQL expression
• descendants?(kql_expression): checks whether at least one descendant card matches the given KQL expression

• relationships?(kql_expression): checks whether at least one card in a Card relationship field matches the given KQL expression

  Example: relationships?(title="Card 10") finds cards that have a Card relationship field containing a link to Card 10.

  If you have several Card relationship fields, you can specify which field to search in like this:

  relationships?(title="Card 2", {name of the Card relationship field})

Object references

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

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

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

Variables

Difference between KQL fields and variables

KQL fields
Used to filter your views, create conditions to trigger automations, filter automation queries, write Dashboard view queries, or calculate values in a Formula field.

Their form: {field}

See the Special KQL fields list above.

Variables
Used only in automations.

Their form: ${variable}, ${card:variable}, or ${parent:card:variable}

In automations, you can combine KQL fields and variables, but the reverse is not possible.

The following variables can only be used in an automation, in:

• condition filters
• query filters (scan cards)
• post a comment
• send an email
• fill a field from a formula
• create a variable

These variables can be combined with special KQL fields so you can work with other cards, not only the one currently being processed (the card that triggered the action).

Example: 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 still retrieving information from other cards.

• Organization name
  ${organization}

• Team name
  ${team}

• Workspace name
  ${workspace:title}

• Workspace URL
  ${workspace:url}

• Name of the user who triggered the action
  ${author}

• Card ID
  ${card:id}

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

• Card URL
  ${card:url}

• Card sharing URL
  ${card:share_url}

• Card title
  ${card:title}

• Card request URL
  ${card:request_url}

• Card field
  ${card:field name}

• List of child cards
  ${children}

• Comment content
  ${comment:message}

  Only works with the trigger Triggered when a new comment is posted.

  Example: reuse the posted comment content in an email action or when posting a comment on another card.

• Username of the comment author
  ${comment:username}

With the create a variable action, you can create your own variable and reuse it in an automation rule.

Examples

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

@me in assignees

Find cards assigned to me in the Backlog group:

@me in assignees #backlog

Find all cards due tomorrow (using the Due date field):

{due date}=tomorrow

Find all cards that contain the word issue in their description:

description ~= issue

Find all cards assigned to me or created by me:

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

Find all overdue cards with no assignees:

assignees=empty {due date}<today

Find all cards older than 3 days with no description:

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

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

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

Examples using dates

Displaying dates in a card

Date field

Add a Date field and customize its title and options. By default, it stores a single date, but you can allow date range selection.

Formula field - Kantree card states

Use a Formula field and add one of the following expressions:

• Creation date: {created at}
• Last updated date: {updated at}
• Archive date: {archived at}

Prefix the expression with as_date() to display it in YYYY-MM-DD format.

Formula fields containing a date can be used in time-based views such as Calendar, Timeline, and Gantt.

Formula field - Automatic date calculation

You can calculate a date automatically by adding a Formula field and using the period("") function.

Based on another field plus or minus a period:

I want to display a review date that must be 7 days before delivery (field: Delivery date).

I want to display a warranty end date that must be 1 year after the delivery date (field: Delivery date).

Formula field - Using start / end dates

If you use a Date field in date range mode, the start of the range corresponds to {Field name:start} and the end corresponds to {Field name:end}.

If you want to extract the start date and end date of a Date field in range mode into separate fields, use Formula fields with {Field name:start} and {Field name:end}.

Conversely, if you have separate start and end date fields and want to turn them into a date range, use a Formula field with as_daterange().

I want to create a Date field with a range whose start corresponds to my card creation date and whose end corresponds to the delivery date.

Filtering cards by a date or between two dates

Date field

The assistant shows the available operators such as Is after and Is before, and lets you choose the period from the calendar.

If you want to search between two dates, add an and operator.

Formula field

Unlike a Date field, when you filter on a Formula field that displays a date, you must type the date manually in the assistant.

The date must be written in this format: YYYY-MM-DD.

If you want to filter between two dates, switch to the KQL editor and use the >, <, and = operators.

To search for cards created between 2025-03-01 and 2025-03-08, use those operators. If you want the bounds to be inclusive, use >= or <=.

Filtering cards by a date plus or minus a period

You can use ahead() to add a period and ago() to subtract one, combined with expressions such as "X days", "X months", or "X years".

To filter cards with a due date at D+10:

To filter cards with a due date at D-1 year:

To filter cards with a due date at D-1 year and later:

Counting items between two fields containing a date or a workload

You can count days between two dates, whether they come from a Date field or a Formula field.

Use a Formula field with count_days() or count_working_days().

I want to count the number of working days between the start of my task and its review date.

I want to count the number of days between a transition from one group to another, where the group represents the status.

Using a date as a condition

Trigger condition for an automation rule

You can use a date as a condition to trigger an automation.

I want a comment to be posted on the card when the Delivery date is later than the Due date.

Automation action — posting a comment:

Create a visual indicator

Use a Formula field with an if() function, or an automation rule with the Fill a field from a formula action.

You can also perform this kind of action with automation rules.

Compared with a Formula field, an automation rule lets you choose when the field is filled and combine multiple actions, such as filling a Date field and a text field.

Conditionally fill a Date field

You can use the if() function to fill a Date field depending on a condition.

When the delivery date is later than the due date, I want the field where I create the formula — Actual due date — to be filled with D+14 relative to the original due date. Otherwise, I want Actual due date to display the original due date unchanged.

if({Delivery date}>{Due date},as_date({Actual due date}+period("14 days")),as_date({Due date}))