Manifold tables can contain fields defined by expressions that are computed on the fly, called computed fields. Computed fields in Manifold tables are like cells that are computed by formulas in an Excel spreadsheet: if any values used in the calculation change, the computed field cell will automatically change as well. Computed fields can be created in tables stored within a Manifold .map project. We can even create an index on a computed field, so long as the table for the computed field is stored in the .map project (and not in an external database).
Manifold can create virtual computed fields in external databases, such as Oracle, MySQL, SQL Server, PostgreSQL, DB2, ESRI GDB, GPKG, etc, which will appear as computed fields in a Manifold project when that DBMS is linked as a data source into the project (but will not appear as computed fields in nonManifold tools). This would allow having a virtual computed field with geoms composed on the fly from X and Y values stored in the database. Virtual computed fields on databases cannot refer to other computed fields and cannot participate in indexes.
Computed fields are a great way to automatically compute areas, bearings and other characteristics, to add date stamps like the last time the record was modified, to create custom text configurations from other fields, and for many more purposes. Constraints are related to computed fields in that they also are based on expressions computed on the fly.
We add computed fields or constraints to a table using the Schema dialog, or with SQL queries. This topic shows use of the Schema dialog. See the Computed Fields and Constraints topic for an SQL approach to adding computed fields or constraints.
Open the table and choose Edit  Schema, OR... rightclick on the table in the Project pane and choose Schema.
Press the Add button in the Schema dialog's toolbar and choose Field.
In the Field dialog, provide a Name for the field and choose the data Type for the field.
Press the Edit Expression button, to launch the Expression dialog.
In the Expression dialog's Expression tab, enter an SQL expression that performs the desired computation, using the expression builder to help compose the expression. More sophisticated expressions can use the Expression Context tab as well.
Press OK.
Back in the Field dialog, press OK.
Back in the Schema dialog, press Save Changes.
See the Schema topic for details on controls. The expression builder in the Expression dialog is similar to the Query Builder for SQL expressions in the Command Window. See the Example: Expression Context and Computed Fields topic for step by step examples using the Expression Context tab.
Computed fields can be based on fearsomely intricate expressions, but they often are used with very simple arithmetic that, despite being very simple, provides great usefulness.
We add a computed field to a table that multiplies unit price by units in stock to get the total value of a product in inventory.
We open a Products table based on the nwind example database. For each product the table gives the Name of the product, the Unit Price of the product and the Units In Stock of that product. We would like to add a computed field called Inventory Value that multiplies the Unit Price field by the Units In Stock field to get the total value of that product in inventory.
With the table open, we choose Edit  Schema to launch the Schema dialog.
We press the Add button and choose Field in the resulting menu.
We enter Inventory Value for the name, and we choose int32 for the data type. Using an integer data type for an amount involving money, instead of a floating point number, like float32 or float64, is a simple, albeit less exact, way of ensure the results appear without many digits after the decimal point.
So far, this is the same workflow as for adding any new field to the table. To make the new field a computed field, we must add an expression. To do that, we press the Edit Expression button. That launches the Expression dialog.
The Expression dialog has a pane at the top in which we can write an expression, and a list of templates below that the builtin expression builder uses. We can write an expression freehand, or we can compose the expression using the expression builder templates, or we can write the expression using a mix of freehand and expression builder.
At the top of the expression builder templates is a list of fields in the table. To add a field to the expression, we doubleclick it in the expression builder list. It appears in the expression pane at the current cursor position. We doubleclick the Unit Price field to add it to the expression.
It appears surrounded by square bracket [ ] characters, which Manifold uses to disambiguate any names that have spaces or special characters in them. The expression builder always uses square brackets, even when not necessary, for a consistent visual style.
Next, we manually enter a space character, the asterisk * character (the multiplication operator in SQL), and another space character. We can then doubleclick the Unit Price field to add it to the expression.
That finishes writing the expression we would like to use:
[Unit Price] * [Units In Stock]
We could have written such a short expression freehand, of course, manually keyboarding the names of the fields instead of doubleclicking them in the expression builder. However, getting in the habit of using the expression builder helps to avoid typographical errors, especially with long field names or long function names.
We press OK.
The Field dialog shows the new expression we have created. We press OK.
Back in the Schema dialog, the new Inventory Value computed field appears in provisional blue color, showing the name, type, and the first few characters in the expression. To see the entire expression we can hover the mouse cursor over the expression and the entire expression will be shown in a tool tip.
To create the new computed field in the table, press Save Changes. If we change our mind and decided not to make any changes to the table, we could press Close.
The new Inventory Value computed field appears in the table. It is shown in gray background color used for readonly fields, because it is a readonly field that takes its value from an automatic computation. We cannot doubleclick into an Inventory Value cell to manually change the value.
The new Inventory Value computed field can be used like any other field. A computed field in a Manifold table is a permanent part of the table. When we close the table and open it again, the computed field is still there. When we use the table in other queries, for example, a JOIN, the computed field can be used just like the other queries.
For example, we can Ctrlclick the column header for Inventory Value to sort by that field in ascending order, and then Ctrlclick on the column header again to sort in descending order. At a glance, we can see which products have the highest inventory value considering the number of units in stock and the price for each.
The Inventory Value field is computed using very simple arithmetic, but having it provides tremendous value in being able to see at a glance where our inventory money is tied up, in a way which is not possible just by looking at Unit Price and Units In Stock numbers separately. Without the Inventory Value field we might not have noticed we have more money tied up in maple syrup than in luxury goods like crab meat or caviar.
In the illustration above we have hovered the mouse cursor above the product with the greatest Inventory Value to see the full name of the product, more characters than can fit into the width allotted for that column.
A Manifold computed field is dynamic. It automatically updates when other fields or other values on which it depends change, just like the value in an Excel spreadsheet cell created by a formula will recalculate when values in other cells in the formula change.
Suppose we doubleclick into the Units In Stock cell for the Tibetan Barley Beer record, and we change the value from 17 units in stock to 5 units in stock.
Instantly, the Inventory Value for the Tibetan Barley Beer record changes from 323 to 95.
Computed fields in Manifold are related to the idea of derived fields, a feature most DBMS products (including Manifold) provide. Derived fields are static, like the results computed for a onetime report, while computed fields in Manifold are dynamic, like the computed cells in an Excel spreadsheet. Derived fields are also impermanent appearing in a results table and disappearing when the results table is closed, while computed fields in Manifold tables are a permanent part of the table, which can be used like any other field in the table.
A derived field is a field that an SQL query computes on the fly from other fields, typically appearing in the results table of the query. Consider the query:
SELECT [Name], [Unit Price] * [Units In Stock] AS [Inventory Value]
FROM [Products];
The above query creates a results table where the Inventory Value field in the results table is a derived field, computed on the fly by multiplying the values in the Unit Price field with the Units In Stock field. However, the results table is a virtual table that has no permanent existence. It appears as a report of the results of the query and it disappears when we close it. If we wanted to save the results as a table, we would have to create a new table using SELECT ... INTO, as in:
SELECT [Name], [Unit Price] * [Units In Stock] AS [Inventory Value]
INTO [Valuation]
FROM [Products];
The above query creates a new table called Valuation and fills it with the results of the query, the Name field and the computed Inventory Value derived field for each record.
The Valuation table is permanent and won't disappear when we close it, but the Valuation table is just a static snapshot of inventory value at the moment the table was created. If we changed the Unit Price for some items and changed the Units In Stock quantities, the Valuation table would not automatically be updated. It is not like an Excel spreadsheet, where cells created by formulas update automatically. Instead, it is just a report that was created on a onetime basis.
In the illustration above, we have placed the table cursor on the Inventory Value for Tibetan Barley Beer, which is 95 as a result of our last edit, changing the units in stock of barley beer from 17 to 5.
In contrast, a computed field in a Manifold table is a permanent part of the table. When we close the table and open it again, the computed field is still there. When we use the table in other queries, for example, a JOIN, the computed field can be used just like the other queries.
The Manifold computed field is also dynamic. It automatically updates when other fields or other values on which it depends change.
Suppose we doubleclick into the Units In Stock cell for the Tibetan Barley Beer record, and we change the value from 5 units in stock back to 17 units in stock.
Instantly, the Inventory Value for the Tibetan Barley Beer record changes from 95 to 323.
However, the Inventory Value in the Valuation table does not change. It remains at 95 because it is simply the result of a static, onetime, calculation.
We might think of computed fields in terms of numerical calculations, but they can be the result of any expression, for example, concatenating text. We will take this example at a faster pace, skipping a few illustrations for steps covered in the prior example.
Consider the Employees table above, also based on the nwind sample database. It has two text fields called Last Name and First Name. We would like to concatenate (combine) those in a single field called Name.
We launch the Schema dialog by choosing Edit  Schema. We press the Add button and choose Field.
In the Field dialog we enter Name as the name of the new field. We choose nvarchar, the Unicode text type, for the Type. Many Manifold users always choose nvarchar instead of varchar for text fields, to ensure that no matter how they choose to use their tables, they are always ready for Unicode, that is, international character sets. Manifold automatically converts between nvarchar and varchar when exporting to formats that cannot handle nvarchar.
The expression we enter is
[First Name] & ' ' & [Last Name]
That is the First Name field, concatenated with a string consisting of a space character (written between single ' quote characters), concatenated with the Last Name field.
We press OK. and then back in the Field dialog we press OK again.
We press Save Changes.
The new computed field appears in the table. We now have a field in the table that automatically concatenates last name and first name to provide a full name, which we can use in other expressions, to create labels, and so on.
We can take advantage of Manifold's many SQL functions that can operate on text.
For example, in the Restuarants table above, similar to that used in the Example: Street Address Geocoding topic, we have an address text field that provides the complete address for an InNOut restaurant. We would like to create a computed field called street that provides only the street portion of the address, without the town, state and zip code.
We launch the Schema dialog, perhaps by rightclicking the Restaurants table in the Project pane and choosing Schema. We press the Add button and choose Field.
In the Field dialog we enter street as the Name of the new field, we choose nvarchar as the Type, and we press the Edit Expression button.
In the Expression dialog we enter the expression:
StringRegexpReplace([address], ',.*', '', 'c')
... using the StringRegexpReplace function. How we learned to use this function is discussed later in this topic.
We press OK. Back in the Field dialog we press OK again.
Back in the Schema dialog, we press Save Changes.
The new street computed field appears in the table, neatly providing just the street portion of the address. We can now create labels from this street field, which we can label points on a map for each restaurant, giving the address in shorter form than the full address.
How did we learn to use the StringRegexpReplace function? We could read through functions in the SQL Functions topic, or we can see what Manifold does when it uses SQL functions in Transform templates.
We start with the table above. We can get the result we want by starting with the full address and then eliminating everything that comes after the first comma. To figure out how to do that, we switch to the Transform pane. We can use the preview Manifold gives us to try different transform templates to find the one that does what we want.
We choose the address field as the target. After some tinkering, we zero in on using the Replace template with the regular expression choice in the Replace box.
In the Search for box we enter the regular expression ,.* as a pattern, that is, a comma , character followed by a dot . character followed by an asterisk * character. Refreshing our regular expression knowledge by reading the Regular Expressions topic, we know that the .* sequence matches any group of one or more characters, so the ,.* sequence matches all of the characters that come after the first comma, including the comma. The Replace with box is empty, the effect of replacing the regular expression pattern with nothing will be to delete all occurrences of text that matches the regular expression pattern.
We will place the Result into a new field, called address2. The Transform pane can put the result back into the original field or into a new field in the same table, automatically creating the new field. We will do that so we can compare the starting text with the result of the transform template. If we wanted, we could put the result into a new table, which the Transform pane would also create for us automatically.
We press the Transform button and the result is:
The first comma and everything after it is replaced with nothing, that is, deleted. The great thing about doing a transform into a temporary field is that if we are not sure how regular expressions work, we can try different things to see what they do until we get it right.
The Transform pane is still loaded up with parameters as last used. We switch the result to (same field) and then we press the Edit Query button to see the SQL that Manifold would use to make that change to the address field. Manifold opens a Command Window loaded with the following query:
 $manifold$

 Autogenerated

 Replace
 Layer: Restaurants
 Field: [address]
 Replace: regular expression
 Search for: ',.*'
 Replace with: ''
 Ignore case: TRUE
 Result: [address]
 Resources: all CPU cores, all GPU cores
 Transform selection only: FALSE

UPDATE [Restaurants] SET
[address] = StringRegexpReplace([address], ',.*', '', 'i');
All we care about is the central expression that generates what the rest of the query uses to update the address field:
StringRegexpReplace([address], ',.*', '', 'i')
That is the expression we want to use for our computed field. With those arguments, the StringRegexpReplace function takes whatever text is in the address field and it returns a string that is just those characters up to, but not including, the first comma , character, ignoring case. That is exactly what we want to put into our new street computed field.
By "creating geometry" we mean creating geoms for objects. A classic example is creating points at the centers of area objects.
We begin with a drawing of Mexico showing provinces as areas.
We open the table: we would like to add a computed field of type geom that contains the center point for each area.
We launch the Schema dialog by choosing Edit  Schema. We press the Add button and choose Field.
We enter the name CenterGeom and choose geom as the type. We press Edit Expression.
Suppose we cannot quite remember the name of the function we would like to use? We enter center in the filter box, to see only those functions with center in their names. We doubleclick onto GeomCenterInner to add that function to the expression. We manually enter [Geom] as the first argument and 0 for the second argument, knowing that a tolerance of zero means automatic tolerance.
However, we see from the templates list that the function does not return a geom but an x2 coordinate pair value. We must use a different function to turn that into a point geom. We may dimly remember that is how it is done from reading other topics in this documentation, but if we do not remember the name of the function we can again use the filter box to help jog our memories.
We position the cursor above the expression in progress, and enter point into the filter box. Looking at the list, we doubleclick onto the GeomMakePoint function, recognizing that function as the one we want to use to convert an x2 value into a point geom.
Doubleclicking the template adds it to the expression. We can now use a few quick highlight and delete moves to edit the text, so that the GeomCenterInner expression we created earlier replaces <valuex2> within the parentheses of the GeomMakePoint function.
Using simple cut or copy and paste moves, or other simple editing moves based on what we insert using the expression builder is much faster than manually keyboarding. Our final expression is:
GeomMakePoint(GeomCenterInner([Geom], 0))
We press OK, and then back in the Field dialog we press OK as well.
In the Schema dialog we press Save Changes.
The new CenterGeom computed field appears in the table, filled with the point geom values it has computed for each record from the area geom values.
To create a drawing using the CenterGeom field, we rightclick onto Mexico Table in the Project pane and we choose Create  New Drawing.
We specify the name Mexico Centers for the new drawing, and we choose the CenterGeom field for geometry. Note that a computed field can be used like any other in such dialogs.
We press the coordinate picker button and specify Latitude / Longitude projection, which is what we know the area geoms in this drawing use. The point geoms we compute from those area geoms use the same coordinate system.
We press Create Drawing.
We can now drag and drop the new drawing into the map, and Style it using stars for the center points.
We may have many uses for centroid points automatically created from areas. For example, we may want two different versions of a drawing that shows real estate parcels in various locations: a version using areas when zoomed into a parcel and a version using the center point to show on a larger scale map that shows the locations of various parcels with a point icon.
Manifold System Release 8 provides a builtin collection of fields automatically calculated by the system, called intrinsic fields. Intrinsic fields are computed on the fly to report values of interest, such as the length of lines or the area of area objects. They are similar to how computed fields work in Release 9, except that Release 8 intrinsic fields are hardcoded, that is, built into every table. With Release 9, we can add only those computed fields we want, and we can easily customize them.
The following expressions provide examples of Release 9 computed field expressions similar to Release 8 intrinsic fields. See also the computed fields in Euclidean and Geodetic sections below for Release 8 equivalents, or better.
Branches 
GeomBranchCount([Geom])
Returns the number of branches in the object. 
Coordinates 
GeomCoordCount([Geom])
Returns the number of coordinates in the object. A triangular area contains 4 coordinates because the last coordinate is the same as the first to close the area. 
Type 
CASE WHEN GeomIsArea([Geom]) THEN 'area' ELSE (CASE WHEN GeomIsLine([Geom]) THEN 'line' ELSE 'point' END) END
Returns the object type  area, line or point. 
X or Longitude 
VectorValue(GeomCenter([Geom], 0) ,0)
X coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Longitude. 
Y or Latitude 
VectorValue(GeomCenter([Geom], 0) ,1)
Y coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Latitude. 
Centroid 
GeomMakePoint(GeomCenter([Geom], 0))
A Release 9 feature not available in 8: In a drawing's table we can create a computed field of type geom that is powered by the above expression, which creates a point geom at the centroid of the object in the Geom field. If we have a table with an area in each record, using the Schema dialog we create another field, perhaps called Centroid, of type geom and we enter the above expression. Automatically, as areas or other objects are added, deleted, or edited, the Centroid field will have a geom that gives the point location of the centroid of each object. 
The expressions below work for drawings in any coordinate system, providing results in whatever units are used by the coordinate system. The functions use compute Euclidean measurements, that is, measurements on the Euclidean plane. For accuracy, they should be used with coordinate systems that provide good measurement accuracy in the area of interest. For a step by step example of use, see the Example: Add a Computed Field to a Table topic.
Area 
GeomArea([Geom], 0)
Returns the Euclidean area of an area object in square units of measure used by the coordinate system. Returns NULL for lines and points. 
Bearing 
GeomBearing([Geom])
Returns the Euclidean bearing in degrees of a line object. Returns NULL for areas and points. The line must have a single branch. The bearing is computed from the first to last coordinate of the line. 
Branches 
GeomBranchCount([Geom])
Returns the number of branches in the object. 
Coordinates 
GeomCoordCount([Geom])
Returns the number of coordinates in the object. A triangular area contains 4 coordinates because the last coordinate is the same as the first to close the area. 
Length 
GeomLength([Geom],0)
Returns the Euclidean length of lines and areas in units of the coordinate system. NULL is returned for points. The length of an area object is the length of its boundary, that is, the perimeter of the area. The length reported for a branched object is the sum of the lengths of the branches. 
Type 
CASE WHEN GeomIsArea([Geom]) THEN 'area' ELSE (CASE WHEN GeomIsLine([Geom]) THEN 'line' ELSE 'point' END) END
Returns the object type  area, line or point. 
X 
VectorValue(GeomCenter([Geom], 0) ,0)
X coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Longitude. 
Y 
VectorValue(GeomCenter([Geom], 0) ,1)
Y coordinate of the center of the object, in units of the coordinate system. For Latitude / Longitude systems, this is the Latitude. 
We can create geodetic (computed over the ellipsoid) computed fields for any coordinate system, but when drawings are in the default Latitude / Longitude coordinate system we can utilize especially simple, low level expressions. These can be copied and pasted with no need to customize the expression for the component in use. The following expressions work with drawings in Latitude / Longitude coordinate system, using the WGS84 base, for any area of interest.
Geodetic Area 
GeomAreaGeo([Geom], 6378137.01, 0.08181919084262149, 0)
Returns the geodetic area of an area object in square meters. Returns NULL for lines and points. 
Geodetic Bearing 
GeomBearingGeo([Geom], 6378137.01, 0.08181919084262149, 0)
Returns the geodetic bearing in degrees of a line object. Returns NULL for areas and points. The line must have a single branch. The bearing is computed from the first to last coordinate of the line. 
Geodetic Length 
GeomLengthGeo([Geom], 6378137.01, 0.08181919084262149, 0)
Returns the geodetic length of lines and areas in meters. NULL is returned for points. The length of an area object is the length of its boundary, that is, the perimeter of the area. The length reported for a branched object is the sum of the lengths of the branches. 
We can use the ValueSequenceRandomInt SQL function to generate pseudorandom integer numbers. The general form of the function is:
ValueSequenceRandomInt(<count>, <limit>, <seed>) where <count> is the number of values to generate, <limit> is the maximum bound so that random values from zero to <limit>  1 are generated, and <seed> is any convenient value that varies by record, with mfd_id often being used. To generate a single random number, we use 1 as the <count>.
Random integer from 0 to 9, inclusive 
(TABLE CALL ValueSequenceRandomInt(1, 10, [mfd_id]))
Returns a random integer from 0 to 9, inclusive. 
Random integer from 0 to 99, inclusive

(TABLE CALL ValueSequenceRandomInt(1, 100, [mfd_id]))
Returns a random integer from 0 to 99, inclusive. 
We can use the expression as written above within the Expression tab, when creating computed fields, in the Transform pane, or in queries. If we need more than one random integer in the same expression, instead of using identically the same expression as above, we should vary the limit or the seed to get a different pseudorandom number.
Euclidean vs. Geodetic  Projections (another name for coordinate systems, as used in GIS) work by representing the spherical or ellipsoidal shape of the Earth as a flat, Euclidean plane. See the About Projections collection of topics in this documentation.
One reason projections were invented was to simplify the complicated calculations required to compute measurements on the 3D surface of a sphere, with even more difficult calculations required for measurements on the surface of a slightly flattened ellipsoid, a closer approximation to the shape of the Earth than a sphere.
A wellchosen projection does all the calculations up front to create a map that is a flat, Euclidean plane, on which measurements are then easy using simple calculations. Most classic GIS is oriented to such calculations, where part of the art for making accurate measurements is choosing a projection for the area of interest, the region in which we work, that provides good accuracy despite the simplification of making computations on a flat plane.
But that only goes so far, as no projection to a flat plane provides good accuracy over larger distances where 3D effects of the Earth's ellipsoid shape play a greater role. Measurements over the 3D shape of the Earth's ellipsoid are called geodetic measurements, with a full range of SQL functions provided within Manifold for making such measurements. To make such measurements using Manifold SQL functions, we normally have to provide arguments that capture whatever coordinate system we are using, so Manifold can make necessary conversions.
In the case of default Latitude / Longitude, we can take advantage of low level functions that require Latitude / Longitude in the drawing and only need specification of whatever Base ellipsoid is used. In the case of the WGS84 base used for Manifold's default Latitude / Longitude projection, the numbers used in the expressions above can be plugged in.
If we would like to build computed fields that perform geodetic measurements in drawings using coordinate systems other than Latitude / Longitude, we can use the technique illustrated in the Example: Expression Context and Computed Fields topic, where we first build a measure object using the CoordMeasureMake function and then we use that measure object with CoordMeasure... functions like CoordMeasureArea. That technique, however, requires specifying the name of the drawing used (so the coordinate system in use can be grabbed) and thus it is not a totally cut and paste proposition like the expressions shown in this topic.
Computed Fields and Constraints  An SQL approach to computed fields.
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: 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: Expression Context and Computed Fields  When creating a computed field in the Schema dialog, using the Expression Context tab can increase efficiency, legibility, and maintainability of the expressions that power the computed field.
Example: Create a Table with a Constraint  Create a simple table that includes a simple constraint upon one of its fields.