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. See also the Select Pane topic and the Select Reference topic.
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.
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.
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.
|
Select |
Do the selection, combining the selection with any previous selection as specified by the Action setting. |
Preview |
Show a preview in blue preview color of what the select operation will select. A preview is just a temporary view and does not change the selection.
Press the Preview button to launch a preview, or to update a preview after changing any parameters or controls in the pane. A preview will stay in view until we hide it, or until a layer used to compute the preview is removed or refreshed. We can add layers, pan and zoom, alt-click objects to view attributes, and edit layers without losing the preview.
Closing a preview: In map windows, right-click the blue preview caption bar at the top of the window and choose Hide Preview. In table windows, right-click the blue preview column head and choose Hide Preview. |
Edit Query |
Click to open the Command Window pre-loaded with an SQL query which accomplishes what the template does given all specified settings. |
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.
The quickest way to find records with a text field that contain a given string is to use the Search template. However, we can do the same thing with an expression using the StringContains function.
We will work with a modified 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. 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 choose the Name field.
Double-click the Expression template. Next, to build an expression we press Edit Expression.
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, for an expression of
StringContains([Name], 'B')
Press OK to get back to the Select pane.
To see a preview, we press the Preview button. A preview of records that will be selected is shown using blue preview color. A preview caption appears in the upper right corner of the window with the name of the template being previewed.
For simple selections we might not choose to see a preview, since there is no harm done in making a selection: no data is changed. However, if we are in the middle of a complex sequence of selection actions that involve adding to, subtracting from, intersecting or inverting with existing selections, a preview can be a very big help in seeing what new selection will be combined with the existing selection, before we command the combination.
To make the selection, we press Select,. All rows with a capital B character in their Name field will be selected.
To refine the selection, we can press Edit Expression and change the string between single quotes to 'Be', and then we press OK to return to the Select pane.
Press Preview for a preview. The three records with the string 'Be' in their names will be rendered in blue preview color. Since they already are selected in red color, the three records are shown in a blended color combination of red and blue.
Press Select. When we press Select with an Action mode of replace selection, the prior selection is replaced with the new selection, which now contains only three records.
We can click Edit Expression once more to use the full word 'Beer' in the expression.
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 write expressions of arbitrary complexity using any combination desired of fields, mathematical and other operators, SQL 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 Templates topic.
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.
To see a preview, press the Preview button. We probably would not do a preview for such a simple selection, but instead would go straight to making the selection. See examples of previews for expressions in the Select Pane and Example: Using Select Pane Expressions topics.
The preview shows the three records that will be selected, based on the expression and the less (<) condition.
Press Select to make the selection.
Right away, the system selects all records where Automobiles is less than Buses + Trucks.
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.
Example tables - In this topic we use tables adapted from the ubiquitous nwind example database. To keep the illustrations as small as possible to fit into this documentation we will often take a moment to use the Layers pane to hide the mfd_id field and to hide other fields in the table window that are not being used or are not central to the example.
Example: Using Select Pane Templates - A sequence of actions using Select pane templates.
Example: Using Select Pane Expressions - Make selections by writing snippets of SQL into the Select pane Expression template.
Example: Construct JSON String using Select and Transform - Use the Select and Transform panes to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.