Example: Transform Field Values using an Expression in the Transform Panel

This is a follow on topic, continuing onward from the preceding example of Copy one Column into Another Column using Transform and using the same data.   We show how the Expressions tab of the Transform panel may be used to change the values of fields.   We include an example of changing the price of selected products.


Let's continue on from the previous example to show how expressions may be used.




Recall that we had two products selected in the table.   If the Transform panel is not in sight we click the Contents pane and choose the Transform panel.


We click the Expressions tab in the Transform dialog and


ico_nb_arrow_blue.pngCheck the Restrict to selection box.




Next, we choose the Unit Price field in the top box and we scroll down in the fields, operators and functions display to where the Unit Price field is visible.




We double-click on the Unit Price field in the table scheme to automatically add the field name to the expression pane.   We also add the text * 1.1 to form the expression


[Unit Price] * 1.1


That means we are telling Manifold to multiply the value of the Unit Price field by 1.1 and to put the result into the Unit Price field.   Since we have left the Restrict to selection box checked that will apply only to the two selected records.




As soon as we create an understandable expression, the Transform panel will preview it in the table.  The preview uses a blended color that is a mix of blue preview color and red selection color.



We click the Update Field button to commit the action of the expression.




When we press the Update Field button we will commit the changes to the table, increasing the unit price by 10% for the two selected records.

Example: Transform Field Values using a Template

In the prior example we used the Expression tab.  In this example we will use the Template tab to accomplish the same task, increasing prices for selected records.  




We open the Products table.




Using Ctrl-Click we select two of the records.   We will increase the Unit Price for these two of Chef Anton's products   The Unit Price is 24.2 for the first product and 21.35 for the second product.  


We will use the Transform panel to modify the Unit Price field as the target field.



In the Transform panel we check the Restrict to selection box to apply the transform only to selected records.


We scroll down to and click on the Multiply template to chose that as our transform activity. As soon as we choose Multiply for the template two combo fields that work with that template appear in the dialog, a combo box captioned Value and a combo box captioned Multiply by.   


The Value parameter box is loaded with the same field as the target by default, and the Multiply by box starts off with mfd_id, the name of the first field in the table by alphabetical order.    The parameter boxes provide a pull-down list of all of the fields in the table to make it easy to click on one to use that field with the template.  If desired, we can choose the Expression setting or the Value setting, for those templates which allow it, to manually enter an expression or a literal value into the parameter box instead of choosing a field in the table.


The default choice of Unit Price field in the uppermost parameter box together with the default choice of mfd_id in the lower box can result in some odd values in the preview, at least until we choose more sensible values.  




The moment we chose Multiply as the template transform, Manifold went to work calculating what the results of that transform would be for whatever values are in the combo boxes and then previewing those values right away in the table.  Because the default setting of the combo boxes uses Unit Price in the upper box and mfd_id in the lower box, Manifold calculates a preview of what would be the value for the Unit Price field for each record if we took the value of the Unit Price field for that record and we multiplied that by the value of the mfd_id field for that record.    As soon as we enter a more sensible value in the lower Multiply by box, the preview will show what we expect.




For example, suppose instead of choosing a field we choose the Value setting for the Multiply by box and then we enter the fixed value 1.15 into the Multiply by box.




As we start entering a number into the Multiply by box Manifold in real time will update the preview in the table window.  On the way to entering the value 1.15 the moment we enter the first digit, 1, Manifold is already computing in real time what the preview should be and then updating the table to show the preview.   When we finish entering the value 1.15 the previewed Unit Price results in the table now become  27.83 and 24.5525, a 15% increase.




If we change the Multiply by value to 1.05, that is, a 5% price increase...




Manifold will preview the new Unit Price values.




We can keep going like this, entering different values into the Multiply by box, such as 1.07 above, to see what the resulting Unit Prices will be in the preview.  That allows us to interactively try out different percentage price increases to see if those cross any psychological barrier we may feel are important in the final price.


When we are happy with the preview, we can commit the change by pressing Update Field.




The final result shows the updated Unit Price values for the selected fields, as set by the transform template.

See Also



Contents Pane


Contents - Select


Contents - Transform


Transform Options


Command Window




Transform Templates


Transform Templates - Boolean


Transform Templates - Datetime


Transform Templates - Geom


Transform Templates - Numeric


Transform Templates - 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: Construct JSON String using Select and Transform - Use the Select panel and the Transform panel 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: Copy one Column into Another Column with Transform - How to use the Transform panel 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: Transfer Options and Merge Areas - Using the Merge Areas Transform panel template, an exploration of the difference between using Copy and Sum for transfer options.


Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform panel 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 Expression - Use the Expression tab of the Transform panel 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 Expression - Use the Expression tab of the Transform panel 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.


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


Example: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform panel, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.