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.

 

eg_trnsfd01_11.png

 

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 check Restrict to selection.

 

eg_trnsfd01_12.png

 

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

 

eg_trnsfd01_13.png

 

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.

 

eg_trnsfd01_14.png

 

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.

eg_trnsfd01_15.png

 

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

 

eg_trnsfd01_16.png

 

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.  

 

eg_transform_price01_01.png

 

We open the Products table.

 

eg_transform_price01_02.png

 

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.

eg_transform_price01_03.png

 

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 contents of the combo boxes for Value and Multiply by both start off with mfd_id, the name of the first field in the table by alphabetical order.    The combo 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 manually enter a value into the combo box instead of choosing a field in the table.

 

eg_transform_price01_04.png

 

We choose Unit Price field in the uppermost combo box of the Transform dialog.   That can result in some odd values in the preview.  

 

eg_transform_price01_05.png

 

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 the mfd_id field in both, as soon as we chose Unit Price as the field to use in the upper 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.

 

eg_transform_price01_06.png

 

For example, suppose instead of choosing a field we enter the fixed value 1.15 into the Multiply by box.

eg_transform_price01_07.png

 

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.

 

eg_transform_price01_08.png

 

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

 

eg_transform_price01_09.png

 

Manifold will preview the new Unit Price values.

 

eg_transform_price01_10.png

 

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.

 

eg_transform_price01_11.png

 

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

See Also

Selection

 

Contents Pane

 

Contents - Select

 

Contents - Transform

 

Transform Options

 

Command Window

 

Queries

 

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.