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.




We open a table called Products that is taken from the nwind example database.  The table has been simplified to only five fields to more easily fit into illustrations.   The Products table shows a list of products, each having a price.   We will add a new field by clicking on the blank, asterisk *  template column head at the right of the table.




The New Field dialog appears.  We specify a name, Total Price, for our new field and choose float64 as the type.  If all we wanted to do was to add a new, blank field we could click the Add Field button and we would be done.  However, we want to populate the new field for all records with values that will be computed.   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 have clicked on the Expression tab since we want to write an expression to calculate the values that will be entered for each record into the new field.




In this expanded form the New Field dialog is similar to the query builder in the Command Window.  It provides a list of fields already in the table that we can use to construct the expression, along with operators and functions we can use.   Just as with the query builder we can double-click on items to add them to the expression, saving the need to keyboard field names and helping us to avoid typographical errors.


For our new Total Price field we want to add values that are the Unit Price value for each record multiplied by the Units In Stock value for each record.  We begin creating the expression by double-clicking on the [Unit Price] field in the list of fields in the expression builder.




Double-clicking on the [Unit Price] field adds it to the expression.  Manifold immediately begins calculating a preview display in the table window.  Until the preview is ready the * field column will be  shown with gray background color.




Most calculations go so fast we might not notice the brief flicker of gray color while a preview calculation is pending.  As soon as the preview calculation goes to completion the * field will show a preview of the computed values for which an expression is being written, using blue preview color for the background of the cells.   We can see that for each record the value in the * column is the same for that record as in the Unit Price field.  




As we continue building the expression by adding additional operators, functions or fields the preview computation will be continuously updated.   In the illustration above we have added a multiplication  *  operator and have double-clicked on the [Units In Stock] field to add it to the expression.  The expression now reads:


[Unit Price] * [Units In Stock]


Right away, the blue preview in the table window shows the new values that will be computed from that expression for each record in the new field.   We can see, for example, that 702 is indeed the result of multiplying 18 by 39.




If we change our minds and want to use the [Units On Order] field in the expression instead of the [Units in Stock] field, we highlight the [Units in Stock] term in the expression and then double-click on the [Units On Order] field in the expression builder.




That will replace the highlighted text with the [Units On Order] field.




As the [Units On Order] field goes into the expression Manifold right away recomputes the preview shown in the table window.  As expected the previewed result in the first line is 0, which is indeed the result of multiplying 18 by 0.




If we are happy with our expression and the preview we see we can add the computed field to our table.  To do that we first must change the action button at the bottom of the New Field dialog from Add Field to Add Computed Field





In the pull down menu we choose Add Computed Field.




We can then press the Add Computed Field button.




The table now gains a new field called Total Price. The new field is a computed field, which we can tell at a glance because of the light gray background Manifold uses in columns for computed fields.




We can see the expression used to compute the field by hovering the mouse over the column header for the Total Price field to display a tooltip.  The tooltip shows the type, float64, as well as the expression used to compute the field.


Computed fields are dynamically updated.  If we change the values of fields used in the expression the value shown for the computed field will change.



For example, suppose we double-click into the Units On Order field for the first record and change the value from 0 to 16.   We press Enter to accept the edit into the cell and then Ctrl-Enter to commit the edit to the record.




Computed fields will automatically recompute their contents for any changes in fields that participate in the expressions that create them.   If we change the Units On Order value for the first record from 0 to 16 the value of the Total Price field for that record will automatically be changed to 288, which indeed is the value of 18 multiplied by 16.


Using Add Field vs. Add Computed Field

How does the use of Add Computed Field differ from the original setting of Add Field in the action button of the New Field dialog?    



We can see the difference by re-playing the example above but pressing the Add Field command button without first changing it to Add Computed Field.   We will call the new field Total Price NC ("NC" for "not computed") to emphasize it is not a computed field.





A new Total Price NC field is added to the table and for each record the value in that new field is the Unit Price value for that record multiplied by the Units On Order value for the record.



The field has been automatically filled for each record in the table with the results of that computation for each record.  All of the records in the table have been filled with that computed value and not just those records we see in the table window.  If the table has millions of records each one will have a value in the Total Price NC field for that record which is the Unit Price multiplied by the Units On Order.


The values filled into the new field are computed just once based on the values of the other fields at the time the new field was created.   They are not re-computed on the fly thereafter if other values in fields used in the expression change.  


We can see that effect by changing the Units On Order value for the first record from 0 to 16.





The value of Total Price NC for that record will not change as a result.  It stays at zero since it is not a computed field but instead it is just a regular field like any other.  Changing the value of Units On Order will not change the value that is in Total Price NC for the record.    That the Total Price NC field was populated initially based on the results of a computation no longer has any lingering effect, no more than if the table in which it is located was created based on the result of a SELECT INTO query using various computations within the query to populate the contents of the table when it was created.


