Select Dialog

The Edit - Select dialog combines a number of functions related to selection into a single dialog.  The Select dialog allows making selections within open windows, such as table windows.  It also allows us to transfer selections between open windows that are based on the same table.  tech_ravi_sm.png

 

Important: As with any selection capability, to use the Select dialog the table must have at least one btree index in the table. 

 

Please read the Selection topic before proceeding with this topic.

 

To make selections using the Select dialog:

 

  1. Open a table in a table window..
  2. Press Edit - Select or Ctrl-Q to open the Select dialog for that table window.
  3. Using a template, expression, a saved selection or a selection from another window, put together a proposed selection.  
  4. That proposed selection will be previewed in the table window using blue preview color.
  5. Choose how the selection will be combined with any previously existing selection in the table window by choosing a mode in the pull down menu for the command button.
  6. Press the command button.

 

With the focus on a table, open the Select dialog by pressing Ctrl-Q or by choosing Edit - Select.  The Select dialog will open with the Template tab by default.

 

il_select_dlg_tabs.png

 

The Select dialog has four tabs to access capabilities it provides:

 

 

At the bottom of the dialog is a command button that specifies how the selection the dialog creates should be applied to any existing selection in the table window.   By default the command button is set to Replace Selection.  

 

eg_seldlgwin01_17.png

 

The pull-down menu allows us to specify the command button's use of other selection modes.   See the discussion below for more detailed information on selection modes in the command button.

Dynamic Updating

The Select dialog dynamically updates itself given any changes in the table's schema.  For example, if while a Select dialog is open we choose Edit - Schema and then add a new boolean field to the table the available fields in the Expression tab's expression builder will be updated with the new field and it will also appear in the Saved tab's list.  If we delete all btree indexes from the table the Select dialog will automatically close, since it cannot work with tables that do not have at least one btree index.

Simultaneous Use of Other Facilities

When we have the focus on an open table window and we open the Select dialog with an Edit - Select command, that Select dialog works with that particular window.  At the same time we can open other windows and do other work with Manifold, going back and forth between the other work and the Select dialog.    

 

We could even open another table window for that same table and open a second Select  dialog for that second window.    We could go back and forth between the two windows and their Select dialogs and completely independently of each other preview different templates or expressions to see what selections they would make.  We can do that with as many windows and open Select dialogs as we want.

 

See the step by step example below for more on multiple windows with multiple select dialogs.

Template Tab

The Template tab provides a list of pre-built selection commands, called templates.  Clicking on a template will highlight it and will open combo boxes that allow us to choose fields and to specify values we would like to use for that template.   

 

To make selections using the Template tab:

 

  1. Open a table in a table window.
  2. Open the Select dialog with Edit - Select or Ctrl-Q.
  3. Click on the Template tab in the Select dialog if not already open.
  4. Click on the name of a template in the list to highlight it.
  5. In the Value box, choose the name of the field to use.
  6. For other boxes, choose another field to use as the argument or enter a number or text for the command to use.
  7. The dialog will preview that selection in the table in blue preview color.
  8. Apply the previewed selection to the table by clicking the command button in the desired mode.

 

Combo boxes generally come pre-loaded with a pull down list of fields in the table and by default contain the name of the first field in the table by alphabetical order.  This makes it easy to choose any field in the table to use with a template without having to keyboard the name of the field.  Combo boxes also generally allow us to manually enter a specific value, such as a field name or a number.  

 

All templates open a Value combo box that allows us to specify the name of a field in the table with which the template works.   The Is not Null and Is Null templates open only the Value combo box while other templates will open additional combo boxes to specify other parameters the template requires.

 

For example, the Equal template will open a Value and Compare to combo boxes. We choose the field name in the Value box and then in the Compare to combo box we can either choose the name of a different field or enter a fixed number.

 

Consider a table of products adapted from the ubiquitous nwind sample database.

 

eg_seldlg_template01_01.png

 

To select records where the Stock is Equal to the Units On Order we launch the Select dialog and click on the Equal template. In the Value box we choose the Stock field from the pull down list of fields and in the Compare to box we choose the Units on Order field.

 

eg_seldlg_template01_02.png

 

