Filtering by formula

While filtering views based on expressions offers a lot of flexibility, it does not cover all possible filtering scenarios. For this reason, Platform offers filtering by a formula. To filter a view by a formula, click the drop-down menu next to Filter Conditions and select Formula:

The page displays the Formula Helper, where you can define the formula.

You write a filter formula using the syntax of a SQL WHERE clause. The formula may include any stored fields (i.e. non-dynamically computed fields), SQL operands (including LIKE, IN, AND, OR, and NOT), helpers (such as the current user ID, customer ID, portal user ID, etc.) and the following special functions:

• #YEAR(date) returns the date's year as an integer
• #MONTH(date) returns the date's month as an integer in range 1 - 12
• #DAY(date) returns the date's day of month as an integer
• #IF(expr, val1, val2) returns val1 if expr evaluates to true, val2 otherwise.

When using an object's field in a formula, you must use the field's integration name. The Formula Helper area can help you find the integration name. As shown below, if you select the field from the Select Merge Token field, the Copy Merge Token field displays its integration name. Copy this name and use it in the formula. The following simple example of a filter formula filters last names in a room reservation application to only view reservations where the last name is Beauchamp:

A filter formula can include tokens derived from the current date as shown above.