In this example we will use the Transform pane to copy the contents of one column in a table into another column, but only for selected records. We will use the Products table from the Nwind example data set.
Open the Products table and Ctrl-click in the rows for two records to select those records. The Units On Order value for both of the selected records is 0.
With the focus on the open Products table window, in the Transform pane we choose the Units in Stock field. We double-click the Copy template to launch it.
In the Copy template, we check the Transform selection only box. That restricts the operation of the transform to only selected records.
When working with selections, it is a good idea to begin by checking the Transform selection only box before specifying other parameters, in case we forget to do that later.
In the Copy template, from the pull down menu for the Result box we choose the Units on Order field. That will copy data from the Units in Stock field into the Units on Order field.
For a preview of what will happen, press the Preview button.
A blue preview column appears showing what will be the results of the template. The name of the column is the template being previewed. A dot appears in the column head of the destination column to show where the results will go. We can drag the preview column to different locations in the table, and we can adjust the width of the column by dragging the vertical sides of the column left and right.
The preview shows how a result will be created only for selected records. We do not have to do a preview before doing a transform, but previews can be a great way of preventing errors, by checking what will happen before we command it.
To apply the transform template, press Transform.
Data from the Units in Stock field is immediately copied into the Units on Order field, but only for selected records.
In the example above, we copy data from the Units in Stock field into the Units on Order field. If we prefer, we can fill the Units on Order field from an expression, which could be an SQL expression or a fixed value. For example, suppose we would like to copy into Units On Order a value that is half as many units as are in the Units In Stock field.
With the focus on the open Products table window, in the Transform pane we choose the Units on Order field. We double-click the Expression template to launch it.
In the Expression template, we press the Edit Expression button to launch the expression builder dialog.
In the expression builder dialog we enter the expression
[Units In Stock] / 2
and then we press OK to return to the Transform template. (Use of the expression builder is not illustrated above. See the Example: Transform Field Values using an Expression topic for illustrations.)
For the Result destination, we leave the default Same Field choice. That will write the results of the expression to the Units On Order field.
Check the Transform selection only box, to restrict application of the transform to only selected records.
Press Preview to see a preview.
Previews work with expressions, too. We can see how the template will put half of the Units In Stock numbers into the Units On Order field, but only for selected records.
Previews show floating point values (the numeric result of the expression), which will be truncated into integers when saved into the Units On Order integer field. If we wanted to see integer values, we could have used a different expression that rounded or did a CAST to integer values within the expression.
The Units on Order field values update to be half of the Units in Stock values, but only for selected records.
This example continues in the Example: Transform Field Values using an Expression topic.
Transform - Text
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.
Example: Transform Field Values using an Expression in the Transform Pane - How the Expressions tab of the Transform pane may be used to change the values of fields.
Example: Construct JSON String using Select and Transform - Use the Select pane and the Transform pane 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: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform pane 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 saving results to a new component and also the Edit Query button.
Example: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform pane, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.