Page last modified 19:24, 15 Jun 2015 by Pat

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. |

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).

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

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. |