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

This is a follow on to the preceding example of Copy one Column into Another Column using Transform and uses the same data.   We show 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: Transform Field Values using an Expression

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

 

eg_trnsfd01_05.png

 

Recall that we had two products selected in the table.   If the Transform dialog is not open we right-click onto the column header for the Unit Price field and choose Transform.   This launches the Transform dialog with the Unit Price field already chosen.

 

We click the Expressions tab in the Transform dialog and check Restrict to selection.

 

eg_trnsfd01_06.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_07.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_08.png

 

The result will be previewed in the table.   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 will also show how two Transform dialogs can be open at the same time for two different windows showing the same table.

 

eg_transform_price01_01.png

 

We open the Products table again, with the original values.

 

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 22 for the first product and 21.35 for the second product.  

 

We right-click onto the column header for the Unit Price field and choose Transform.   This launches the Transform dialog with the Unit Price field already chosen.

 

eg_transform_price01_03.png

 

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 start off with Category ID, the name of the first field 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

 

The illustration above shows the table window with the Transform dialog moved over it so we can see both at the same time in an illustration that is not too large to fit into this documentation.    It shows the Unit Price values for the two selected records as 4 and not the original prices that were there.  Why is that?

 

After we chose the Unit Price field in the uppermost combo box of the Transform dialog, 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 Category ID field in both, Manifold is calculating a preview of what would be the value for the Unit Price field for each record if we took the value of the Discount field for that record and we multiplied by the value of the Category ID field for that record.  Since the Category ID field for both of the selected records is 2, the result of two multiplied by two is, indeed, 4 so that is what Manifold is previewing for us as the resulting Unit Price field for each of the two selected records.

 

eg_transform_price01_05.png

 

Using the pull-down menu for the combo box we will change the Category ID field that is in the Value box to the Unit Price field.

 

eg_transform_price01_06.png

 

The previewed Unit Price results in the table now become  44 and 42.7  since the Transform dialog computes the preview by multiplying the value of the Unit Price field for each record by 2, which is the value of the Category ID field for each record.  We will change the value of the Multiply by box so that instead of the Category ID field we will multiply by some specific number that we wish to use to increase the price.

 

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.  With 1 entered into the Multiply by box we are asking Manifold to multiple the value of the Unit Price field by 1, so the preview in the table shows that result, the original values in that field.

eg_transform_price01_08.png

 

If our intention is to increase prices 15% by multiplying them by 1.15 by the time we finish entering the 1.15 value Manifold has computed it and displayed the preview.  The new Unit Prices will be 25.30 for the first product and 24.55 for the second product.   We press the Update Field command button at the bottom of the Transform dialog to apply this transformation to the table, thus updating the prices on the two selected records.

 

Now, let's show how we can have two Transform dialogs open at the same time.    We click on the table so it has the focus and then we choose Window - New Window in the main menu to open a second window for that same table.   By default it will be named Products (2) since it is the second window for the Products table.

 

We also open a Transform dialog for that second window by choosing Edit - Transform.

 

eg_transform_price01_09.png

 

In the Products (2) table window we Ctrl-Click to select two other records.   In the Transform dialog we check the Restrict to selection box, we choose Unit Price in the top combo box, Multiply as the transform, Unit Price as the Value and we enter 1.07 as the Multiply by value.   The Transform dialog will show us a preview of what the resulting values will be in the Unit Price fields for the two selected records.  For example, the Unit Price on the Uncle Bob's product is previewed at 32.10.

 

We are doing this all at the same time that the previous table window is still open and the Transform dialog for that window open as well.  We have two different windows open into the same table simultaneously.  Each of the two windows has a different selection in it.   Each of the two windows also has a Transform dialog open that can be used to preview the effects of transforms in the window it serves without affecting anything in the other window until we press the Update Field button.   We can switch back and forth between the two different windows and their Transform dialogs.

 

eg_transform_price01_10.png

 

For example, suppose that when we looked at what prices would result if we increased the Unit Prices on Grandma's and Uncle Bob's products we decided we should increase prices on Chef Anton's  products even further.  The illustration above showing our first table window, the Products table window, and its Transform dialog gives a preview of what would be calculated if we multiplied the Unit Price on those records by 1.05, another 5% increase.   

 

This table window does not show the previewed computations for the Products (2) window.  For example, the Unit Price on the Uncle Bob's product is still shown as 30.00 and not as previewed in the other window at 32.10.

 

The prices in the table will change, and thus their display in all of the windows for that table will change automatically when we press the Update Field button to apply changes in any of the windows open for that table.   For example, if back in the Products (2) window we pressed the Update Field button then the previewed price of 32.10 would take effect as we applied the transform to the field.  That would change the field's value in the table and instantly all other table windows that showed that same table would show the new value in the field.  

 

eg_transform_price01_11.png

 

For example, the original Products table window would be updated as seen above to show the new Unit Price values that were commanded using the Products (2) window and the Transform dialog it has open.  In the illustration above the Unit Price in the Products window for the Uncle Bob's product has become 32.10 as a result of the change we commanded in the other, Products (2) window.

 

Obviously, if we open many different windows for the same table and then open for each one its own Transform dialog we might get confused which Transform dialog goes with which table window.  It is easy to avoid confusion by keeping an orderly desktop arrangement.  For example, we can use more than one monitor so we have plenty of screen area on which to lay out our Manifold desktop work area.   

 

It makes sense to utilize undocked table windows, Alt-clicking on the window's tab or title bar to undock it or to dock it, and to keep each open table window with its own Transform dialog next to it apart from the other table windows. We then can click back and forth between different Transform dialogs for each of the open windows and not get confused about which Transform dialog goes with which window.

 

 

See Also

Transform Dialog

 

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. 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: 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.

 

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.