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. |
Subqueries
To perform subqueries on related records, you create a separate query and link it to your main query's object with "outer". By default queryable types have a "{type}Query" keyword used to introduce a subquery against that type. You may then filter the subquery using .Where() with an expression in the subquery's namespace, and then collapse it to a single value with an aggregate function.
To aggregate the list of subquery items, you can use one of the following functions (where "expr" is an expression in the subquery's namespace)):
| .Any() | False is the list is empty, true otherwise |
| .Count() | An integer representing the number of elements in the list |
| .Average(expr) | The average of "expr" for every element in the list |
| .Max(expr) | The maximum value of "expr" for every element in the list |
| .Min(expr) | The minimum value of "expr" for every element in the list |
| .Sum(expr) | The total of "expr" for every element in the list |
For instance, let's say that Clients and Jobs are defined, with Job having a Client property, and you are running a query on Clients. To add a subquery on Jobs, you start with JobQuery. Presumably you want jobs for the current client; to achieve that you use JobQuery.Where(outer = Client). Here "outer" is a reference to the Client object outside of the subquery, and Client is the Client property of the Job. Within the Where clause, you are using the Job query language, so any properties will be Job properties and not Client properties. So JobQuery.Where(outer = Client AND StartDate < StartOfDay(-30)) will correspond to a list of jobs for each client where Job.StartDate is more than 30 days ago; JobQuery.Where(outer = Client AND StartDate < StartOfDay(-30)).Any() is a filter for any client who has any job whose StartDate is more than 30 days ago; and JobQuery.Where(outer = Client AND StartDate < StartOfDay(-30)).Sum(ExpectedRevenue) returns the total of Job.ExpectedRevenue for every such job.