Filter Language

Some pieces of Pleasant software have a filtering language that allows you to perform sophisticated text based filtering of data. This document describes the common parts of this filter language that are typically available.

The list here is specified in order of precedence. So, for example, you can see that multiplication will be performed before addition in expressions. For mathematical expressions, the standard "BEDMAS" rules are followed.

Construct Purpose
( <expr> ) Parentheses can be used to group parts of the expression to change the order of operations.

field

function()

function(arg1)

function(arg1, arg2)

Filters often have built-in fields or functions. Please see your project-specific documentation for a list. Note that the language treats no-argument functions the same as fields, so these are equivalent:

field

field()

and these are equivalent:

function()

function

Some filter languages may automatically convert unknown fields into strings. Please see your project-specific documentation for information on this behavior.

TRUE

FALSE

Boolean literals

'string'

"string"

Text (string) literals. If you want to include a ' in a string quoted with ', use two. For example: 'Bob''s House'

The double-quoted string works exactly the same way, so you can do: "He said ""Bob is cool."""

It's often convenient to use the other form if you have a special character. For example: "Bob's House"

10

43.3

Numeric literals

EMPTY

NULL

Represents an empty or unset value.
NOT <boolean expr> Inverts the boolean value of the expression. If it was previously matching <expr>, it won't anymore. If it was previously not a match for <expr>, it will now be a match.
- <numeric expr> Negates the numeric value of <expr>.

<numeric expr> * <numeric expr>

<numeric expr> / <numeric expr>

<numeric expr> % <numeric expr>

Multiply, divide, or modulus (remainder).

<numeric expr> + <numeric expr>

<numeric expr> - <numeric expr>

Addition or subtraction.

<expr> = <expr>

<expr> == <expr>

 

<expr> <> <expr>

<expr> != <expr>

 

<expr> > <expr>

 

<expr> < <expr>

 

<expr> >= <expr> or <expr> => <expr>

 

<expr> <= <expr> or <expr> =< <expr>

Comparisons for equals, not equals, greater than, less than, greater than or equal, and less than or equal.
<expr a> IN ( <expr b>, <expr c>, <expr d> )

Checking whether a value is equal to any element in a list of values. Is equivalent to:

<expr a> = <expr b> OR <expr a> = <expr c> OR <expr a> = <expr d>

<expr a> NOT IN ( <expr b>, <expr c>, <expr d> )

The negation of the IN operator. The example statement is equivalent to:

NOT ( <expr a> = <expr b> OR <expr a> = <expr c> OR <expr a> = <expr d> )

<boolean expr> AND <boolean expr> Is true only if both the left and right operands are true.
<boolean expr> OR <boolean expr> Is true if either the left and right operands are true.

 

Empty Value Handling (null handling)

If you want to check if a field is unset, you can use one of these:

MyField = empty

MyField = null

If you want to check if a field has any value set, you can use one of these:

MyField != empty

MyField != null

 

You can safely use null or empty in equality comparisons. This is commonly expected behavior but it doesn't match the typical behavior of languages such as SQL or JIRA's JQL. For example, you can write:

MyField != 'Bob'

and it will return items where MyField is set to a value that's not Bob AS WELL AS items where MyField is empty (unset).

Date Formats

The typical date format will accept strings such as 'YYYY-MM-DD' to compare against date fields.

Date Handling Functions

If your filter language has date support enabled these functions should be available:

StartOfDay

StartOfDay(<number of days offset>)

Returns the start of the current day (midnight) or if an offset is present, midnight of the day before or after today. For example: StartOfDay(0) is equivalent to StartOfDay() and StartOfDay(-1) will be yesterday morning's midnight.

StartOfDayOfWeek(<day of week>)

StartOfDayOfWeek(<day of week>, <number of weeks offset>)

Behaves similarly to StartOfDay but returns the start of the specified day of the week instead. Use the full name of the day, such as "Monday" or "Wednesday".

StartOfMonth

StartOfMonth(<number of months offset>)

Behaves similarly to StartOfDay but returns midnight at the start of the month.

StartOfYear

StartOfYear(<number of years offset>)

Behaves similarly to StartOfDay but returns midnight at the start of the year.
Tag page
You must login to post a comment.