Example: Using Select Pane Expressions

This example shows how to use the Select pane Expression template to make selections in various ways.

 

We will work with the Customers table from the ubiquitous Nwind example database.

 

 

Open the Customers table.  We have used the Layers pane to turn off some of the fields.

 

With the focus on the open Customers table, in the Select pane we choose the Country field.  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 helps us build expressions with a minimum of keyboarding.    The lower pane provides access to fields and elements of SQL, that we can double-click to add to the expression, similar to how the Query Builder works in Command Windows.   Note that the lower pane shows all fields, including those that have been turned off in the Layers pane from display in the table window.

 

For example, in the lower pane we can double-click [Country] to add that to the expression pane at the current cursor position, without having either to keyboard the name of the field or the square [ ] brackets that surround it.   We can then keyboard the rest of the expression, to select those records where the country name is Germany.   The expression is:

 

[Country] = 'Germany'

 

Press OK to return to the Select pane.

 

 

To see a preview of what will be selected, we can press the Preview button.

 

 

Manifold will immediately evaluate the expression to show a preview of what will be selected, in blue preview color in the table.  The virtual preview column header that appears in blue preview color in the column header section indicates the preview is for a use of the Expression template.

 

We can edit the expression by pressing the Edit Expression button again to re-launch the expression builder dialog again.

 

 

In the expression builder dialog, we alter the SQL for the expression as follows:

 

[Country] = 'Germany' OR

  [Country] = 'Mexico'

 

We can use indents and whitespace as we like to write a more legible and pleasing expression.

 

Press OK to exit the expression builder and to return to the Select pane.

 

 

Press Preview to update the preview in the table.

 

 

Manifold will show an updated blue preview in the table.

 

 

If we like what we see in the previews, and we are ready to apply the expression and to make a selection, we press Select.

 

 

The new selection is immediately applied, appearing in red selection color in the table.  The preview disappears, since we have made a selection.

 

The expression we used remains loaded in the Select pane, so we can modify that expression and then use whatever is selected by the new expression in combination with the existing selection.

 

Suppose, for example, we press the Edit Expression button, and then in the expression builder we edit the expression to return to the original expression we entered:

 

[Country] = 'Germany'

 

We press OK to return to the Select pane:

 

 

Press Preview.

 

 

The records selected by that expression will be previewed.  Since the two records that would be selected by the expression are already selected, they will be previewed in a blend of blue preview color and red selection color.

 

 

If we would like to remove those records from the current selection in the table, we can switch the Action option to subtract from selection.  

 

Press Select.

 

 

That clears the preview, and removes from the selection records which match the expression [Country] = 'Germany'.

 

The example above uses very simple SQL expressions.  We can write virtually any SQL expression into the Expression tab, including expressions which use functions, as shown in the Select Pane topic.

See Also

Selection

 

Select Pane

 

Example: Using Select Pane Templates - A sequence of actions using Select pane templates.

 

Example: Construct JSON String using Select and Transform - Use the  Select pane and Transform pane 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.