Transform Templates

Templates which appear in the Transform Dialog depend upon the context in which the dialog is launched, as well as the data type of the field upon which the template will operate.


In the lists below what determines where a template is listed is the data type of the result of the template, that is data type of the target field.  For example, the Equal template gives a Boolean result of TRUE or FALSE depending on whether the two fields it compares are the same or not.  It requires a boolean type of target field to store that Boolean result, but the two fields it compares could be any type.  For example, it could see if two nvarchar string fields are the same or not, or if two numeric fields have the same value or not.


Because drawings are created from tables, when the Transform dialog is opened for a drawing the fields available will be those in the table.   Choosing a field other than a geom field will effectively be the same as launching the Transform dialog for the drawing's table and choosing that same field.   


Geom fields are different because some templates operating in the context of drawings that work with geometry can create new tables, while templates that operate on geom fields within tables are restricted to that table.   Therefore there are fewer templates for geom fields in tables than for geometry in drawings.


Most templates in the Transform dialog take their source values from a source field and put the result of what the template does into a target field.   If we make the source field the same as the target field then that becomes an operation on the same field.   For example, Absolute Value applied using the same source and target field will change all negative values in that field into positive values.  Choosing the Equal template and specifying the same field for both source fields will, of course, always result in a Boolean value of TRUE or 1.


Counting starts with Zero


Templates that count steps, such as the character position in a string, use counting that starts with zero:  0, 1, 2, 3, ...   The first position, such as the first character in a string, is position 0.   The second character is in position 1 and so on.


SQL Nomenclature for Values


When specifying values in option boxes, such as for source fields or comparisons, we can usually either choose fields of appropriate data types from the pull-down list or we can specify fixed values manually to use for all records.   When doing the latter, we use SQL nomenclature for specifying values.  For example,  datetime values are enclosed in # characters as in #12/15/2016 12:00:00# and strings are enclosed in single quote ' characters as in 'Eccentricity'.  Numbers cannot start with a decimal point but must have a zero before the decimal point for decimal fractions, as in 0.023.   



Combo Boxes can contain SQL expressions


Tech Tip: A totally cool thing about the Transform dialog is that we can write SQL expressions into the combo boxes that appear for various templates.   Most combo boxes will allow us to choose a field from a pull down list of fields of the appropriate type.  We can also enter a fixed value like a number or a string.   But we can also enter an SQL expression using the table's fields, for example, like the  COALESCE(Status, '') expression used in the source field combo box in the Concatenate template example for text fields.



Everything Math - For a handy reference to anything in mathematics, see the Wolfram MathWorld site.   Thank you Wolfram!


See Also

Transform Dialog


Transform Options


Transform Templates - Drawings


Transform Templates - Images


Transform Templates - Binary


Transform Templates - Boolean


Transform Templates - Datetime


Transform Templates - Geom


Transform Templates - Numeric


Transform Templates - Text


Transform Templates - Tile


Transform: Center and Centroids


Transform: Escape Templates


Transform: Overlay Topology


Transform: Voronoi Diagrams


Example: Two Drawings from the Same Table - Take a table with a geom field that is visualized by a drawing.  Add a second geom field to the table and create an rtree index on that field so it can be visualized by a drawing.   Copy the first drawing, paste it and adjust the pasted copy so it uses the second geom field. Demonstrate how to use the Transform dialog to show "live" modifications in the second drawing compared to the first drawing.


Example: Copy one Column into Another Column with Transform - How to use the Transform dialog to copy the contents of one column in a table into another column, but only for selected records.  Uses the Products table from the Nwind example data set.  


Example: Transform Field Values using an Expression in the Transform Dialog -  How the Expressions tab of the Transform Dialog may be used to change the values of fields.   We include an example of changing the price of selected products and using two different Transform dialogs open at the same time for two different table windows.


Example: Overlay Contained -  A frequent use of overlays is to sum the values of many points that fall within an area and to transfer that sum to a new field for an area.  In this example we take a drawing that has cities in the US with a population value for each city.  We use Overlay Contained  to sum the population of each city within a state and to transfer that sum to a total population for the state.


Example: Overlay Containing - One of the most common uses of overlays is to transfer fields from areas to points that are contained in those areas.    Tasks such as transferring a census block group number or zip code number from a drawing of areas to points that fall within each area are extremely common.   In this example we transfer the name of a French region  to the points that represent cities which fall within each region.


Example: Overlay Topology Intersect - In this example we use the Overlay Topology, Intersect template in the Transform dialog to trim a drawing of points so that all points which do not fall within areas in a second drawing are deleted.   The drawing of points we trim will become the US cities drawing that is used in the Example: Overlay Contained topic.


Example: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.


Example: Union Areas - Combine multiple area objects into a single area.   A drawing of French regions shows some regions as more than one area.  We would like each region to be one area so the table of regions has one record per region.


Example: Construct JSON String using Select and Transform - Use the Select and Transform dialogs to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.


Example: Edit a Drawing with Transform Dialog Templates -  In this example we open a drawing and edit objects in the drawing using the Transform dialog Template tab.   Includes examples of using the Add Component button and also the Edit Query button.


Example: Use a Transform Dialog Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform Dialog to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.


Example: Clip Areas with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.


Example: Smooth Lines with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to make lines smoother in a drawing so that longer lines are smoothed more.  Includes examples of using the Edit Query button to show how different queries are created automatically depending on if we want to update a field or to add a new component.