From RPM Wiki
| Table of contents |
Summary
Non-editable fields that get their value from math performed on other fields.
Architecture
- Input fields: Each formula field has two input fields. One is called the primary input field and the other is the secondary input field.
- Value: Formula fields store their value (as opposed to generating it on demand). The value is calculated whenever one of the input fields is changed. An input field may itself be a formula field.
Types
There will be various types of formula fields. The type determines which input fields can be used and the format of the result.
Common setup
All formula fields are added from a single "Formula" type in the field toolbox.
- The first wizard page is where the user picks the type. Editing a formula field then skips that page (same pattern as reference fields). However, until we have more than one type of formula field we'll skip this page and assume a money type.
- The second page is where the user selects the input fields, the output format (if applicable), and the operator.
- It's fine for the same field to be selected for both inputs.
- Circular reference: The selected inputs are checked for circular references on OK. The check must work all the way up and down in the case of multiple formula field chains.
- No fields - invalid: If there are no eligible options for one or both of the input fields then we still allow the formula field to be created. However, the formula is invalid and will always have a null value.
- The reason we still allow the field to be created is that this situation can also occur to existing fields when one or more of their input fields is deleted. This is always why we allow an n/a input field option instead of just an n/a label when there are no options, because it in the case of a deleted field we DO NOT want to just select the next field automatically. We want to set it to n/a and then let the user edit the field to pick a different input.
Template and forms
The setup of formula fields for process management is available only in the template. Individual forms can have their formula fields changed or deleted like normal, but the formula setup can't be changed.
- Formula type is not shown in the form setup toolbox (can't add it to a form directly)
- Fields made invalid because of a form edit have a null value.
Template setup
Formula fields:
- Can not be selected for status triggers.
- Can not be selected to control field groups, but they can belong to (be affected by) fields groups.
- Can be title fields
Money formula
The first type we'll build is money.
Format
As with money fields (as of Shine) the user can select to display 2 or 4 decimal places. This only affects display since money values are always stored and calculated with 4 decimal places.
Input fields
The fields can come from the template or through references. Example: If there's an agency percent custom field then adding an agency reference to the template will allow money formula fields in the template to use the percent field stored with the agency for an input.
- To use a money or formula field from a reference, that field must first be added as a shared field.
- Eligible primary input field types
- Money
- Money formula
- Eligible secondary input field types
- Money
- Money formula
- Number
- Percent
Operators
The way the math works depends on the type of the secondary input. Basically we do the straight forward operation except for adding & subtracting a percent. For those 2 cases we first multiply the percent:
| * | / | + | - | |
|---|---|---|---|---|
| Money/formula | p * s | p / s | p + s | p - s |
| Number | p * s | p / s | p + s | p - s |
| Percent | p * s | p / s | p + (p * s) | p - (p * s) |
p = primary input, s = secondary input
Examples:
- $100 * 25% = $25
- $100 / 25% = $400
- $100 * $25 = $2500
- $100 / $25 = $4
- $100 + $25 = $125
- $100 - $25 = $75
- $100 + 25% = $125
- $100 - 25% = $75
Setup rules
- Default operator is *
- Default primary input is the first eligible field
- Default secondary input is the second eligible field (unless there is only 1)
Security
- Can be only "Hidden" or "Read"
- Default is "Read" for staff and agent users, and "Hidden" for customer users.
Use
Null
- Divide by zero: If the secondary field is 0 for a division operation then the value is null.
- Null input: If either input has a null value, the resulting value is null.
Unread
- Formula fields being updated on their own do not trigger form unread and email notifications. This is to prevent edits on customers, agencies, etc. from triggering notifications on forms that have formula fields that use them.
- However, a formula fields should still highlight as having been changed when there is something else that does set off the form's unread flag.
Reconciliation
Formula fields can be used for reconciliation comparisons.
History
- Shine: Feature candidate
Name
Not called "Calculated field" to avoid confusion with commission calculation. "Equation field" is the most likely alternate name, but "Formula" seems to be more commonly used (http://spreadsheets.about.com/od/excelformulas/ss/formula_begin.htm) when talking about Excel. Not called "Function" because in Excel those are specific things like "SIN" that are added into formulas.
- This page was last modified 17:15, 11 Jun 2009.
- This page has been accessed 4205 times.