We can enter numbers into the combo boxes as well.  If we want to select all records where the Stock field is 15 we enter the number 15 into the Compare to box.

 

eg_seldlg_template01_03.png

 

We can enter text strings into the combo boxes.   If we want to select all records where the Name field is Dharamsala Tea we choose the Name field in the Value box and then enter 'Dharamsala Tea' into the Compare to box.   Note that the text string is within single quote ' characters.

 

The Text Contains, Text Ends with and Text Starts with templates also come in Intl or international versions that provide a Language combo box used to choose a collation supported by Manifold.  The Intl forms of those templates also provide checkboxes to Ignore case, Ignore accent and Ignore symbols.   Those options can be very useful in languages where sometimes accents and symbols are used and sometimes not.   

 

For example, it is very common in French to encounter place names which in their official forms may use accents but which in technical uses such as databases do not.  The town of Vendôme in the Loir-et-Cher department of France, for example, is properly spelled with a circumflex accent (the small chevron mark) over the letter o. Nonetheless the name of the town is frequently spelled Vendome without a circumflex accent on web sites and in numerous address databases and other usages, both in France as well as internationally.   To select both Vendôme and Vendome we would check the Ignore accent box for templates that offer it.

 

The Like template utilizes the LIKE operator in SQL to match given patterns while the Text Matches Regexp allows us to specify a regular expression as the pattern to be matched.

 

Note that even though combo boxes for templates allow us to enter a numeric value instead of choosing a field does not mean that it makes sense to do so for all templates.

 

For example, the Is NULL template is used to select all records that have a NULL value.  If we click on the Is Null template a Value combo box opens up, pre-loaded with the name of the first field in the table by alphabetical order.   We can choose the name of any field in the table from the pull-down list of fields available in the Value box and the dialog will preview a selection consisting of all records for which that field contains a NULL value.

 

Because combo boxes allow us to enter a number as well, instead of choosing a field name in the Value combo box for Is Null we could have entered a number such as the number 3.  That is possible but it doesn't make sense to do so, since in that case we would be asking the Select dialog to select all records for which the number 3 is NULL.   A constant number like 3, of course, is never NULL so the dialog would select no records.  tech_lars_sm.png

 

Important: When using the template Text Matches Regexp we enter a regular expression as a string into the Pattern box, surrounded by single quote ' characters and with any backslash characters within the regular expression escaped by a backslash character prefix for each.   See the discussion and examples in the Regular Expressions topic.

 

Expression Tab

Clicking the Expression tab in the Select dialog allows us to make selections with the dialog based on SQL expressions that we can build with assistance similar to the Query Builder in the Command Window for SQL.

 

eg_seldlg_expression01_01.png

 

The Expression tab provides three panes within the dialog: an upper pane in which we can write expressions, a middle pane that shows fields in the table and a lower pane that shows 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.

 

To make selections using the Expression tab:

 

  1. Open a table in a table window.
  2. Open the Select dialog with Edit - Select or Ctrl-Q.
  3. Click on the Expression tab in the Select dialog.
  4. Double-click on the names of operators or functions to add them to the expression.
  5. Highlight arguments or other elements of the expression being built and double-click on fields in the field list to add them to the expression.
  6. The dialog will preview the selection made by that expression in the table in blue preview color.
  7. Apply the previewed selection to the table by clicking the command button in the desired mode.

 

For example, if we want to use the StringContains function we double-click on the function to add it to the expression pane.  

 

eg_seldlg_expression01_02.png

 

We then highlight the <string> part of the function syntax and double-click on the [Name] field to replace <string> with [Name].

 

eg_seldlg_expression01_03.png

 

We then highlight the <substring> part of the function syntax and then add the substring we would like to match within single quote ' characters.  

 

If we do this by first adding two single quote characters, that is, beginning with an empty string and start writing the string we can see how the dialog will dynamically begin previewing in blue preview color the selection it would make.   

 

eg_seldlg_expression01_04.png

 

For example, if we want to find all products in our example table that include the substring Beer in their names we can begin by writing 'B' in the substring section of the function and the dialog will preview all products that have a capital B in their name.

 

eg_seldlg_expression01_05.png

 

