A Lookup Filter is an optional feature in List Selection and Multi Lookup fields. In both types of field you specify an app and form to lookup, and the field’s list shows the records that match the app and form. A Lookup Filter is a way to further refine what is shown in the displayed list, to those records where one or more fields on the record also match specified values. You can have multiple filters active at the same time.
Lookup Filters can be defined in the following places:
- Step one of the Multi Lookup creation wizard
- The properties panel of a Multi Lookup field: Properties > Options > Lookup Filters
- The properties panel of a List Selection field: Properties > Options
|Multi Lookup Wizard Step 1|
|Multi Lookup field properties|
|List Selection field properties|
Setting a Filter Value
There are two main steps to adding a filter: Select the field on the lookup form to compare with, along with the comparison operator, and then you define what you are comparing against.
|Adding a Filter – Step 1|
|Click on this icon to add a new filter. You can have as many concurrent filters as you need. If you add more than one filter, then all filter conditions must be met simultaneously. (They are combined with a logical AND.)|
|Delete: Click on this icon to delete the current filter.|
|Field||Select the field on the lookup form you would like to filter based upon: i.e. Surname, Department, etc.|
|Operator||Select the desired logical operator for the comparison.
The options vary depending on the data type of the field selected in Field. The options are:For Text type fields and pick from list type fields (Radio Button, Checkbox, List Selection, Multi Lookup):
For Date type fields:
Equals and does not equal require the entire compared value to match/not match: e.g. “Department” does not match “IT Department” when using equals as the whole value is not the same.
Contains and does not contain just searches for the specified text in the comparison value to see if it is there at all. The entire compared value is not required to match: e.g. “Department” does match “IT Department” when using contains.
|Adding a Filter – Step 2|
|Text||Enter the actual value to compare against: e.g. if you were filtering a lookup so it only showed records where the project area was ‘IT’, then simply type ‘IT’ in this field.|
|Use Local Field||This option allows you to do the comparison against the value stored in an field on the current record, instead of against a fixed value as in the Text option above.
If you click on Use Local Field, the Text field is replaced by a is
|Adding a Filter – Step 2 Local Field option|
|Select Field from this Form||Select the field on the current form that you would like to do the comparison against.|
|Use Value||This option allows you to cancel the Use Local Field option and go back to doing the comparison against a fixed value supplied in the Text field, as shown above.
If you click on Use Value, the Select Field from this Form is replaced by the Text field, as before.