Administration Guide

Filters and Parameters

On this page

A Filter is a named workflow object bound to exactly one lookup, either a class or the shared user-defined-field table, with its own parameters and an expression. A field's filter can be filled in from a Filter bound to the same lookup. The Filter window builds a condition from rows or as SQL. The same Parameter window declares a Filter's or a Function's parameter, or a Variable. Parameters are given values where a Function is called, where an e-mail notification's parameter is set, and where a Filter is applied.

In the Settings App

Define a Filter

A Filter has:

  • A name.
  • A bound Lookup - the class it filters, chosen from the workflow's lookup objects, or the UDF Lookup entry, which targets the shared user-defined-field table instead of a class.
  • A description.
  • Its own list of parameters, each with a name, a type, and a lookup object for a Reference-type parameter.
  • An expression. When you save a Filter with an expression, the expression is checked against the bound class's table (or the user-defined-field table for UDF Lookup). If the check fails, the Filter is not saved and the focus returns to the expression.

For how to write an expression, see Conditions and Expressions.

Filter a field's lookup

For where a field's Filter: box appears on a form and when it is available, see Action Forms.

A field's Filter: row has one button, F(x). It opens the expression window, where you write only the WHERE clause:

  • The prompt line above the expression reads select * from <lookup table> where.
  • The field list offers a Lookup Fields category with the lookup table's own columns, among them ID (Global ID - GUID) and OID (Object ID - String).
  • The expression can be empty.
  • On OK, the clause is checked against the lookup table, and the accepted text is saved as a single line.

To start from a Filter:

  1. In the expression window, click Select Template....
  2. In Select Filter Template, choose one of the Filters bound to the same lookup. The Filter's expression and description are loaded.
  3. If the expression uses parameters, the Assign Filter Parameters dialog opens with the Filter's description above a Name / Value grid. Assign each parameter a value, and then click OK.

On OK, each parameter in the expression is replaced by its value: a calculated value by its own SQL, a literal value converted and quoted, with an N prefix for a character type.

Assign a Filter's parameters

When a Filter with parameters is applied, the Assign Filter Parameters dialog lists the parameters in a Name / Value grid and keeps OK unavailable until each has a value. An unassigned parameter shows <Double-click to assign value> in red. Double-click a row, or press Enter on it, to open Assign Parameter. A value is either a literal, entered with the parameter's own type editor, or a calculated SQL expression, which is shown underlined once assigned.

Build a condition in the Filter window

The Filter window builds a condition in one of two modes: Design mode, with condition rows, and SQL mode, with a box for a WHERE clause. It opens from a dashboard's activity-widget editor, for the activity filter and for the object filter. The window of a Filter object, described above, is also captioned Filter but has no modes.

  • A toolbar of Add Condition, Add Group, Delete Row, Move Up, and Move Down sits over the condition rows.
  • One button below switches the mode. It reads Switch to SQL Mode while the rows are shown and Switch to Design Mode while the SQL box is shown.
  • Switch to SQL Mode first asks "You are about to enter a potentially dangerous mode, which is recommended for SQL experts only. Continue?" On Yes, the SQL box is filled with the SQL that the current rows translate to. No select * from ... where prompt line is shown above the SQL box in this window.
  • Switch to Design Mode asks "The SQL condition that you have entered in this mode will be lost. Continue?" when you have changed the SQL since switching. Either way, the SQL box is cleared on the return, so nothing typed in SQL mode is kept.
  • On OK, SQL that was never edited is switched back to Design mode without a prompt, so the rows are what is saved.
  • When the window is handed a condition it cannot turn into rows, it reports "Error while building the filter clause. Filter cleared." and leaves the rows empty.
  • In Design mode, while the selected row uses a like or not-like comparison, a legend reads "Use _ to represent any single character" and "Use % to represent any series of characters".

Declare a parameter

The Parameter window declares a Filter's or a Function's parameter, or a Variable. It holds a Parameter group with Name:, Type:, and Reference to: (available only while the type is Reference), and a Default value group with a value box and an expression button. OK stays unavailable until the name is filled in, a type is chosen, and a Reference type has its lookup object. Closing the window with unsaved changes asks whether to save them.

The types on offer depend on what the parameter belongs to:

  • A Filter's parameter offers every type except Lookup, Attachments, Multi-select, Duration, Encrypted, Image, and Script Object.
  • A Function's parameter also offers Attachments and Script Object.
  • A Variable also offers Script Object.

When a caller has already given a Function or e-mail parameter a value that is not calculated, changing the parameter's type is refused with an error, unless the old and new types are convertible. The error names Callers for a Function parameter and Usage for an e-mail parameter.

Assign a parameter's value

The Assign Parameter window opens when you set one value for a Function call, an e-mail notification's parameter, or a Filter's parameter. It shows Parameter: with the name read-only, the explanation Parameter "<name>", and a Value: editor that follows the parameter's type:

  • A lookup picker for a Reference parameter.
  • The form's attachment areas, by name, for an Attachments parameter.
  • A plain editor for any other type.

Each has an expression button for a calculated value.

Bind a step's parameters

In a step's Parameters grid, a parameter the step has not bound shows <Default> when the parameter has a default value, and <Double-click to assign value> in red when it has none. A row bound to an empty value shows <Empty>, and an expression binding shows its text underlined.

  • Double-click a row, or press Enter, to open Assign Parameter.
  • Press Delete or Backspace to remove a binding and return the row to its marker.
  • Press Ctrl+Alt+Q to bind every unbound row to an empty value.
  • Right-click a row for quick values: <Empty>, and False and True for a Logical parameter, then, sorted by name, the program's caller parameters, the action form's fields, and the attachment areas, and then the variables. Each is offered only when it belongs to the same type group as the row and has the same lookup. A caller parameter is bound as %[PAR <name>]%, a form field as %[VIR <name>]%, a variable as %[VAR <name>]%, and an attachment area by its name. A pick other than an attachment or object value is checked like a typed value before it is stored.

In the Assign Parameter window opened from this grid, the expression button opens Build Expression with Set expression for: Parameter "<name>" and an Expression Type: group. Choosing SQL expression, SQL script, or PowerShell script stores the binding as an expression.

At run time, a parameter left unbound takes its default value. A bound SQL script gives the parameter the first column of the first row of its last result set, as a closing SELECT returns it; for a Multi-select parameter, the first column of every row, joined with ;. A Script Object parameter's expression is run as a script object instead.

Insert a parameter as a placeholder

In the Select Placeholder window, a Function's or an e-mail notification's own parameters appear under the Parameters category, and choosing one inserts %[PAR <parameter>]%. For a Reference parameter, the SubItem: pane lists the referenced record's fields, and the placeholder joins the parameter and the field with a dot, as in %[PAR <parameter>.<field>]%. A parameter category shows no Description: pane. An Attachments parameter is left out unless the editor that opened the window allows attachments.

In an item that supplies virtual mapping fields, the category is named Service Fields for a connector item and Form Fields for a Self Service Portal item. Its entries are sorted by name, and choosing one inserts %[VIR <name>]% instead of a PAR placeholder.

For the other placeholders, see Macros Reference.