As we continue to write the word Beer to enter 'Be' the preview will indicate fewer products.

 

eg_seldlg_expression01_06.png

 

When we finish writing the word the preview indicates just one product.

 

We can, of course, write expressions of arbitrary complexity using any combination desired of fields, mathematical and other operators, functions and so on.

 

Important: 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.   See the discussion and examples in the Regular Expressions topic.

 

Saved Tab

The Saved tab in the Select dialog allows us to save selections for a table so the saved selection will persist if the Select dialog is closed and even if the project is closed.   

 

eg_seldlg_saved02_01.png

 

Selections are saved by adding a new boolean field to the table that captures the selected / not selected status of a record.   The value in the field for selected records will be 1 or TRUE.  The value in the field for unselected records will be 0 or FALSE.

 

Important: Using the Saved tab to save a selection to a table will alter the schema of the table by adding a new boolean field to the table.  

 

That's something to keep in mind if the table with which we are working is located within a data source where other people might also be using it and might be surprised to see the table's schema change.

 

Adding a saved selection to the Saved Tab:

 

  1. Open a table in a table window.
  2. Open the Select dialog with Edit - Select or Ctrl-Q.
  3. Make a selection in the table.
  4. Click on the Saved tab in the Select dialog.
  5. Click on the <new field> entry in the list.
  6. Enter a name for the new field in the text box.
  7. Click the Add button.
  8. A new boolean field will be added to the table with 1 values for the selected records.

 

Using a saved selection:

 

  1. Open a table in a table window.
  2. Open the Select dialog with Edit - Select or Ctrl-Q.
  3. Click on the Saved tab in the Select dialog.
  4. Click on the name of a saved selection field in the list to highlight it.
  5. The dialog will preview that selection in the table in blue preview color.
  6. Apply the previewed selection to the table by clicking the command button in the desired mode.

 

Updating a saved selection:

 

  1. Open a table in a table window.
  2. Open the Select dialog with Edit - Select or Ctrl-Q.
  3. Make a selection in the table.
  4. Click on the Saved tab in the Select dialog.
  5. Click on the name of a saved selection field in the list to highlight it.
  6. Click the Update button.
  7. The saved selection field will now save the current selection of the table.

 

 

We can add as many saved selections as we want, with a new boolean field being created in the table for each.  Manifold applies no limit to the number of saved selections although obviously a list of very many saved selections in the Saved tab may be cumbersome to navigate.

 

There is nothing magical or special about fields the Saved tab creates to save the selected status of records.  Such fields are simply boolean type fields just like any other boolean field.  If our table has any existing boolean fields in it when we open the Select dialog the Saved tab will show those in the Saved list as well.  That has the side effect of enabling us to rapidly make a selection on the basis of any boolean field that already exists in the table.

 

When opened the Saved tab shows a list of all saved selection fields as well as all other boolean fields in the table along with a <new field> option to create another saved selection field.   If there are no saved selection fields in a table and no other boolean fields the Saved tab lists no fields but just offers a <new field> option.

 

We can use an existing saved selection by clicking on the field name to highlight it in the Saved list.  The selection will then be previewed in blue preview color in the table.   We can then combine that saved selection with any current selection in the table by using the Select dialog's command button for replace, add, intersect, invert or subtract combinations.

 

Because saved selections are saved by adding a field to the table those saved selections will persist even if we close the table.   When we re-open the table the added field will still be in the table and the Select dialog's Saved tab will once again show it. If the table is stored within the project and we save the .map file for the project, the fields added to a table to save selections will still be there when we next open the .map file, even if we move the .map file to a different computer.

 

Note that to save selections the table must be writable.  If it is a read-only table Manifold will not be able to create a new boolean field in the table for the saved selection.   Note also that because saved selections are nothing more than an additional boolean field, we can save selections for any table that allows us to create a boolean field in it, even if the table is hosted in some data source external to the project, such as an Oracle or PostgreSQL database.

 

For a step by step example, see the example below on using saved selections.

Window Tab

The Window tab in the Select dialog provides a list of all other opened windows using the same table as the current window.  The Window tab allows selections in those windows to be utilized in the current window via the selection modes offered by the Select dialog's command button.

 

