Example: Add a Field to a Table and Fill It

In this topic we add a field to a table and simultaneously for all records fill the field with a specified value.   We do that in two examples, first a very simple one and second, a more elaborate example that also shows restriction to a selection as well as use of the Transform dialog.


We open a simplified version of the Products table from the nwind sample database.   Click on the asterisk * column header to launch the New Field dialog.   In our first example we will create a new, boolean field that will be used to indicate if products are on sale or not.




We enter the name On Sale 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 Field button at this point and we would be done.  However, we want to populate the new field for all records with a specified value.  To do that we check the Set field values box.  We check this box whenever we want to create a field and simultaneously populate it with values, either dynamically computed or statically specified values.





Checking the Set field values box causes the New Field dialog to expand as seen below.  






We click on the Expression tab to specify the value that will be entered for each record into the new field.  In this case the value is a constant, but that still is an "expression," just a very simple one.





In the Expression pane we enter the expression TRUE.    tech_lars_sm.png


Tech Tip: 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.   We could write any expression, such as (1 = 1) that evaluates to TRUE.  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 representing boolean values in tables, as seen in this example.


As soon as we write TRUE in the Expression pane Manifold previews what will happen in the table window.   So far this is just a preview.




To add the field to the table with the given values filled in for all records, we press the Add Field command button.






The result is a new, boolean field added to the table called On Sale which is populated with the value TRUE (represented as a 1 in the table) for all records.




The above is a really simple example.  Let us next consider an example that uses text, restriction to selection and also the Transform dialog.


We begin with the Products table again, this time simplified to only two fields, the mfd_id field used for an index and a Name field.   We will add a text field we can use to describe the status of the vendors who provide each product.




Once again we click on the asterisk * column header to launch the New Field dialog.  We enter Vendor Status as the field name and choose nvarchar, a variable length text data type as the type.   




We check the Set field values box to expand the dialog and then click the Expression tab to allow us to enter an expression.  Once again we will enter a constant expression, this time a string, that is, some text.




We enter 'OK' using the SQL convention of putting text within single quotes.    




As soon as we do that Manifold previews what will happen in the table window.  So far this is just a preview.


Our plan is to fill the new field with the text OK for all products except Grandma's Boysenberry Spread.   To do that we will use selection using a simple trick to avoid having to click on all the records except Grandma's to select them.  


Manifold is fully parallel so different dialogs will normally use different threads.  That often means they can stay "live" and be used without closing other dialogs.  In this case the Products table window lives in a different thread than the New Field dialog and is fully "live."   We do not have to close the New Field to make selections within the Products table.   


We Ctrl-click on the row handle for Grandma's Boysenberry Spread. That selects the record, which is then shown with red selection color background to indicate it is selected.




Next, we choose Edit - Select Inverse to invert the selection.





Now all records except Grandma's are selected.




We can now go back to the New Field dialog and check the Restrict to selection check box.





This will apply the expression only to selected records.    As the preview shows, the new field will be filled with OK for all records except the  Grandma's Boysenberry Spread record.




To make that take effect we press the Add Field button.





Right away a new nvarchar field called Vendor Status is added to the table and is populated for all records except Grandma's with the text OK.  The record for Grandma's has a NULL in that field.




We will now populate the field for the Grandma's record using the Transform dialog.  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 dialog.


We begin by inverting the selection so only the Grandma's record is selected.    Choose Edit - Select Inverse to invert the selection.





Now only the record for Grandma's is selected.   Most people who work with selections frequently would just press Ctrl-I.  




We choose Edit - Transform to launch the Transform dialog.





We choose Vendor Status as the field on which we will operate and we click the Expression tab to allow us to write an expression.   The Transform dialog uses the same, familiar interface for expressions as does the New Field dialog.





We enter the text expression 'Growing rapidly' and check the Restrict to selection check box so the transform will apply only to the Grandma's record we have selected.


As soon as we enter the expression Manifold previews the results in the Product table window.  So far this is just a preview.




To make the change permanent we press the Update Field command button.





Now the change is permanent.




We can press Edit - Select None to deselect the record and get rid of the red selection color.  We could also Ctrl-click the record handle for the selected record to toggle it from selected to not selected.



The result is a table with a new text field added that has been populated with the values we want for each record.  



For such a simple table in real life we would not have bothered with selections and the Transform dialog, but the technique shown scales up well so we would probably use it in tables with many records.   We also did not have to apply the selection to only some records when first adding the new field, leaving the Grandma's record populated with a NULL.    This example used that technique to show how the Restrict to selection box operates.    Instead, we could have populated all of the records with an OK value when first adding the field and then second, we could have selected those records we wanted to change and then used the Transform dialog to change those selected records to a Growing rapidly value.


But in real life we might never have wanted to take the risk that a wrong value at any time would be in the database for a record.  If we were not editing a local table but operating on a table in some external database system it could be that other people might grab a record in the time we are working on it and have not yet changed an OK value to the desired Growing rapidly value.  In that case it is wise to use a two-step process as this example shows so that  the Grandma's record has a NULL value in the new field until the field is populated with the desired Growing rapidly value.

See Also

User Interface Basics




Data Types




Editing Tables


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 field to a table.  We first set the values for a field dynamically with a computed field using the Add Computed Field option in the New Field dialog.   We then illustrate what happens when we fill a field statically with values using the Add Field option.  Last, we show what the Add Component choice does in the New Field dialog.


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.