Select - Expression

The Expression template in the Select pane allows making selections in components by writing an expression that evaluates to a true or false boolean value. The system will parse expressions that are written and will pop open an error dialog if the expression evaluates to a data type other than the chosen data type. The system helps us with an expression builder dialog similar to the Query Builder in the Command Window for SQL.

The Select pane targets the currently active window. When the focus is on a window with more than one layer, to change the layer component on which the Select pane operates, press the Up button in the Select pane to return to the top level, where a new layer can be chosen.

The current build does not do previews. Those will be added in the next build.

As with any selection capability, to use the Select pane Expression template the table must have at least one btree index in the table.

SQL expressions can be used in the Select pane either in the Expression template or to provide a value for some option box where the value is the result of an expression. Exactly the same Expression dialog is used in both cases to help build the expression.

To make selections using the Expression template:

Open a component in a window.
Click on the Select pane. If the component is a map, choose the component desired.
Double-click on the Expression template.
Click on the Edit Expression button to launch the Expression dialog.
Enter the desired expression. Use the expression builder facilities to help build the expression.
Press OK
Choose an Action mode other than replace selection, if desired, and press Select.
To change the expression, press Edit Expression again.

In the illustration at left above we have chosen the Expression template. Pressing the Edit Expression button will launch the Expression dialog, allowing us to compose a new expression or to edit an existing expression that may already be in the pane.

In the illustration at right above, we have chosen the Expression option to provide a value for parameter that is used by the Search template's less (<) operation. Choosing the Expression option also launches the Expression dialog, allowing us to compose a new expression or to edit an existing expression that may already be in that parameter box.

Using the Expression Template

In the Select pane, choose the Expression template to select on the basis of expressions.

Expression template controls:

Expression	Write an SQL expression into this pane, either manually or assisted by the expression builder by double-clicking onto fields or operators/functions in the other panes to add them to the expression.
Filter	Enter text to be matched, case not significant. Allows us to reduce the long list of fields, operators, and functions to show only those which contain the text entered in the Filter box. For example, entering null reduces the list to only those items that have NULL in their names.
Fields	A list of fields and their types from the table used by the component that is the subject of the Select pane.
Operators and Functions	A list of operators and functions that may be used in the expression.
Action	Choose settings by pulling down on the down arrow button. Press the button to alter the selection as specified. Record is a synonym for object. replace selection - Select all records chosen by the template and deselect all other records. This operation takes whatever records would be selected by the template and makes those the only records selected, ignoring anything before. add to selection - Select all records that were previously selected plus in addition select all records that the template chooses. Any records that were previously selected will, of course, remain selected. If there was no prior selection, add to selection is equivalent to replace selection: in both cases the result is what the template would select. intersect with selection - Select only those records that were previously selected and which also are chosen by the template. If there was no prior selection the result would be to select no records. invert with selection - An "anti-intersect" operation: everything except the intersection. Select all records previously selected and all records which also are chosen by the template, except those records which were previously selected and also are chosen by the template. This is equivalent to an add to selection that simultaneously de-selects any intersect with selection records. If there was no prior selection the result of an invert with selection is equivalent to both add to selection and to replace selection since in all three cases the result is what the template chooses. subtract from selection - De-select all records that are chosen by the template.
Edit Query	Click to open the Command Window pre-loaded with an SQL query which accomplishes what the template does given all specified settings.

Expression Dialog

Click the Edit Expression button to launch the Expression dialog, to enter a new expression or to edit an existing expression. The Expression dialog facilitates writing an expression with Query Builder features.

Expression	Write an SQL expression into this pane, either manually or assisted by the expression builder.
Filter	Enter text to be matched, case not significant. The list of fields, operators, and functions will be reduced to show only those items which match the text. For example, entering null reduces the list to only those items that have NULL in their names.
Fields, Operators, Functions	Lists of fields and their types from the table used by the component that is the subject of the Transform pane, along with lists of available SQL operators and functions that may be used in the expression.
Data type	The data type of the targeted field or parameter to which the expression must evaluate. Expressions used for selection must always evaluate to a boolean, since the record in the source field is either selected or not. Writing an expression which does not evaluate to a boolean value will cause an error message to pop open when we press OK.

The Expression dialog provides two panes within the dialog: an upper pane in which we can write expressions, and a lower pane that shows fields in the table, operators, and functions we can use. Just as with the query builder in the Command Window, double-clicking on a field or an operator or function will add it to the expression we are building. Expressions must evaluate to a boolean result to be used for selections.

When we click the OK button to apply edits to an expression, the dialog automatically checks the syntax of the entered expression and pops open an error message on any error. The dialog also automatically checks the type of the entered expression and pops open an error message if the type is incompatible with the expected type. The expected type for the expression is shown in the left bottom corner of the dialog.

