To use the value of a database field within a formula, type the name of the field into the formula. This formula adds up the sum of three fields.
SubTotal+Shipping+Tax
When a field is used in a formula it always refers to the value of that field in the current record in the current database (the database belonging to the topmost window). As you move from record to record the result of the computation will change depending on the values in that particular record. (The only exception to this rule is certain functions like lookup( and fieldvalue(, which may refer to fields in other records or even other databases.)
If a field name contains spaces, numbers, or punctuation marks in it, you must surround the name with chevron characters (« and »). (Press Option-\ to create the « chevron character and Shift-Option-\ to create the » chevron character.) Here is a database with some unusual field names.
(Price, Quantity, Zip Code, P/E Ratio)
The first two names can be used without chevrons, but the last two require chevrons because of spaces and punctuation in the names.
Price
Quantity
«Zip Code»
«P/E Ratio»
Using the Current Field
A formula may use «» to refer to the current field without having to know what the current field is. For example, this formula converts the current cell to upper case.
«» = upper(«»)
If necessary, a formula can find out what the current field name is with the info(“fieldname”) function.
Line Item Fields
Line items are used for repeating items within a record (see Line Item Fields). Line item fields always end with a numeric suffix, for example Qty1, Qty2, Qty3, etc. Line item fields can be used in formulas just like other fields, for example:
Quantity3*Price3
When the current cell is a line item field the Ω symbol (press Option-Z) can be used as an automatic numeric suffix. Panorama automatically adjusts the suffix depending on what cell is currently active. For example, consider this formula using the Ω symbol.
QuantityΩ*PriceΩ
When this formula is calculated, Panorama will automatically substitute the correct line item number for each Ω symbol as shown in this table. If the name of the current field ends in 1 (for example Item1), this formula will be automatically be interpreted as:
Quantity1*Price1
It the name of the current field ends in 7 (for example Item7), this formula will be automatically be interpreted as:
Quantity7*Price7
If a formula containing the Ω character is used when the current cell is not a line item cell an error will occur. To add up a series of line items you can use the sum( function.
See Also
- Arithmetic Formulas -- mathematical operators and functions.
- Characters and Unicode Values -- working with individual characters of text.
- Constants -- values embedded into a formula.
- convertvariablestoconstants -- converts all of the variables in a formula into constant values.
- Date Arithmetic Formulas -- performing calculations on dates, and converting between dates and text.
- Formula Workshop -- formula workshop wizard for testing and experimenting with formulas.
- formulacalc -- allows you to evaluate a formula that you were not able to
code into the procedure when it was being written.
- formulafields( -- returns a list of fields used in a formula.
- formulaidentfiers( -- returns a list of identifiers (fields and variables) used in a formula.
- Formulas -- basics of formulas: components and grammar.
- formulavalue -- calculates the result of a formula. Usually this is done with an assignment statement (for example `x=2 * y` ), but the *formulavalue* statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
- formulavariables( -- returns a list of variables used in a formula.
- Functions -- index of all functions available for use in Panorama formulas.
- Linking with Another Database -- techniques for relating multiple database files so that they work together.
- makemergeformula( -- builds a formula from an “auto-wrap" style merge template.
- Non Decimal Numbers -- working with numbers in alternative (non-decimal) bases, including binary, octal and hexadecimal.
- Numbers -- Working with numeric values in a formula, and converting between numbers and text.
- Operators -- index of all operators available for use in Panorama formulas.
- Quotes -- text constants embedded into a formula
- Statements -- index of all statements available for use in Panorama procedures.
- SuperDates -- date and time combined into a single value.
- Tag Parsing -- Panorama functions for working with text that contains data delimited by tags, including HTML.
- Text Formulas -- manipulating text with a formula (concatenation, extraction, rearranging, etc.)
- Time Arithmetic Formulas -- performing calculations on times, and converting between times and text.
- True/False Formulas -- logical boolean calculations.
- Values -- the raw material that formulas work with -- numbers and text.
- Variables -- storing and retrieving individual items of data, not part of a database.
History
| 10.0 | No Change | Carried over from Panorama 6.0 |