Search RowCraft tables with JQL

Every column of every RowCraft table can be searched with JQL. Use the searches in Jira's work item search, saved filters, boards, dashboards and automation, like any other JQL.

Two functions

issue in rowcraftField("<table field>", "<column> <operator> <value>", ...)
issue in rowcraftPanel("<panel table>", "<column> <operator> <value>", ...)
issue not in rowcraftField("<table field>", "<conditions>")

The two functions never match each other's tables, so a field table and a panel table may have the same name. Combine them with any other JQL:

project = PUR AND issue in rowcraftField("Purchase items", "Qty > 1") AND issue in rowcraftPanel("Vendor quotes", "Accepted = false")

Write conditions

Each condition is <column> <operator> <value>:

• Column: the column name as shown in the table, in any case. Names with spaces need no quotes: Unit price > 100. Put a name in square brackets when it contains an operator character: [A > B] ~ x.
• Value: numbers as numbers; text, options and dates with or without quotes; several words need no quotes (Item ~ dell laptop); lists in parentheses: IN ("Hardware", "Software").
• Jira functions work in values: currentUser(), membersOf("finance"), startOfMonth(), endOfWeek(), now() and relative dates such as -7d.
• Checkboxes take true or false.

Combine conditions

Several arguments are combined with AND. Inside one argument you can use AND, OR, NOT and parentheses, as in JQL (NOT binds tighter than AND, and AND tighter than OR):

issue in rowcraftField("Purchase items", "Category = Hardware", "Unit price >= 1000")
issue in rowcraftField("Purchase items", "Category = Hardware AND Unit price >= 1000")
issue in rowcraftField("Purchase items", "Category = Hardware OR Unit price >= 5000")
issue in rowcraftField("Purchase items", "(Category = Hardware OR Category = Software) AND NOT Owner = currentUser()")

The first two searches are the same. Up to 10 conditions fit in one call.

Operators by column type

Each column type accepts the same operators as the Jira field it resembles.

• Text is searched by word, like Jira's text fields: ~ lap does not find laptop, but ~ lap* does. A quoted phrase finds rows that contain all its words, in any order.
• Options match exactly, including case: = Office does not find Office supplies.
• For people, use currentUser(), membersOf() or account IDs; display names cannot be searched (as with Jira's own user fields).

Column statistics

Compare one number for the whole column instead of "any row":

issue in rowcraftField("Purchase items", "SUM(Amount) > 10000")
issue in rowcraftField("Purchase items", "AVG(Unit price) >= 100")
issue in rowcraftField("Expense lines", "COUNT(Receipt attached) >= 3")
issue not in rowcraftField("Purchase items", "MAX(Unit price) >= 7000")

The statistic is always the one you write. It does not depend on what the column's totals row shows.

Total and RowCount

Two names stand for the whole table (English, any case):

• Total is the sum of the column chosen as Column behind Total in JQL in Table settings, the same as SUM(that column). Without such a column the search reports an error; use SUM(column) instead.
• RowCount is the number of rows.

issue in rowcraftField("Purchase items", "Total > 10000")
issue in rowcraftField("Purchase items", "RowCount >= 3")

Amount > 10000 means "a row above 10,000"; Total > 10000 means "the rows add up to more than 10,000". A column that is really called Total takes precedence.

not in and NOT

not in returns the exact opposite of in, including work items whose table, or that column, is empty. NOT inside the conditions does the same for one condition.

Range comparisons on a column (>, >=, <, <=) cannot be negated: because a table has many rows, "no row is 7,000 or more" is not the same as "some row is below 7,000". Write the opposite condition with in, or use a statistic, which allows ranges with not in:

issue in rowcraftField("Purchase items", "Unit price < 7000")
issue not in rowcraftField("Purchase items", "MAX(Unit price) >= 7000")

The second search finds work items without any row at 7,000 or more, including those with no table.

Name the table

If two tables have the same name, use the ID.

Panel tables: rowcraftPanel()

rowcraftPanel() takes the same conditions as rowcraftField():

issue in rowcraftPanel("Onboarding tasks", "Done = false", "Owner = currentUser()")

• Rows can be found a few seconds after they are saved.
• Up to 10 panel tables can be searched on one work item.
• People columns of panel tables take currentUser() and account IDs, but not membersOf(): Jira does not allow it there. To search by group, use a table field.

Search properties of table fields

Table fields also have search properties that you can use directly in JQL, for example in tools that only offer field paths:

Note that the field name and property must be inside one pair of quotes. Column values are stored in numbered properties ("Purchase items.Number2", "Purchase items.Option1" …) that depend on the order in which columns were created; rowcraftField() translates column names into them for you, so prefer the function.

When a search shows an error

For every app-provided JQL function, Jira shows one general message when something is wrong: JQL is invalid. Validation of field by customJqlFunctionExpansion failed. Check:

• the table name, and that you used rowcraftField() for a field and rowcraftPanel() for a panel table;
• the column names, operators (see the table above) and values (quotes around text with special characters, lists in parentheses);
• that you did not negate a range on a column;
• that you have access to the RowCraft app.

The search helper validates the search as you type and shows Jira's message under the JQL box.

When changes reach searches

• New and changed rows are found as soon as the work item is saved (panel tables: a few seconds after saving).
• After an administrator saves the column designer, renames a table field or changes its contexts, RowCraft refreshes its JQL functions immediately, so saved filters keep working.
• After a formula or the Total column changes, existing work items are recalculated in the background, usually within seconds to minutes.
• Searches always respect Jira permissions: work items you cannot browse never appear.