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.
User interfaces for the Transform pane have been changed. See the Transform Reference topic for guides to new interfaces while this topic is updated.
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 field using the Transform pane. With the focus on the table we switch to the Transform pane in the Content pane.
By default, the Transform pane's actions are aimed at the mfd_id field. We will change that to the new field we have created and then choose the template we will use to fill that field.
In the drop-down menu we choose the Vendor Status field. The menu provides a list of all fields in the table.
Next, we scroll down in the list of templates and click on the Copy template. A Value box opens up which allows us to choose a field that is the source of the Copy, or into which we can write an expression.
As soon as we choose the Copy template, Manifold previews what will happen in the table window, using blue preview color. So far this is just a preview. Since the Copy is currently set to copy whatever is in the Vendor Status field into the Vendor Status field, that is what the preview shows, NULLs.
To write an expression instead of choosing an existing field in the table, we click on the field icon in the Value box.
We choose Expression from the pull down menu in the Value box.
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.
Right away, Manifold previews what will happen. This is still just a preview. No changes have yet been made to the table.
To apply the transform template, we press the Update Field button in the Transform pane.
When we press the Update Field button in the Transform pane, the template fills the Vendor Status field with the text OK. The blue preview color goes away because what we see now in the table is not a preview, it is the actual data which is now in the table.
So far, so good. We have created a new field called Vendor Status and we have filled that field with the text value OK for all records. However, our plan is to fill the new field with the text OK for all products except Grandma's Boysenberry Spread. For that product, we would like to write something else into the Vendor Status field.
We could, of course, do that by simply double-clicking into the Vendor Status cell for that product and manually editing the cell. But suppose we wanted to make that change for hundreds, or thousands of such products? In that case, it would be easier to use selection to choose the desired records and then again to use the Copy template, but restricted to the selection.
To select the record for Grandma's Boysenberry Spread we Ctrl-click anywhere in the row for Grandma's Boysenberry Spread. In the illustration above we have widened the Name column slightly so the full name of the product can be seen.
That selects the record, which is then shown with red selection color background to indicate it is selected.
There are many ways to select records in Manifold, either interactively with mouse and keyboard or with the Select pane. We show the simple case of just selecting one record, since the technique shown in this topic, of applying a Transform template only to selected records, works the same whether there is only one selected record or many selected records.
We will now populate the field for the Grandma's record using the Transform pane.
Of course if we only had one record to adjust we would simply edit it directly by double-clicking into the Vendor Status cell for that record, as illustrated in the Editing Tables and Example: Editing Records in a Table topics. But since we are teaching a method that can be applied when many more than just a single record are to be modified we will use the Transform pane.
Back in the Transform pane, which we have left open, the Vendor Status field is still the target. When we click the Copy template, the Vendor Status field again appears in the Value box as the default choice.
The blue preview appears again in the table, since we again have a Transform template in action.
To apply the Copy only to the selected record, we check the Restrict to selection box. Note that clicking the caption text also works to check the box, making it easier to click the box.
When we check the Restrict to selection box, the blue preview shows only that record will be altered by the Copy template.
As before, we switch the Value box to using an expression, and then we enter the text expression 'Growing rapidly' into the Value box. That is what we want to be copied into the target field.
As soon as we enter the expression Manifold previews the results in the Product table window. So far this is just a preview.
To apply the change we press the Update Field command button in the Transform pane.
The illustration above shows the record still selected. To de-select it, we can Ctrl-click anywhere in the record.
The result is a table with a new text field added that has been populated with the values we want for each record.
If we no longer need to restrict operations only selections, it is a good idea to uncheck the Restrict to selection box right away, so in the future we are not confused if the Transform pane does nothing, because we do not have anything selected.
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.
User Interface Basics
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.