The local Panorama database normally has the same configuration as the corresponding Rails table. In other words, the local Panorama database has the same fields in the same order as the Rails table. However, this doesn't have to be the case. For example, you might not want the local Panorama database to include "blob" fields in the Rails table, or you might want the Panorama fields to be in a different order than the fields in the Rails table. As an example consider this employee table, which has 13 fields:
To specify a different Panorama database field arrangement use the Local Field Configuration… dialog in the Rails menu.
The dialog lists all of the fields in the Rails table. Fields with a check mark will be included in the local Panorama database, unchecked fields will not (the default is to display all normal fields while leaving "magic" rails fields unchecked). You can also drag fields up and down within the list to change the order of the fields within the local Panorama database.
When the fields are set up the way you want them press the Update Configuration button. Track Magic will re-configure the fields in the local Panorama database. (In the process, all data in the local database will be removed, so Track Magic will offer to re-download the data from Rails.) Here is the reconfigured local Panorama database, with only five fields. The other eight fields are still part of the Rails table, but for now they cannot be viewed or edited in Track Magic.
Use the Save command in the File menu to permanently save the new field configuration. This will tell Panorama to continue to use this field configuration until you change it again.
Adding New Rails Fields to the Local Panorama Database
When you add one or more fields to a Rails table (see Adding & Removing Fields from a Rails Table) you'll probably want to add these fields to the corresponding local Panorama database as well. Simply open the Local Field Configuration… dialog and make sure the new fields are checked, then press the Update Configuration button. That's all there is too it!
Defining "Scratch" Fields in the Local Database
The Local Field Configuration… dialog also allows you to set up additional fields that are not directly linked to fields in the Rails table. These extra fields can contain calculated data for reports, or simply be used as temporary scratch areas (this can be especially handy for temporary results if you write Panorama procedures for analyzing your data).
To set up scratch fields, press the Advanced button in the Local Field Configuration… dialog. This expands the dialog to show a matrix of scratch fields.
To add a new scratch field, press the + icon in the upper right hand corner. This opens a new dialog that allows you to specify the parameters of the new scratch field.
At a minimum you must type in a name for the scratch field. You can also specify a data type (the default is text) and a formula (more on scratch field formulas in a moment). Press the Ok button when you're done — the new scratch field will be added to the list.
To modify the specifications for an existing scratch field simply double click on it. To delete a scratch field press the - icon to the left of the field name. You can also rearrange the order of the scratch fields by dragging fields up or down within the list.
When the scratch fields are set up the way you want them press the Update Configuration button. The scratch fields will appear towards the right edge of the database, immediately after the __rid field.
Scratch Field Calculations
If you specify a formula for a scratch field, Track Magic will automatically calculate the contents of the scratch field whenever data is downloaded from Rails. For example this allows you to calculate a scratch Age field from a Rails BIrthday field, or a Panorama date field from a Rails field that contains combined date and time information (this allows you to use Panorama's built in tools for summarizing information by day, week, month, quarter and year). Panorama has hundreds of available functions that can be used in formulas, and you can also create your own custom functions. Here are some examples of typical scratch field calculations.
| Formula | Notes |
| date(firstword(updated_at)) | Convert a Rails datetime field (in this case updated_at) into a Panorama date only field. The local scratch field must be specified as a Date field. |
| age(Birthdate) | Convert a birthdate into a person's current age. The local scratch field should be specified as an Integer or Float field. |
| upper(Name) | Convert text to all upper case. Similar functions include lower(text) and upperword(text) |
| firstword(Name) | Extract first word of the specified text. Similar functions include lastword(, nthword(, firstline(, lastline( and nthline(. |
| after(Email,"@") | The after( function extracts text that appears after a specified character. In this example, the formula could be used to create a scratch ISP field based on e-mail addresses in the Rails table. |
| obscuredigits(Card,4) | This formula could be used to create a scratch field that contains credit card information, but with all but the last four digits of the card number obscured. For example 1234-5678-9876-5432 would become XXXX-XXXX-XXXX-5432. |
| sandwich("",Name,"("+Title+")") | The sandwich( function assembles a text item from three smaller text items. The prefix and suffix are slapped on the ends of the root, just like a sandwich. However, if the root is empty, the prefix and suffix are also left off (the result is an empty text item), just as you wouldn’t make a sandwich without any meat. In this example the sandwich( function is used to create a composite scratch field with Name and Title. If the person has a title it will appear in parentheses. If there is no title then no parentheses will appears. |
| striphtmltags(Description) | If a Rails field contains HTML tags, this formula can be used to create a scratch field with the same text but with all of the tags removed. |
| nth(Rank) | The nth( function converts a number into an ordinal (1st, 2nd, 3rd, etc.) For this formula to work the Rails field (in this example called Rank) must be an integer, while the scratch field must be a text field. |
This table is just a small sampling of the functions available in Panorama. For more information about Panorama formulas please see Chapter 23 of Panorama Essentials. This book, like all Panorama documentation, is available as a PDF download from the ProVUE.com web site.