Using a selection from the Window tab:

 

  1. Open a table in a table window.
  2. Open the Select dialog with Edit - Select or Ctrl-Q.
  3. Click on the Window tab in the Select dialog.
  4. Click on the name of a window in the list to highlight it.
  5. The dialog will preview the selection from that window in the table in blue preview color.
  6. Apply the previewed selection to the table by clicking the command button in the desired mode.

 

A window based on the same table as the current window can be a table window or a drawing window for that table, including a drawing layer in a map window.  All such windows will appear in the Window tab.   Windows based on other tables will not appear.

 

The Window tab is dynamic and will update itself in real time.   If we open another window based on the same table that window will automatically appear in the Window tab with no need to manually refresh the display.   If we close a window that appears in the Window tab it will disappear from the list in the Window tab.

 

See the example below for step by step use of the Window tab.

Command Button Modes

The Select dialog provides a command button at the bottom of the dialog that specifies how the selection it creates will be applied in combination with any selection that already may exist in the table window.   

 

eg_seldlgwin01_17.png

 

We choose a selection mode from the pull-down menu and then the command button will use that mode.   The selection mode we choose from the menu will persist until we choose a different mode for the command button.   The available selection modes available are listed below with a Venn diagram showing how the different command button modes operate.

 

In the Venn diagrams one circle represents the existing selection in the table and a second, overlapping circle represents the selection that the Select dialog would make.  Just as for records in the table window, the existing selection circle is shown in red selection color.  The selection the dialog would make is shown in blue preview color. The overlap, that is, those records that are both in the existing selection and which are also are in the preview are shown in blended color that combines the red selection color with blue preview color.  The final result of pressing the Select dialog command button in the given mode is shown entirely in red selection color.

 

il_sel_replace.png

Replace Selection -  Select all records in the preview and deselect all other records.   This operation takes whatever records would be selected by the Select dialog and makes those the only records selected in the table, ignoring anything before.

 

il_sel_add.png

Add to Selection -  Select all records that were previously selected in the table plus in addition select all records that the dialog would select.  Any records that were previously selected in the table and which the dialog would also select will, of course, remain selected.  Note that if the table did not have any prior selection that Add to Selection is equivalent to Replace Selection since in both cases the result is what the Select dialog would select.

 

il_sel_intersect.png

Intersect with Selection - Select only those records that were previously selected in the table and which also would be selected by the Select dialog action.   If the table did not have any previous selection the result would be to select no records.

 

il_sel_invert.png

Invert with Selection -  An "anti-intersect" operation: everything but the intersection.  Select all records previously selected in the table and all records the Select dialog action would select except those records which both were previously selected and also would be in the Select dialog's selection.   This is equivalent to an Add to Selection that simultaneously de-selects any  Intersect with Selection records.   If the table did not have any previous 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 Select dialog would select.  

 

il_sel_subtract.png

Subtract from Selection - De-select all records that are in the Select dialog's selection.  If the table has no existing selection this is the same as selecting no records.

 

Note that since other windows, including the originating table window, remain fully operational when the Select dialog is open we can still use menu commands in the table window such as Edit - Select All, Edit - Select None and Edit - Select Inverse.  Between those commands and the rich set of selection capabilities provided by the Select dialog we can make exactly the selection we want in the table window.

 

Notes

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 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.  We also frequently show dialogs overlapping the table windows.  There is no requirement to overlap a table window with a dialog for that table window but doing so allows us to fit more dialogs and tables into a smaller illustration.

 

Transition from pre-Radian products -  The Select dialog provides within a single dialog the functionality of the Selection Toolbar, Saved Selections pane and Selection Modes in pre-Radian versions of Manifold GIS.

 

See Also

Tables

 

Selection

 

Example: Using the Select Dialog Template Tab -  A sequence of actions using Select dialog templates.

 

Example: Using the Select Dialog Saved Tab - How to save selections and then use them later.

 

Example: Using the Select Dialog Window Tab - How to transfer selections between windows.

 

Example: Multiple Windows with Multiple Select Dialogs - Open multiple windows for the same table and open a Select dialog for each. This shows how multiple Select dialogs providing multiple selections and multiple previews for the same table can be opened and used at the same time.   

 

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