This page introcudes some of the most powerful commands in Panorama. These commands allow you to automatically transform and modify large amounts of existing data. Many different kinds of transformations are possible, including mathematical calculations, re-arranging characters or words, transforming individual characters (for example converting from lower to upper case), and transformations based on patterns in the data.
Most of the commands in this chapter are found in the Math Menu. However, this doesn’t mean that only numbers can be transformed. Unless specified otherwise, these commands can transform all kinds of data, including text, numeric, dates, and choices.
Transforming Selected Data
The transformation commands described in this chapter may be used on an entire database, or on a selected subset. To learn how to select a subset of the database see Find and Select.
Filling a Field with a Formula
The Formula Fill command fills all the selected cells in the current field with the result of a formula. Any selected data already in the field is destroyed (although you can get it back with Undo). The formula result must match the data type of the field being filled. For example, a numeric formula can only be used if the current field contains numeric data. Panorama will display an error message if you use a formula that results in an incorrect data type.
To illustrate this command we will add $2.50 to the price of all tank cars in a hobby shop catalog database. The first step in making any bulk data change is to select the records to be changed. In this example the Live Clairvoyance wizard has been used to select Tank Cars.
The next step is to click in the column to be modified, in this case the Price column. Then choose Formula Fill from the Math menu and type in the formula.
Press Ok to actually modify the database.
If this is a Rails connected database there is one final step -- choose Upload All Changes to Rails from the Rails menu. (If you are doing several batches of changes you can wait until the end and upload all of the changes at once).
The Formula Fill command can any type of field, not just numbers. For example, suppose we want to mark items from IHC as discontinued. The first step is to select items from that manufacturer (see Find and Select).
Click anywhere on the Item field (or whatever field you want to modify), then choose Formula Fill and type in the formula.
This formula will append (Discontinued) to the end of every selected cell in the Item field.
Formula Operators and Functions
As a Ruby programmer you’ll find the general syntax and operation of Panorama formulas quite familiar. Of course the specifics are not exactly the same. To learn about all of the operators and functions available see Chapter 23 of Panorama Essentials or the Panorama Handbook.
Advanced Topics
For additional discussion about Formula Fill, as well as other commands for changing large groups of data, see Chapter 12 of Panorama Essentials or the Panorama Handbook.