Building Custom SQL Filtering Expressions

You can create a custom SQL filtering expression:

  1. You can build a filter using one of the following options:
    • In the Action Form dialog box, double-click the field definition. The Form Field dialog box opens.
    • In the Workflow and Business Logic > [Object Class] > Fields section, double-click the field definition on a Full Form. The Field Properties dialog box opens.
  2. Next to the Filter field, click the Build Expression button to bring up the Build Expression dialog box.
  3. Type a filtering expression using the syntax of a SQL “WHERE” clause (without the word WHERE).

    INFO: Using the area below the text box, you can insert a placeholder for a field value or a macro. For details, see Inserting Placeholders.

    NOTE: In addition to Object Fields and other categories that usually appear in the Build Expression dialog box, you can select an item from the Lookup Fields category. This Lookup Fields category is available only when you create filter expressions. It contains field names (or SQL column names) for a referenced object class. To insert a placeholder into your SQL expression, select Lookup Fields -> [FieldName] and click Insert.

  4. Click OK to close the Build Expression dialog box. Alloy Navigator analyzes your input. If your SQL expression is invalid, an error message appears.

The following example demonstrates a SQL filtering expression in the Incident workflow.

When a technician performs the “Create Work Order” Action, the Assignee drop-down list contains only members of the selected Assignee Group.

To view a SQL filtering expression for Assignee:

  1. In the Action Form dialog box for the “Create Work Order” Incident Action, double-click the definition of the Assignee field. If the Form is read-only, click Make Custom to be able to view filters.
  2. Next to the Filter field, click the Build Expression button.
  3. View the following filtering expression:

    (%[VIR Assignee Group]% IS NULL) OR ([ID] in (select [Person_ID] from Group_Members where [Group_ID]=%[VIR Assignee Group]%) )

    This filter ensures that the Assignee drop-down list on the Form would contain members of the selected Assignee Group (or all technicians, if the Assignee Group is NULL), as illustrated below. Click OK to close the Build Expression dialog box.