Manifold's Select pane and Transform pane are often used together to choose a desired set of records and to then make a change to those records. We can, of course, make such changes using SQL queries as well. However, many times it can be easier to make changes interactively using techniques like those shown in this topic. Even if our SQL skills are very strong we might be able to make changes more quickly through interactive dialogs than constructing an elegant query to do the same. The Transform panel also provides a "live" preview of proposed changes so we can use them to spot mistakes before they happen.
In this example we will create a new text field that will be used for a JSON string that will encode information within the table in a form that is portable to any other application that can use JSON. JSON (JavaScript Object Notation) is a stylish, lightweight data-interchange format.
Our starting table uses data taken from a database of Carolingian coin hordes discovered in Europe. Coin hordes are usually small groups of coins buried in centuries past, usually for safekeeping, and then never retrieved by their owners. Most coin hordes do not have precious coins, but hordes are still very valuable for archaeologists because the coins can be dated precisely and their origins identified, thus serving many useful functions as guide posts in deciphering the past.
Our table contains a field called Town where the coin horde was found and two fields called NonCoin and Photo which indicate whether archaeological items that were not coins also were found in the horde and whether a photo is available of the find. All fields in the table are nvarchar types, a typical situation in data published online. Most publication formats do not have more sophisticated types such as boolean types to indicate a yes or no situation like whether a photo is available or not.
Our task is to construct a JSON string that encodes the Town name, which we will call a "Site," plus the boolean status of the NonCoin and Photo fields. An example of the correct JSON string for the first record would be:
{ "Site": "Krinkberg", "NonCoin": true, "Photo": false }
As we can see, JSON is fairly obvious in how it encodes information. Strings are enclosed in double " quotes and values such as numeric values or boolean values are not quoted.
In this data set the yes or no status for photos and non-coin finds were represented simply by the letter Y if there is a photo available and also the letter Y if items in addition to coins were found, with no comment otherwise. The table therefore just has a Y character representing a "yes" and a NULL representing a "no." That is not a clean way of representing such a boolean status so when we create the JSON string representing that same information we will do so by using genuine boolean values, true and false, within the JSON string.
Our first step is to create an nvarchar field in the table for the JSON string, and to populate that field with the first part of the JSON string, that gives the town name. The Transform pane can simultaneously create the new field in the table and also populate it with the first part of the string, for the first record populating it with:
{ "Site": "Krinkberg
With the focus on the Coin Hordes table window, in the Transform pane we choose the Town field, and then we double-click the Concatenate template to launch it.
In the Concatenate template we choose start as the Add at option.
In the Value box, from the pull down menu we choose Value and we enter { "Site": " as the text characters to concatenate to the start of the Town field. We will not specify any Separator characters to put between the Value characters and the Town field.
For the Result destination, we choose the New Field, and then we enter JSON_info as the name of the new field, with a Result type of nvarchar.
To get a preview of what the template will do, we press Preview.
A preview column appears (which can be dragged left or resized) with the name of the template in the caption header at the top of the column. We can see right away the concatenation produces the desired result.
Press Transform to apply the transform operation.
The template adds a new field to the table, called JSON_info of type nvarchar, and it populates it with the text characters { "Site": " concatenated to the start of whatever is in the Town field for each record. In the illustration above we have adjusted column widths to provide more space for the JSON_info field, which will contain long text strings.
Our next task is to grow the JSON_info string by adding NonCoin status to the string. We begin by using the Select pane to select records where the NonCoin field has a Y value.
With the focus on the Coin Hordes table window, in the Select pane we choose the NonCoin field, and then we double-click the Search template to launch it.
In the Search template we choose text as the Use option. We choose contains as the Condition option. We could use equal (=) as the option, but contains has the benefit of not requiring an exact match or trimming, if the field contains space or other whitespace characters to either side of the Y character we seek.
From the pull down menu in the Value box we choose the default Value option, if it is not displayed by default, to enable us to enter the literal text we want to search for with the contains condition. We enter the text Y into the Value box.
We leave the default none choice for the Trim parameter, meaning we will not trim any characters (including any whitespace characters) from the Name field before making the contains comparison.
We leave the Collation choice at the default neutral, nocase setting, to capture upper and lower case variations of spelling. We could use the Collation setting to specify a language and language options if we wanted.
For Action, we use the default replace selection.
If we like, we can do a preview, but since selects are harmless (we can select and deselect all day long without changing data) we simply press Select.
The template immediately selects all records where NonCoin is Y, and indicates them in red selection color.
With the focus on the Coin Hordes table window, we can now use the Transform pane to concatenate the desired value to the JSON_info field for selected records.
The Transform pane, illustrated at left above, is still loaded with all values exactly as we last used it. We can quickly modify those as follows to get the illustration at right above.
In the Field box, from the pull down menu we choose the JSON_info field.
In the Add at box, from the pull down menu we choose the end option.
In the Value box we enter the text characters ", "NonCoin": true, with a space character (not visible in the illustration) at the end of the string.
In the Result box, from the pull down menu we choose the Same Field option.
We make sure to check
the Transform selection only box.
What all the options mean is to take whatever text is in the JSON_info field for each record, add the string ", "NonCoin": true, to the end of the text, and to put the concatenated result back into the JSON_info field, but to do that only for selected records.
Press Preview. We do not need to do a preview, but it is a good idea to check what is about to happen before committing a transform.
Previews that are limited to only the selection will show transform results only in rows that are selected.
Press Transform.
The template immediately updates the JSON_info values for all selected records, concatenating onto them the JSON text we specified. All of the selected records, that is, all of the records we selected for NonCoin being Y, now have their JSON strings extended so that the NonCoin entity in the JSON string has a boolean value of true.
We will now invert the selection, thus selecting all records where NonCoin is NULL, and for those records we will concatenate onto the JSON string a NonCoin entity value of false.
With the focus on the Coin Hordes table window, press Ctrl-I to invert the selection, or choose Edit - Select Inverse.
All records where NonCoin is NULL are now selected, and indicated in red selection color.
With the focus on the Coin Hordes table window, in the Transform pane we see that the same values are still loaded from the last operation.
We change the true in the Value string to false, for a text of ", "NonCoin": false, with again a space character at the end.
Everything else stays the same, including the checked box for Transform selection only.
Press Preview.
The proposed results look correct, being applied only to selected records.
Press Transform.
The result in the table is that all the selected records, those for which NonCoin is NULL, now have their JSON strings extended so that the NonCoin entity in the JSON string has a boolean value of true.
We continue this example by repeating what we did for the NonCoin field but now for the Photo field. We begin by selecting all records for which Photo has a Y value.
With the focus on the Coin Hordes table window, we use the Select pane.
The Select pane, illustrated at left above, is still loaded with all values exactly as we last used it. We can quickly modify the Field value to get the illustration at right above.
In the Field box, from the pull down menu we choose the Photo field. Everything else stays the same, since we are searching for all records that have a Y in the Photo field.
Press Select.
The template instantly selects all records that have Y in the Photo field, showing them in red selection color.
We can now use the Transform pane to grow the JSON_info string, by adding a JSON boolean value of true for a Photo entity.
The Transform pane, illustrated at left above, is still loaded with all values exactly as we last used it. We can quickly modify the Value entry to get the illustration at right above.
In the Value box we enter the text characters "Photo": true } with no space characters at the beginning or end of the string.
That is the only modification we need make. All other parameters stay the same, including the check in the Transform selection only box.
What all the options mean is to take whatever text is in the JSON_info field for each record, add the string "Photo": true } to the end of the text, and to put the concatenated result back into the JSON_info field, but to do that only for selected records.
Press Preview.
As the string we are builder gets longer, it becomes more difficult to see the entire preview within the small size of tables we use for illustrations. We have dragged the preview column all the way to the left of the table window, to provide more room so we can resize it as wide as reasonable to make a comparison. We mainly want to make sure that the transform will be applied only to selected records and that we correctly append the right text.
Press Transform.
The template immediately updates selected records by adding the string "Photo": true } to the end of whatever is in the JSON_info field. The result in the table is that all the selected records, those for which Photo is Y, now have their JSON strings finished with a Photo entity set to a boolean value of true.
We will now invert the selection, and then use the Transform pane once more to finish all the other records by adding a Photo entity set to a boolean value of false.
With the focus on the Coin Hordes table window, choose Edit - Select Inverse or press Ctrl-I to invert the selection.
Now, only those records are selected that have NULL in their Photo field.
With the focus on the Coin Hordes table window, we turn to the Transform pane again.
The Transform pane, illustrated at left above, is still loaded with the last set of parameters used. We quickly modify the Value entry to get the illustration at right above.
In the Value box we change the text string "Photo": true } to replace "true" with "false" to get "Photo": false } as the text string to use.
That is the only modification we need make. All other parameters stay the same, including the check in the the Transform selection only box.
What all the options mean is to take whatever text is in the JSON_info field for each record, add the string "Photo": false } to the end of the text, and to put the concatenated result back into the JSON_info field, but to do that only for selected records.
Press Preview.
As before, we have dragged the preview column all the way to the left of the table window. We mainly want to make sure that the transform will be applied only to selected records and that we correctly append the right text.
Press Transform.
The template immediately updates selected records by adding the string "Photo": false } to the end of whatever is in the JSON_info field. The result in the table is that all the selected records, those for which Photo is NULL, now have their JSON strings finished with a Photo entity set to a boolean value of false.
We choose Edit - Select None to deselect all records so we can admire the final result.
After thinking for a few moments, "Wow. Cool." we begin to realize that perhaps we made a small error. We've named the entity that gives the name of the hoard as Site in the JSON string but in the table the field is called Town. That is not a big deal but it could be that people who are familiar with this data set may expect the place name to be called Town. They might expect our JSON string to use the same name.
To avoid any worries we will change Site to Town in the JSON string. That is easy to do with the Transform pane.
In the Transform pane we press
the Up button to move up one
level so we can choose a different template.
With the focus on the Coin Hordes table window, we choose the JSON_info field, and then we double-click the Replace template to launch it.
In the Replace template we choose text as the Replace option. For the Search for text, we enter "Site":, and for the Replace with text we enter "Town":. We might be tempted to simply replace Site with Town but that could be risky in case one of the town names includes the letters site. So we instead search for and replace text that is the full JSON entity name, complete with colon.
For the Result destination, we choose Same Field, so that the JSON_info field is modified in place. Note that the Transform selection only box is not checked.
Just to be on the safe side, we press Preview, to get an advance look at what will happen.
The preview shows exactly what we want.
Press Transform.
There, that is better: the template has replaced all instances of the "Site": entity text in the JSON_info field with "Town": text.
We now have exactly the table we want, including a JSON string that can be consumed by other applications which correctly gives the values of JSON entities as strings. Better than a text field that sometimes contains the letter Y and sometimes a NULL, the JSON string now gives the values of NonCoin and Photo explicitly as boolean values of true or false. Explicit values of either true or false are safer to use since many applications cannot handle NULL values correctly.
Widely Applicable Work Flow - The heart of this example is to use the Select pane to choose those records on which we want to operate, to use the Transform pane to concatenate the text desired for selected records, and then to change the selection so we can make analogous changes to other records.
We can use that work flow as much as we want to make whatever changes we want using any of the templates that serve our purpose. In this example we built a string field through concatenation, but we could in other situations use similar work flow to edit string fields, to copy data between fields, to modify numbers or other values and so on.
SQL in Combo Boxes - This example uses either the names of fields or simple literal expressions in the combo boxes when using the Concatenate template. If we wanted to, we could use SQL expressions, including functions, in those parameter boxes as a combination between using the dialog alone or writing an SQL query to do what we want. To do that, we choose the Expression option from the pull down menu in the Value parameter box.
Suppose for example that we have built up the JSON string as follows, using the first record as an example:
{ "Site": "Krinkberg", "NonCoin":
We are now ready to add a true or a false value for the NonCoin entity. In the example we did something similar by first selecting all records where NonCoin was Y, using the Transform dialog to concatenate a true, inverting the selection and then using the Transform dialog to concatenate a false.
But we could accomplish all of that in one step by using JSON_info as the source field and in the Value box entering a single SQL expression:
CASE WHEN NonCoin IS NULL THEN 'false' ELSE 'true' END
For all records the CASE construction uses the Manifold SQL function IS NULL to see if NonCoin is NULL. If NonCoin is NULL, then the string literal false gets concatenated and if it is not NULL then the string literal true gets concatenated.
It is true that someone who is a real SQL wizard (and fanatic) may prefer to use the Command Window to write masterful SQL all at one go. But sometimes it is very convenient, especially with a preview of what will happen at hand, to simply plug a bit of SQL into a combo box to do in one step what otherwise might take several steps involving selection and transforms.
The Edit Query button - At any time we can press the Edit Query button to see what Manifold uses as an SQL query to implement what we have commanded in the dialog using the chosen template and the specified combo box values and options. That can be instructive to see how selections are used and how queries can be written.
For example, consider the last step of building the JSON string in the example above, where we had all records selected for which Photo was NULL, and, with the Transform selection only box checked, we concatenated the value:
"Photo": false }
If at that moment instead of pressing the Transform button we had pressed the Edit Query button the Command Window would have opened up loaded with the following query:
-- $manifold$
--
-- Auto-generated
--
-- Concatenate
-- Layer: Coin Hordes
-- Field: JSON_info
-- Add at: end
-- Value: '"Photo": false }'
-- Separator: ''
-- Result: (same field)
-- Resources: all CPU cores, all GPU cores
-- Transform selection only: TRUE
--
UPDATE CALL Selection([Coin Hordes], TRUE) SET
[JSON_info] = StringConcat([JSON_info], '', '"Photo": false }');
That is the query which appends the desired string to the existing JSON_info field, but only for those records that are selected.
The guts of the query are simple, a tsimple use of the StringConcat function. If we are learning SQL taking a look at queries like this can teach a lot, much of which is encouraging along the lines of "Wow! The basics really are simple." But at the same time if we want to take advantage of infrastructure like Selection, that can be easier to do in the Transform dialog than to expend the extra labor building even a simple query.
Coin hordes (or "hoards" in American English) are groups of coins that were buried or hidden in the past and then never retrieved by their owners. When discovered in modern times they provide a time capsule, a marker, that can help date the vicinity in which they are found.
Throughout history coins often have been made of durable metals, such as bronze, that have endured the centuries in recognizable form. Coins can often be dated to within a few years given the tendency of new rulers to mint new series of coins, so the discovery of a coin horde can pin the date of the location to within a few years. The identity of coins also tends to pin their manufacture to known sites of mints, so discovering coins far from where they were made can reveal patterns of trade.
Some coin hordes contain precious metal coins but most are petty coins buried on the eve of battle by soldiers or hidden by common people. Their great value today lies in their historical value as markers of time and place.
The illustration above shows coins, Christiana religio deniers, of Louis the Pious from the Häljarp, Sweden, coin horde discovered in 1905, the fifth row in our example table. The most recent coin in the horde dates from 840 AD. The obverse bears a cross surrounded by the Latin legend Hludovvicus imperator or "Emperor Louis", while the reverse bears a temple with a cross within surrounded by the Latin legend Christiana religio or "Christian religion".
Citation - The example data set of Carolingian Hoards used in this documentation was downloaded from dataverse.harvard.edu and is cited as Coupland, Simon; Maione-Downing, Brendan, 2013, "Geodatabase of Carolingian Coin Hoards: AD 751-987 (Version 1.1)", doi:10.7910/DVN/23984, Harvard Dataverse, V1
Example: Select a Range of Records in a Table - We can select or deselect a range of records between any two records in a table window using fast and easy mouse commands.
Example: Mouse Selection in a Drawing Window - Using the mouse to select objects in a drawing window. This could be a drawing layer in a map or simply in a drawing window.
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: Edit a Drawing with Transform Templates - In this example we open a drawing and edit objects in the drawing using Transform pane templates. Includes examples of saving results to a new component and also the Edit Query button.
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.