Sfairadora

  • Reference Guide

Filter

A Filter allows you to include only those rows in a view that satisfy a certain condition. A filter is defined in the area between the table header and its contents. The area for defining the filter is normally hidden. It can be displayed by clicking the button next to the upper right corner of the table. You can hide the table filter by clicking the same button. Hiding the filter does not mean that the filter is canceled, only the area for its editing is not displayed. When a filter is applied to the table, the image on the button for displaying/hiding the filter changes to .
The filter area looks like a table with controls in every row, allowing you to specify restricting conditions for individual columns. The control for entering the condition has a button on the left, and when you click this button, a menu appears containing available operations that you can use to define the condition. A symbol of the selected operation appears on the button after selection. You can type a parameter into the field to the right of the button to further specify the condition. Available operations depend on the column type. They are listed in the following table:
Operation
Description
All column types
=
equal
Only rows where the value in the column is equal to the parameter value are included in the view.
not equal
Only rows where the value in the column is different from the parameter value are included in the view.
■
data present
Only rows where the value in the column is not missing are included in the view. In case of string columns, an empty string is considered a missing value even though otherwise an empty string is distinguished from a missing value. For all other types this condition makes sense only for optional data items – in this case the filter selects only rows where a value is present.
◻
data not present
An opposite to the data present condition – includes in the view only rows with non-present optional value or with an empty string.
f(x)
expression of variable x is satisfied
Allows you to enter any condition you like. The value being tested is represented by the x variable. The condition is specified in the Enki language. If the result is true (TRUE) for a value in the column, the respective row is included in the view and excluded otherwise. If an error occurs during the evaluation of the expression, the row is also included in the view.
Please note that Sfairadora is case sensitive in data names and therefore a lowercase x letter must be used.
Examples: x mod 2 == 0
for an integer type column, only rows with even number values are included in the view.
(mod … remainder after dividing, == … value comparison).
Note: It is necessary to distinguish between the = operator (one equal sign), which assigns a value, and the == operator (two equal signs), which compares two values. In filter conditions, the == operator must be always used for comparison.
size(x) > 10
for a string type column, only rows with a string exceeding ten characters are included in the view.
(size … number of characters of the string).
no filtering
The value of the column is insignificant for the filter.
Column types that can be ordered (numbers, strings)
>
greater than
A row is included in the view if its value is greater than the parameter value.
greater than or equal
Analogically.
<
less than
Analogically.
less than or equal
Analogically.
≤ ≤
is between
You can enter two parameter values with this condition (the filter control contains two fields side by side). A row is included in the view if its value falls within the range of the values specified by the filter, inclusively.
Strings, texts
has at beginning
A row is included in the view if its value starts with the string specified by the parameter. (For example, if the parameter is ad, the filter condition is satisfied by Adam, Adolph, Adelaide …, but not by Madam.) A value that is equal to the parameter is also included.
contains
A row is included in the view if its value contains the string specified by the parameter – the string can be at the beginning, inside, or at the end. (For example, if the parameter is ad, the filter condition is satisfied by address, blade, bad ….) A value that is equal to the parameter is also included.
has at end
A row is included in the view if its value ends with the string specified by the parameter. (For example, if the parameter is er, the filter condition is satisfied by Writer, summer, Oliver ….) A value that is equal to the parameter is also included.
i
ignore case
If you select an operation from the right column under the Ignore case header, then the condition is not case sensitive with respect to the parameter value entered in the filter.

Combining Filter Conditions

If you specify a filter condition for a single column, the rows are simply filtered according to this condition as described above. However, you will often need to combine several simple conditions into a compound filter condition. To achieve this, you can specify more conditions in one filter definition row. Moreover, you can also enter more rows into the filter definition. Rows are added in the same way as table contents rows using the empty row at the end of the filter definition or by pressing the Insert key. To remove a row, press the Ctrl+Delete keys or click the button in the tool bar. You can also select, move, and copy filter rows just like table rows.
When a filter composed of several conditions is evaluated, the following rules apply:
1.
Conditions specified in the same row of the filter definition must be all satisfied at the same time, otherwise the row is excluded from the view. (That is, conditions in the same row are combined by the “and” logical operator – they are conjunctive.)
2.
At least one of the conditions specified in different rows of the filter definition must be satisfied for the row to be included in the view. In other words, every new row of the filter definition adds to the view those table rows that correspond to this filter row. The final view represents a union of these filter rows. (That is, conditions in different rows are combined by the “or” logical operator – they are disjunctive.)

Filter Parameter Specified by Reference

The menu under filter condition operation further includes the parameter is reference item. This item can be selected independently of the selected filter operation. If the option is selected, the value entered as the operation parameter is not a constant, but is interpreted as a reference to a data item whose value determines the filter condition. Consequently, the inclusion of a row into the view depends not only on the values in the table, but also on the value of this external data item. For example, if you create an integer item named limit in the Data document section, select the “> greater than” filter operation for a particular column, switch on the parameter is reference option, and enter document.data.limit into the operation parameter field, the rows included in the view will change according to the changes of the limit data item. This technique allows you to create a filter parameterized by a variable.
Another example is a table with nested tables (that is, every row of the outer table contains an inner table). Suppose the outer table has a payment column and the inner table has a price column. You wish to display only those rows of the inner tables in which the price is lower than the payment on the outer table row. That is, to filter every inner table according to the value of the respective outer table row. To achieve this, select the “≥ greater than or equal” filter operation for the price column, switch on the parameter is reference option, and enter parent.payment into the operation parameter field. The parent identifier refers to the row of the parent table.
Further possible use is the creation of a filter based on the comparison of values in two columns. Suppose you have a table with income and expense columns and wish to display only those rows where income is lower than expense. To achieve this, select, for example, the “< less than” condition in the income column, switch on the parameter is reference option, and enter expense into the operation parameter field (or a full reference including the object name this.expense; however, the this object name is not necessary). Of course, you could also specify the reverse condition for the expense column.

Condition Defined by an Expression for the Whole Row

If a filter condition has to capture a more complex relationship between column values, you may need to specify it for the whole row (record) and not for several columns individually. To do this, click the button at the right end of the filter definition row. The filter definition row will switch to the mode in which an expression for the whole record can be entered. The row will then contain only one field spanned over the entire row, in which you can type the condition. The condition should be specified in the Enki language.
Suppose you have a table with income1, income2, and expense columns and wish to display only those rows in which total income exceeds expenses. To achieve this, switch the filter definition row to the whole record filter mode and type this expression: income1 + income2 >= expense. (Note: Strictly speaking, the expression should be this.income1 + this.income2 >= this.expense; however, if not specified, the this object name is implicit).

Filter Cancellation

To cancel a filter, select all rows of the filter and press Ctrl+Delete or press the keys repeatedly to cancel the filter row by row.