In this example we illustrate use of SQL functions for selection. If you have teleported into this topic without reading the basics, selection in Manifold is a different thing than use of the SELECT statement in SQL. Please review the main Selection topic before proceeding.
SQL functions for selections come in two versions for each function. One version of the function takes a table as an argument, and is used for selections in tables and components created from tables. Each selection function has a second version, with "window" in the name of the function, that takes a window name as an argument as well as a table name. This latter form is typically use for selections in queries and components created from queries, where each individual window can have its own selection.
The example data we use in this topic is a drawing of Mexico that shows provinces as areas. The drawing is called Mexico and it is based on a table called Mexico Table.
In this example, the drawing in use in an open drawing window, also called Mexico, where the drawing is the only layer.
The drawing is also in use in an open map window, called Map, where the drawing is one of two layers.
We also have the drawing's table open in a table window, called Mexico Table.
To illustrate the use of functions we will interactively hack away, writing snippets of SQL into the Command Window and then running them by pressing the ! run button on the main toolbar to see what happens.
Our first example uses the Selection function, which returns a table of all records that are selected (if the boolean argument is True) or unselected (if the boolean argument is False) in the table.
? CALL Selection([Mexico Table], True)
The ? command in the Command Window is a command to evaluate the following expression. It provides a quick way of evaluating snippets of SQL, to see what they do, without writing full SQL queries. We must use CALL because the function we use returns a table. There are four selected records in the table, so that is what the results table returns.
? CALL Selection([Mexico Table], False)
Changing the boolean argument to False returns a results table of all unselected records in the table.
The analogous "window" version of the function is called SelectionWindow.
? CALL SelectionWindow([Mexico], 'Map', 'Mexico', True)
When using SelectionWindow we must specify the name of the desired window as well as the name of the desired layer in the window, since windows can have more than one layer. In the above example we also use the name of the drawing, since the functions can take either the name of the table or the name of the component based on that table. We use the name of the drawing in the example above to show that either can be used.
In SelectionWindow the name of the drawing is in square [ ] brackets as [Mexico] because it is the name of a component, like a table. The names of the window and the layer are simply string arguments, so those are given using single ' ' quotes, as 'Map' and 'Mexico' as any string literal would be in SQL. As a boolean type, the last argument is simply True with no need to be set off by quotes or brackets.
This example shows a variation where we use SelectionWindow with the drawing window. Recall that the drawing window is called Mexico.
? CALL SelectionWindow([Mexico Table],'Mexico', '', True)
In this example we use the name of the table within square [ ] brackets, [Mexico Table]. The drawing window has only one layer, the drawing, so when using the SelectionWindow function with windows other than map windows we enter the layer name as an empty string, denoted by two single quote ' characters with no space between, as ''. This may look like a double quote character but in actuality it is two single quote characters with no space character in between them.
The SelectionKeys function returns a table with values of the key field for selected records.
? CALL SelectionKeys([Mexico Table])
In our table the key field is the usual mfd_id field used as a key field by default in most Manifold drawings. The results table reports the value of mfd_id for each of the selected records.
We haven't inverted the selection in this table, so as expected...
? SelectionIsInverted([Mexico Table])
...returns False. Since the SelectionIsInverted function does not return a table, we simply use the ? command with no need to use CALL.
Likewise, the SelectionIsEmpty function returns a boolean, not a table, so we do not need to use CALL with this function either.
? SelectionIsEmpty([Mexico Table])
The selection is not empty, so the above also returns False.
So far we have been using selection functions to view the status of the selection. We can use them to edit content as well.
UPDATE CALL Selection([Mexico Table], True)
SET [Place_name] = [Place_name] & ' - OK';
In the above we take the table of selected records returned by the Selection function and we concatenate the string - OK to the place names of each selected record.
The Command Window reports that 4 records have been updated, and the open table window is instantaneously updated as well.
What follows is not really specific to SQL functions for selection, but it does show how they can be combined with other capabilities.
EXECUTE CALL Selection([Mexico Table], True);
The results table generated by the above query is fully read/write since it includes the mfd_id field which hosts the index for the table. We can double-click into cells to edit the contents, for example, changing the Place_name value for the Sonora record from SONORA - OK to SONORA - Maybe.
The edit takes immediate effect and the table window is updated with the change.
Other facilities typical of table-style, grid displays in Manifold also work. For example, we can click on the SQKM field column header in the results table to sort the results table by that field.
In the above examples we have seen how to use SQL functions in the Command Window to pick out only the selected records in a table, and to then edit and sort the results table. Using snippets of SQL in the Command Window provides vast, endless power and flexibility but it is not the only way. If all we want to do is see only selected records in a table we can simply open the table and use Filters.
We click open the table with the selection in it.
With the focus on the table we choose View - Filter and choose the Selected option.
That filters the table to show only selected records. We can then click on the SQKM column header to sort by that field.
See the Example: Create a Drawing from a Query topic for a step by step example showing how to automate the use of selection query functions, to automatically create a drawing, on the fly, that shows only selected regions in some other drawing.
Example: Select a Range of Records in a Table - We can select or deselect a range of records between any two records in a table window using fast and easy mouse commands.
Example: Mouse Selection in a Drawing Window - Using the mouse to select objects in a drawing window. This could be a drawing layer in a map or simply in a drawing window.
Example: Combining Selections using the Select Pane - How to use selections to solve a one-off problem casually that would be more difficult to solve rigorously with SQL. The objective in the example's scenario is to visit a point of tourist interest while travelling. We will use the Transform pane as well as the Select pane.
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: Create a Drawing from a Query - Everybody knows we can create a drawing from a table, but we can also create a drawing from a query. When the query reports different results the drawing changes too. This example show step by step how to create a query and then how to create a drawing from that query. We show how to command Manifold to write a query for us that grabs a selection, and then how to create a drawing based on that new query. This example duplicates the Drawings from Queries video using the Mexico_queries.mxb sample project that may be downloaded from the Examples page on the Manifold web site.
Example: Construct JSON String using Select and Transform - Use the Select pane and the 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.