Extra Filters for Selection Boxes

How to define extra filters for selection boxes in the template manager.
This can shorten the length of the selection list and make it easier to find the desired value.

Extra Filters for Select Inputs

Tips and Tricks

You can filter the records of a linked table or a value list by entering filter conditions.
You can do this for any column of the model.
Define filter conditions using the extraFilter textfield.

A filter condition for a column can look like this: <column name>=<value>
(<column name> and <value> are placeholders here)
You can use different operators than = - just as in the "Create Filter" dialog. You can use >, <, >=, <=, !=, ==.
For text columns you can use the same search terms as described in the "Create Filter" dialog form.
Multiple filter conditions can be combined with the & character. This is a logical AND operation.

See General Introduction

Restrictions:

Since the filters are used to create an SQL filter expression, mDIS "pseudo columns" cannot be searched or filtered because their values are calculated on-the-fly. (These values are not stored in any database).
You cannot use any of the following characters in the <value> part of the filter: &, {, }.
Watch out when using characters that have a special meaning in Regular Expressions, like (, ), |, [, ], ., *, +.
You could use operator == to check for strict equality. This trick avoids a search with Regular Expressions. You can also use "\" to "escape" the characters. See below in "(Easy)" Example.
The <value> part of the filter cannot be syntactically validated. If there is a syntax error in the expression, you may see an Error in extraFilter in the console of your browser (hit key F12 to open the developer tools).

Usage of the `extraFilter` textfield

In the textField extraFilter: enter the following:

display=<value>

In the <value> part of a filter condition, you can use placeholders that will be replaced with values from the current data in the form. If the contains a part enclosed in braces "{ .... }", this part will be evaluated as a JavaScript block/expression. In this expression, you can use "self.<columnName>" to get the value of this column in the current form. The calculated value of the expression will replace the part in {braces}.
Using this mechanism, you can create coupled selection boxes.
See below, in the "(Hard)" Example.

Example: Filtered value list

(Easy)

Consider a value list ROCK_TYPE containing the name of the rock type plus the abbreviation of the rock type in parenthesis:

Basalt            (IGN-PLUT)
Gabbro            (IGN-PLUT)
Lherzolite        (IGN-PLUT)
Listvenite        (META)
Metabasalt        (IGN-PLUT)
Metagabbro        (IGN-PLUT)
Metasediment      (SED)
Ultramafic schist (META)

1
2
3
4
5
6
7
8

To perform a case-sensitive search for rock types of class META (= Metamorphic rocks category), select the input type select in the template manager, choose the value list ROCK_TYPE.

In the textField extraFilter: enter the following:

display=META # assuming case-sensitive search

This may work, but the result can be inexact:

Listvenite        (META)
Metabasalt        (IGN-PLUT)  # unwanted here
Metagabbro        (IGN-PLUT)  # unwanted here
Metasediment      (SED)       # unwanted here
Ultramafic schist (META)

1
2
3
4
5

Why?

Although display=... correctly defines a filter condition for the column display of the table dis_list_item (because we are using a Value List), this is not exact enough.

This filter expression display=META will populate the selection list with any string containing "META" anywhere in the string. Due to the operator = and because the column dis_list_item.display is a string column, the value is searched as a Regular Expression. So we also match the values Metabasalt (IGN-PLUT), Metagabbro (IGN-PLUT) and Metasediment (SED). These values are undesirable here.

We should make sure that we only find records mentioning (META) in parentheses. Thus we add a parenthesis before the value.
However, we have to escape that (with character \), because the open-parentheses character ( has a special meaning in Regular Expressions.
So the final extraFilter is:

display=$META$

This is the correct way to filter strings containing parentheses such as ( or ) .

Example: Coupled value lists

(Harder)

Consider two value lists: ROCK_CLASS and ROCK_TYPE.

Values of ROCK_CLASS have to be modified, such that they contain the content of textfield remark. So

Sediment rocks would be changed to Sediment rocks (SED).
Alterated rocks (ALT),
Igneous-Plutonic rocks (IGN-PLUT),
Metamorphic rocks (META),
Undefined (UND).

Values of ROCK_TYPE contain values like

Gabbro (IGN-PLUT),
Limestone (SED).
and many many more.

The task is to extract the part in parentheses from the value of ROCK_CLASS and use it as a filter condition for ROCK_TYPE.

Solution

In a model, we have two columns rock_class and rock_type.
In the corresponding form, I define the field rock_class as an input type select and choose the value list ROCK_CLASS. For the field rock_type, I also set the input type to select and choose the value list ROCK_TYPE. Here I enter the following JavaScript snippet in Extra Filter:

display={ self.rock_class ? '\\(' + self.rock_class.replace(/^.+\((.+)\).*$/, '$1') : null }`

In the JavaScript block starting with { and ending with }, we first check if self.rock_class, the current value of the field rock_class in the form, has some value. Otherwise we return null.

From the current value of the field rock_class, we have to extract only the part in the parentheses. This is been done by self.rock_class.replace(/^.+$(.+)$.*$/, '$1'). The value is replaced with a Regular Expression. To match the parentheses in the value, we have to escape that character, because the parentheses character in Regular Expressions has the special meaning of a "capture group". That "group" is simply the match that was found. If Sediment rocks (SED) is selected in the field "rock_class", the result of this replacement is "SED".

(TBC)