If we want our new Total Price field to automatically change based on any changes in values for the fields that are involved in the expression that populated the field, we can do that by creating the new field as a computed field as shown in the first part of this topic.


Add Component


The command button in the New Field dialog has one more choice, Add Component.   This option is the same as Add Field but it creates a copy of the table in the project and adds the new field to that copy of the table.  It does not alter the original table.




To use it we use the pull-down menu to switch the command button to Add Component.





We can now press the Add Component button.   A new table called Products Total Price (the name of the new field concatenated onto the name of the table) is created that is a copy of the Products table but with the new Total Price field added.   The original Products table has not been changed.



If we open the new Products Total Price table we see it is indeed a copy of the original Products table plus the new Total Price field we created with the New Field dialog.   The Total Price field is populated with values that are the result of the expression, the Unit Price multiplied by the Units On Order, we specified in the Expression pane of the New Field dialog.


The Add Component command is a convenience.   We could have achieved exactly the same result by copying and pasting the Projects table to make a copy of the table and then opening the copy and using the New Field dialog with Add Field to add a new Total Price field to the copy.

Using the Restrict to Selection Option

Suppose when we create a new field we would like to fill some records with values for that new field but not others?  That is easy to do.  We simply select those records we would like to be filled and then check the Restrict to selection box in the New Field dialog.  




Because the table window is active in a different thread than the New Field dialog (one of the many benefits of parallelism in Manifold) we do not need to close and restart the dialog to check or to uncheck the Restrict to selection box or to make selections in the table window with the mouse.




Let us begin with the same Products table used elsewhere in this example.  We will add a new field that will help guide us in deciding which products to put on sale.   We click on the asterisk * column header to add a new field.




In the New Field dialog we specify the name Put on Sale for the new field, we choose a data type of float64 for the field and we check the Set field values box.




The New Field dialog expands.  We click the Expression tab and enter the expression


[Unit Price] * [Units In Stock]


Manifold immediately calculates the values for the new field for all records and previews the results in the Products table window.   The table window lives in a different thread and is fully "live," so it immediately shows the preview.  



Not only does the table window show the preview, we do not have to close the New Field to make selections within the Products table.   We Ctrl-click on the row handle for the Grandma's product to select that row.   The record is then shown with red selection color background to indicate it is selected.




We then Ctrl-click on the row handle for the Chef Anton's product two rows up to select that row as well.




These are the two records for which we would like to fill the new Put on Sale field with the result of multiplying the Unit Price by the Units In Stock.    These are plausible candidates to put on sale in a real-world business if the dollar value of existing inventory is high and if the products are slow movers, so we would like to reduce the inventory of them that we keep.





Back in the New Field dialog, which has remained open, we have checked the Restrict to selection box.


As soon as we do, the preview changes in the Product table window.




We can now see that the new field will be filled with values only for the two selected records.




Back in the New Field dialog we press the Add Field button.




Immediately the new Put on Sale field is created and is filled with the values computed from the expression for only the two selected records.  The value in the Put on Sale field for all other records is NULL.


Why two options, Add Field and Add Computed Field? - Why does Manifold provide two ways to populate a new field, either by initially filling it with values as an ordinary field or by filling it with values dynamically computed thereafter by a computed field?  The two methods solve two different needs, much as a spreadsheet such as Excel can provide both computed cells and also cells with specified, static values.


In this example when using Add Field we populated all records with values for the new field that were computed by an expression that was the multiplication of two other fields in the record.  However, the values could have been a constant or a fixed boolean value to set the starting condition.


For example, suppose in our list of products we wanted to have a field called Discontinued that for all products starts off as 0 to indicate a boolean FALSE but that over time as new products are added to the mix and old products are discontinued, some products will be marked 1 or TRUE.   When we create the field Discontinued we might create it as a boolean type and the expression used to generate the initial values for that field would be the one word expression FALSE.  The field would be created with a 0 for each record.


Why is there no "Add Computed Field Component" Option? - The Add Component command is a shortcut way of creating a copy of the table with a new field populated with values as the Add Field command would do.   Is there an equivalent that creates a copy of the table with a new field that is a computed field as would be created by the Add Computed Field command?   No, as that seems a bit too rarely used.  If that is what we want we simply first make a copy of the table and then do the Add Computed Field procedure to that copy.


See Also

User Interface Basics




Data Types




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 Field to a Table and Fill It - 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.


Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.  


SQL Example: Create a Table with a Constraint via a Query -  A simple example using SQL to create a table with a constraint.


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.