If you’ve ever used a spreadsheet, you’ll be very comfortable with how Panorama handles automatic calculations. You simply enter a formula in any field that you want to be filled with a calculation. To illustrate this we’ll use this simple database with four numeric fields for data entry (A, B, C and D) and two fields that we will be calculated (Total and Avg).
Use the Field Properties Panel to set up the formula associated with a field.
With the formulas set up, the Total and Avg fields will be automatically calculated when data is entered into A, B, C or D.
When the calculations are performed, the result of the calculation is stored into the database, just as if you had typed it in. Once the calculation result is stored in the database, it is just like any other data – you can perform selections based on these results, sort based on the results, etc. For example, you could use the Sort Down command (see Sorting Data) to rank the data from the highest to lowest average.
Since the calculation result is actually stored in the database, you can edit it just like any other data.
Of course you probably don’t want to edit calculated results. If you want to prevent a calculated field from being edited in the data sheet, uncheck the Editable option in the Field Properties Panel.
Recalculating an Entire Column
Panorama runs the automatic calculations any time data is entered manually, but it doesn’t update the calculation when the formula is changed, or if data is changed some other way (for example by importing or using the Morph dialog. In those situations, use the Field>Morph>Recalculate Field command to re-run the formula for every selected record in the database.
To illustrate this, suppose that the D column is deleted from the example database shown above. Once the D field is deleted you’ll need to adjust the formulas to remove this field. However, simply changing the formula doesn’t update the existing data in this field – the changed formula will only come into play when new data is entered. So after deleting the field and revising the formulas, you’ll need to recalculate, as shown in this movie.
Standalone Formulas
Unlike some other database programs, you don’t have to set up a field just to run a calculation. Panorama allows formulas to be used “bare”, not associated with any field. For example a formula can be used in various graphics objects, including Text List Object, Image Display Object, and Web Browser Object. When used this way, Panorama calculates the formula result, immediately displays the result, and then discards it. You only need to set up a formula associated with a field when you want to be able to do further operations with the calculation results, for example sorting, selecting, or analyzing.
Automatic Program Code
In addition to calculating a formula when data changes, Panorama can also run a short program. See Automatic Field Code to learn more about this.
See Also
- addfield -- adds a new field to the current database (on the end).
- Adding New Fields -- adding one or more fields to a database.
- autoallfieldwidths -- automatically sets the width of all fields based on the data in each field.
- autofieldwidth -- automatically sets the width of the current field based on the data in it.
- Automatic Field Code -- running a short program when data is entered into a field.
- automaticfieldchoices -- updates the current field's Choice list with actual data in the database.
- automaticfieldname( -- returns an available field name.
- cell -- enters a value into the currently active field (i.e. cell).
- checkdesignlock -- checks if field structure can be changed, if not, returns an error.
- commonfieldspopup -- pops up a list of common fields, and changes the current field specifications when a field is chosen from this menu.
- Construct Multiple Fields -- using a template to quickly add multiple fields to a database.
- constructfields -- creates one or more new fields based on a template.
- databaseconsoledump -- dumps the raw contents of the specified database to the console in comma delimited format.
- Date Patterns -- control how dates are displayed or converted to text.
- Dates -- working with dates.
- dbinfo( -- gets information about a database: what forms it contains, what fields, what flash art pictures, etc.
- deletefield -- deletes the current field from the database.
- Deleting Fields -- deleting fields from the database.
- Disable Editing of Individual Fields -- disable editing of inidividual database fields in the data sheet and/or forms.
- disablefieldediting -- disables editing for a specified list of database fields (all others are enabled).
- editfield -- begins editing of the specified field.
- editfieldname -- opens the data sheet window's field properties inspector and selects the field name.
- Field Blueprint Dialog -- examining and modifying the raw specification of a field.
- Field Properties -- available field attributes.
- Field Properties Panel -- examining and modifying field attributes.
- Field Width -- adjusting the width of a field in the data sheet.
- fieldalignment( -- returns the alignment of a database field.
- fieldformula( -- returns the formula associated with a database field.
- fieldname -- changes the name of the current field.
- fieldnumber( -- returns the number of a database field (starting with 1).
- fieldpattern( -- returns the output pattern associated with a database field.
- Fields -- introduction to database fields.
- fieldtype -- changes the data type of the current field.
- fieldtypes -- returns a carriage return delimited array with list of fields and field data types.
- fieldtypes( -- returns a carriage return delimited array with a list of the fields and field data types.
- fieldwidth( -- returns the width (in the data sheet) of a database field.
- findreplacedialog -- opens the Find & Replace dialog.
- firstcolumn -- moves to the first column in the data sheet (leftmost column).
- getfieldproperties( -- returns a dictionary containing all of the properties of the specified field. (See the setfieldproperties statement if you want to change one or more field properties.)
- hiddenfields( -- returns a list of hidden fields in the curent data sheet window
- hidecurrentfield -- hides the current field in the data sheet.
- hidefieldsbetween -- shows all fields except those in between specified numbers.
- hidelineitemfields -- hides all line item fields.
- hidethesefields -- hides specific fields in the data sheet, making all others visible.
- Hiding and Showing Fields -- temporarily hiding fields in the data sheet.
- info("datatype") -- returns the data type of the current field.
- info("disabledfields") -- returns a list of disabled fields in the current database (fields that cannot be edited).
- info("enabledfields") -- returns a list of enabled fields in the current database (fields that can be edited).
- insertfield -- inserts a new field into the database in front of the current field.
- lastcolumn -- move to the last column in the data sheet (rightmost column).
- Limiting the Maximum Number of Data Sheet Columns -- adjusting the maximum number of columns displayed in data sheet windows.
- Line Item Fields -- are used for repeating items within a record
- mergefieldsdialog -- opens the standard *Merge Fields* dialog.
- Merging Adjacent Fields -- merging two fields into one.
- movefieldbefore -- moves the current field to a new position.
- newdatabasewithfields -- creates a new database with one or more fields.
- newdatabasewithtemplate -- creates a new database with a template.
- Numeric Data -- numeric data (fixed and floating point).
- Numeric Patterns -- control how a number is displayed or converted to text.
- Rearranging Field Order -- rearranging the order of fields in the data sheet.
- reorderfieldsdialog -- opens the Reorder Fields dialog.
- serverdatabaseconsoledump -- dumps the raw contents of the specified database on the server to the console in comma delimited format.
- setfieldnames -- changes the names of all database fields at once.
- setfieldproperties -- modifies one or more properties (name, data type, formula, etc.) of the current field.
- showallfields -- makes every field in the data sheet visible.
- showcolumns -- forces Panorama to display specified fields.
- showfields -- forces Panorama to display specified fields.
- showfieldsbetween -- hides all fields except those in between specified numbers.
- showhidefieldsdialog -- opens the standard Show/Hide Fields dialog (in the Fields menu).
- showthesefields -- makes specific fields in the data sheet visible, hiding all others.
- Smart Dates -- keyboard entry of dates.
- splitfielddialog -- opens the standard *Split Field* dialog.
- Splitting a Field -- splitting a field into two fields.
- tokenname( -- returns the name of a field or variable (instead of the value contained in the field or variables).
- visiblefieldnumbers( -- returns a data array of visible fields (by number) in the data sheet.
- visiblefields( -- returns a list of visible fields in the data sheet.
History
| Version | Status | Notes |
| 10.0 | Updated | Carried over from Panorama 6.0, but now formulas and code are separate. |