Lookup Values window

Updated in 2022.1

A virtual field of Lookup type appears on the Action Form as a drop-down list, where the user can choose one of the available values. Use the Lookup Values window to create or modify the list of values.

These types of sources for the lookup list are available:

• Static list of values (Radio Buttons) - a list of static values, which you specify when configuring the virtual field. The field appears on the Form as a group of radio buttons, where the field name is the group label.

• Static list of values (Drop-Down) - a list of static values, which you specify when configuring the virtual field. The field appears on the Form as a drop-down list.

• Dynamic list based on custom SQL - a list of dynamic values based on a custom SQL query that you create when configuring the virtual field. When the user opens the Action Form, the SQL query retrieves the list of values from the Alloy Navigator database. The field appears on the Form as a drop-down list.

  This type gives you the maximum flexibility and is especially useful when you need to create a complex filter or to display a list of calculated values or a list of values from an external data source such as another database hosted by the same SQL Server (make sure that the users can access that database under their accounts) or a text file that can be accessed using SQL Server API methods (make sure that the SQL Server startup account has the access permissions to that resource) .

The Lookup Values window contains the following fields and controls:

• Source - the type of the lookup lists source. The selected value defines the other options available in the window.

• Static list of values (Radio Buttons / Drop-Down):

  • New - brings up the Add Lookup Value window, where you can type in a static lookup value. Each value consists of a pair of elements: the value itself and the display text. Display text is the option that the user will see in the drop-down list on the Action Form. By default, the display text matches the value, but you can specify a different text.

  • Edit - brings up the Add Lookup Value window, where you can edit the existing value.

  • Delete - deletes the selected list value.

  • Move Up, Move Down - moves the selected value up or down in the list.

• Dynamic list based on custom SQL - creates a list of dynamic values.

  • Text field - the custom SQL query. These two types of SQL queries are supported:

    • An SQL query that returns two columns. The first column contains field values and the second column contains display text (options that will be displayed in the drop-down list on the Action Form).

      For example, to display a list of persons to be notified via e-mail, use the following query:

      select [Primary_Email], [Full_Name] from [Persons] where [Primary_Email] is not null

      The virtual field will have the primary e-mail address of the selected person as its value, and you can use this address in an E-mail Notification.

    • An SQL query that returns one column. This column's values will be used as both the display options on the Action Form and the field values. Single-column queries, while legitimate, offer little practical value.

    NOTE: If your SQL query returns more than two columns, the lookup field will use only the first two columns. The subsequent columns will be truncated.

  • Category, Item, Placeholder, Insert - these controls allow you to choose a placeholder that will be replaced with the actual field value or macro value at runtime.

    Select a category, select a item, view the placeholder it inserts, and click Insert to inset it in your custom SQL query in the text field above.

Related information:

Configuring Forms