The same Expression dialog is used to write expressions used in parameter boxes. Expressions shown in parameter boxes are automatically compressed on the fly to maximize room of available display space, which tends to be limited in value boxes. Comments and unnecessary whitespace are removed, multiple lines are squeezed into a single line, and so forth. Opening the expression will show the original form, with comments and whitespace restored. Expressions that consist of multiple terms are also enclosed in parentheses ( ) to enforce computation order in the query text.

Example

The quickest way to find records with a text field that contain a given string is to use the Search template for text. However, we can do the same thing with an expression using the StringContains function.

We will work with a version of the Products table from the nwind example data set.

With the focus on the open Products table window, the Products table will automatically be the target component for the Select pane. We choose the Name field, but it doesn't matter what field in that table we choose at the top level, since we can write the expression to use whatever fields we like.

We double-click the Expression template to launch it in the Select pane.

In the Expression template, we press the Edit Expression button to launch the expression builder dialog.

The expression builder dialog provides functionality similar to the Query Builder dialog, to help us build expressions with a minimum of keyboarding.

We enter StringCon into the filter box to reduce the long list of fields, operators, and functions to only those which contain that text.

We double-click on the StringContains function to add it to the expression pane.

We then clear the text in the filter box to remove the filter, so we can see all fields, operators and functions.

In the expression pane, we double-click the <string> part of the function sample to highlight <string> and then in the Fields list we double-click on the [Name] field to replace <string> with [Name]. We could have manually keyboarded "[Name]" into the expression but double-clicking often is faster even with short field names.

Avoiding typographical errors is another plus. Typographical errors are not a major risk with short field names such as [Name] but when field names are long or difficult to keyboard we can save much time and avoid many errors by double-clicking the field name to enter it into an expression.

In the expression pane, we double-click the <substring> part of the function sample to highlight it, and then within single quote ' characters we manually enter the text we would like to match.

We enter the capital letter B between single quotes, and press OK.

Back in the Select pane, we press Select, and all rows with a capital B character in their Name field will be selected.

That might be useful, for example, if we want to find all products in our example table that include the substring Beer in their name. We can begin by writing 'B' in the substring section of the function and the pane will preview all products that have a capital B in their name.

To refine the selection, we can press Edit Expression and change the string between single quotes to 'Be'. and then press OK.

Back in the Select pane, when we press Select with an Action mode of replace selection, the prior selection is replaced with the new selection, which now contains fewer records.

We can click Edit Expression once more to use the full word 'Beer' in the expression. Press OK.

Back in the Select pane, when we press Select the resulting selection is just one product.

The above is a very simple example of expressions. We can, of course, write expressions of arbitrary complexity using any combination desired of fields, mathematical and other operators, functions and so on. In particular, we can write expressions to select records (objects) in drawings based on their geometry, which can be formidably complex expressions.

See also the Example: Using Select Pane Expressions topic.

Example

The parameter boxes for templates in the Select pane usually allow a variety of ways to choose the data they use. They usually allow choice of a field, taking the value from whatever is in the specified field, specification of a literal Value, or entry of an SQL Expression.

In this example we will use the table above, which has numeric fields giving the number of households, automobiles, buses and trucks for provinces in Mexico. We will use a typical Select template, the less (<) choice in the Search template, to make selections comparing other numbers to the number of automobiles in each province. We would like to find all provinces in Mexico where the number of Automobiles is less than the sum of Buses plus Trucks.

With the focus on the Mexico Table window, we choose the Select pane and then choose the Automobiles field. We double-click the Search template to choose that template.

In the Search template we choose the less (<) condition. We click on the icon in the Value box and choose Expression from the pull down menu.

Right away, the Expression pane launches.

We enter the expression

[Buses] + [Trucks]

and we press the OK button.

Back in the Select pane, the expression we wrote appears in the Value box. The expression has been compacted to remove unnecessary white space, to show more of long expressions in the relatively small box. If the expression was a long, multiline expression, the first line would remind us what it is. If we like, at any time we can edit the expression by clicking the f() icon in the Value box, to re-launch the Expression dialog.

Press Select.

Right away, the system selects all records where Automobiles is less than Buses + Trucks.

Notes

Regular expressions are different: When using functions involving regular expressions we pass the regular expression as a string argument to the function, surrounded by single quote ' characters and with any backslash \ characters within the regular expression escaped by a backslash character prefix for each, as in \\. See the discussion and examples in the Regular Expressions topic.

Select - Expression

Using the Expression Template

Expression Dialog

Example

Example

Notes

See Also