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 panel.

Fill a Field with a Fixed, Read-Only Value

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

 

eg_addfield03_01.png

 

With the focus on the table we launch Edit - Schema.

 

eg_addfield03_02.png

 

In the Schema dialog, we click the <new field> command under the Units in Stock row.  That opens up controls that allow us to enter a new field.    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 button at this point and we would be done.  However, we want to populate the new field for all records with a specified value.  

 

For the Expression we enter True.   Press Add and then press OK.

tech_lars_sm.png

 

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.   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.

 

eg_addfield03_03a.png

 

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 On Sale 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.

Filling a Field with an Editable Value

Let us next consider an example that first creates a new field and then fills it using the Transform panel.   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 begin with the Products table again, before the On Sale field was added.    If we are following along and trying out this example, we can use the Edit - Schema dialog to first delete the On Sale field.

 

eg_addfield03_03.png

 

With the focus on the table we launch Edit - Schema.

 

eg_addfield03_04.png

 

In the Schema dialog, we click the <new field> command under the Units in Stock row.  That opens up controls that allow us to enter a new field.    We enter the name Vendor Status for the new Field and choose a data type of nvarchar.    Press Add and then press OK.

 

eg_addfield03_05.png

 

A new 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 panel in the Contents pane.   With the focus on the table we switch to the Transform panel in the Content pane.

 

eg_addfield03_06.png

 

By default, the Transform panel'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.

 

eg_addfield03_07.png

 

In the drop-down menu we choose the Vendor Status field.   The menu provides a list of all fields in the table.

 

eg_addfield03_08a.png

 

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 or value.

 

eg_addfield03_08.png

 

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 mfd_id field into the Vendor Status field, that is what the preview shows.   When possible, Manifold will do simple conversions.   For example, even though the mfd_id field is an int64, a numeric field, and the Vendor Status field is type nvarchar, a text field, Manifold will do a quick conversion from number to equivalent text for the Copy template.

 

eg_addfield03_09a.png

 

To write whatever value we want into the Vendor Status field, we simply enter that value into the Value box in the Transform panel.   We enter 'OK' using the SQL convention of putting text within single quotes.    

 

eg_addfield03_09.png

 

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 panel.

 

eg_addfield03_10.png

 

When we press the Update Field button in the Transform panel, the template goes to work and 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.

Use Selection

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.

 

eg_addfield03_11.png

 

We Ctrl-click on the row handle 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.

 

eg_addfield03_12.png

 

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 panel in the Contents 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 panel.  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 panel.

 

eg_addfield03_13.png

 

Back in the Transform panel, which we have left open, the Vendor Status field is still the target.   When we click the Copy template the string 'OK' is still in the Value box.   

 

eg_addfield03_14.png

 

The blue preview appears again in the table, since we again have a Transform template in action.

 

eg_addfield03_15.png

 

To apply the Copy only to the selected record, we check the Restrict to selection box.

 

eg_addfield03_16.png

 

When we check the Restrict to selection box, the blue preview shows only that record will be altered by the Copy template.

 

eg_addfield03_17.png

 

We enter the text expression 'Growing rapidly' into the Value box.   That is what we want to be copied into the target field.

 

eg_addfield03_18.png

 

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 in the Transform panel.

 

eg_addfield03_19.png

 

The illustration above shows the record still selected.   To de-select it, we can Ctrl-click anywhere in the record.

 

eg_addfield03_20.png

 

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

 

See Also

User Interface Basics

 

Tables

 

Data Types

 

Selection

 

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.