In this topic we add a field to a table and simultaneously for all records fill the field with a specified value. There are two ways we might do that, based on our objective:
This topic shows examples for both cases. The first case is more frequently used, so it is illustrated in a more elaborate example that also shows restriction to a selection as well as use of the Transform pane. The second case is very simple but less frequently used, so it is illustrated at the end of this topic.
Let us next consider an example that first creates a new field and then fills it using the Transform pane. The new field will be completely editable. We will add a text field we can use to describe the status of the vendors who provide each product.
We open a simple table that shows products in a table called Food Warehouse.
With the focus on the table we launch Edit - Schema.
Press the Add command
button.
Choose Field in the drop down menu.
We enter the name Vendor Status for the new field and choose a data type of nvarchar. Press OK.
The schema shows the new field in provisional, bluish color to indicate it has been provisionally added and the changes to the table have not yet been committed. Press the Save Changes button to commit the changes to the table.
A new Vendor Status column appears in the table. The field is filled with NULLs since we have yet to add any values for the records.
We will now fill the new Vendor Status field with a text value of OK for every record.
There are many ways to fill the new field. One of the simplest is to use the Copy to Selection command that appears on context menus for selected items. Selection is fast and easy.
We double-click into the Vendor Status cell for the first record and enter OK, and then press Enter. Next, we press Ctrl-A to select all records. Instead of the keyboard shortcut, in the main menu we could choose Edit - Select All to select all records.
Right-click onto the OK cell and in the context menu choose Copy to Selection.
The OK text value is immediately copied into the Vendor Status field for all records. We can now press Ctrl-I to invert the selection, thus deselecting all records, or press Shift-Ctrl-A to deselect all records, or in the main menu choose Edit - Select None.
We can fill the new field using expressions in the Transform pane. That is overkill when filling fields with literal text or numbers, but it is very useful when filling fields with more complex constructions.
Next, with the focus on the table window we choose the Transform pane. In the Transform pane we choose the Vendor Status field. We double-click on the Expression template to launch it.
In the Expression template, we press Edit Expression to launch the expression builder dialog.
We can now enter whatever expression we desire. We enter 'OK' using the SQL convention of putting text within single quotes. In this case we use the expression capability in a very simple way, to enter a static value, that is, a constant, and not a computed expression.
Back in the Transform pane, press the Transform button.
The Vendor Status field immediately fills with OK for all records.
Normally the Expression dialog is used for more sophisticated expressions, calculating values based on different fields, for example, but there is nothing wrong with using it in a very simple way as we do in this example.
If we would like a more sophisticated example, in the Transform pane we could press the Edit Expression button to launch the expression builder dialog again, and to change the simple 'OK' expression entered earlier into an expression using CASE:
The SQL expression we enter is:
CASE WHEN StringContains([Name], 'Anton')
THEN 'Preferred'
ELSE 'OK'
END
It uses the CASE operator to write Preferred into the Vendor Status field if the Name contains the string Anton and to write OK into the field otherwise.
Press OK in the Expression dialog.
Back in the Transform pane, press the Transform button.
The expression template fills the Vendor Status field with the text OK, in all fields except those where the Name field contains Anton. For those records the template writes the text Preferred into the Vendor Status field. Chef Anton is happy.
Manifold provides a very rich toolbox, so there are often many different ways to accomplish the same task, depending on our tastes and which way is quicker or easier. We have seen how using selection to select all records can be used to easily fill every record. We can use a similar technique to select only those records to be changed and to Copy to Selection to those records. We can also use selection with the Transform pane, to apply the results of a transform only to selected records.
In the following examples, suppose we have created a new field called Vendor Status and have filled it with OK for all records. Next, we would like to fill the new field with some other value for Grandma's products, for example, Grandma's Boysenberry Spread. We could do that using a CASE expression like we did for Chef Anton, but we can also do that using a selection to choose the desired records and then to operate only on the selection.
There are many ways to select records in Manifold, either interactively with mouse and keyboard or with the Select pane. We show a selection using the Select pane, but whichever method we use the techniques shown apply the same, whether there is only one selected record or many selected records.
We start with the Food Warehouse table, with a new Vendor Status field that has been filled with OK for each record.
With the focus on the table window, in the Select pane we choose the Vendor Status field and then we double-click the Search template to launch it.
In the Search template we choose text as the Use option. We choose contains as the Condition option.
In the Value box we enter Grandma. We leave other parameters at their default, not trimming text, and using a nocase collation to cover upper and lower case variations for Grandma.
For Action, we use the default replace selection.
Press Select.
That selects the Grandma's Boysenberry Spread record in the display view, which is then shown with red selection color background to indicate it is selected, and it also selects all other records that include Grandma in their Name.
We double-click into the Vendor Status cell for the selected record and change OK to Growing rapidly. Press Enter to commit the edit.
We can now right-click into the Vendor Status cell and from the context menu choose Copy to Selection. That will copy Growing rapidly into the Vendor Status field for all selected records throughout the table, including those not in view.
Of course, if we have only one record for a Grandma's product, we would simply edit the Vendor Status cell for that record directly without any need for doing selections. But if we have thousands or millions of such records we can use selection.
We often use the Select pane and the Transform pane in combination in Manifold, selecting records we would like to change and then doing a transform operation, restricting the action of the transform by checking the Transform selection only box.
With the focus on the table window, in the Transform pane we choose the Vendor Status field and then we double-click the Expression template to launch it.
In the Expression template, we check the Transform selection only box, doing that now to avoid forgetting later, and then we press Edit Expression to launch the expression builder dialog.
In the Expression builder dialog we enter the very simple expression 'Growing rapidly' and then press OK.
Back in the Transform pane, we confirm the Transform selection only box has been checked.
Press Transform.
The template immediately populates the Vendor Status field for all selected records with the result of the expression, the text Growing rapidly.
Of course we would not bother using the Transform pane to edit just a single selected record. But if we have many selected records to edit, and especially if we want to edit those selected records in a sophisticated way, the Transform template is a great way to do it. Besides using free form SQL expressions, we have a very large arsenal of transform tools that can be used.
If we no longer need to restrict
operations only selections, it is a good idea to uncheck the Transform
selection only box right away, so in the future we are not confused
if the Transform pane does nothing, because we do not have anything selected.
The examples above populated the Vendor Status field with text values after it was created. We can edit those at any time, since the Vendor Status field was created as an ordinary, fully writable text field. A different way to fill a field with text values is to create a field as a computed field that is filled with a computed value at the same time it is created. Computed fields are read-only, because their contents are computed based on the expressions defined at the time they are created.
We begin with the Food Warehouse table again, before the Vendor Status field was added. If we are following along and trying out this example, we can use the Edit - Schema dialog to first delete the Vendor Status field: In the Schema dialog, double-click onto the Vendor Status field and then press the Delete button in the toolbar.
In this example we will create a new, boolean field that will always be True, so any records used from this table in other settings will indicate the produce is a food or beverage.
With the focus on the table we launch Edit - Schema.
Press the Add command button.
Choose Field in the drop down menu.
In the Field dialog we enter the name Food or Beverage for the new field and choose a data type of boolean.
If all we wanted to do was to add a new, blank field we could simply click the Add button at this point and we would be done. However, we want to populate the new field for all records with a fixed, specified value. To do that, we will create the field as a computed field, the value for which is generated by an expression.
To create that expression, we click the Edit Expression button.
In the Expression dialog, we enter True into the expression pane. This is a very simple expression, a constant value. Press OK.
Back in the Field dialog we see the expression we added. We press OK.
Tech Tip: Expressions use snippets of SQL. Since this is Boolean logic we are dealing with in an SQL expression setting if the boolean value TRUE is what we want the simplest way to get that is to simply write TRUE (or any variation of TRUE in upper or lower case, as SQL is not case sensitive for such key words). Note that the numeric value 1 is not a synonym for TRUE within an SQL expression, although a numeric 1 for TRUE and 0 for FALSE may be used as synonyms when editing boolean values in tables, with the numeric value being converted on-the-fly into a boolean during the edit.
The new computed field we have added appears in provisional, bluish color in the Schema dialog. If we like what we see, we press Save Changes to commit the change to the table. If we detected an error or wanted to change something, we could press Close to exit the dialog without making any changes.
A new field appears in the table, with values for all records set to true. This is an extremely simple use of an expression, to enter a constant value. But that still is an expression, just a very simple one. The background for the Food or Beverage field is light gray, indicating it is read-only and cannot be edited. That is because the value is specified by the expression.
Normally, expressions would be used for a computed field that was the result of computations based on the values of other fields, as shown in the Example: Add a Computed Field to a Table topic. But we can use an expression to force the value of a given field to a constant, if desired, as is done above.
Example: Editing Records in a Table - How to edit the contents of an existing record using mouse and keyboard.
Example: Adding Records to a Table - How to add a new record to a table using mouse and keyboard.
Example: Create a Table and Add a Record - Create a table with required fields and then add a record with value for those fields. Creates the OGR-required table to prepare a Manifold project for use by OGR as detailed in the Example: Connect to Radian from QGIS topic.
Example: Add a Computed Field to a Table - In this example we add a computed field to a table, illustrating how the computed field automatically changes when changes are made in the fields it uses for computation. We also show how computed fields can use geometry, automatically updating centroids when areas are changed. Last, we show how geometry can be created using computed fields, to create effective radius circles for antennas based on the power of the antenna.
Example: Add a Second Computed Geom Field to a Table - We can create tables with more than one geom field in the table and then we can create drawings which use those additional geom fields. This topic shows how to create a second geom that is a computed field based on the first geom. The topic also shows some "real world" methods, such as how to remember the use of a geometry function to do what we want, and how to restore a geom that has been moved. We close with some illustrations of how multiple geoms might be used, and how selection from any drawing or labels based on the same record selects the corresponding objects or labels in all other components based on that record.
Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.
Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.
Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.
Example: Add a UUID-based Index to a Table - Create a new computed field that is filled with UUID values on creation and then create an index on that field. This technique creates an indexed field that has guaranteed unique values for all records and thus the indexed field and record values may be used in other projects.