MoneyWorks Manual
The Calculation Box Tool
The Calculation Box object is at the heart of the Forms designer.
Use a Calculation Box wherever you need to place information that will be different each time the form is printed.
Creating a New Calculation Box
A calculation box is “drawn” on the form just like a rectangle:
- Select the Calculation Box tool
- Click where you want one corner of the box to be
- Drag to where the diagonally opposite corner is to be and release the mouse button
The Calculation dialog box will open immediately
The calculation box will be created with a transparent fill and borders, but with the currently selected font, size and pen width. The vertical size of the box will “snap” to an integral number of line-heights for the currently selected font and size.
If you don’t want the calculation dialog box to open when you release the mouse button, hold down the ctrl key (Windows) or the option key (Mac) as you do so. A blank calculation box will be created which you can come back to later to add the calculation formula and other settings.
The Calculation dialog box is used to specify the calculation (formula) for the object, and (optionally) a result name, format and other settings.
Entering a Formula: You type the calculation formula into the Calculation box. To make things easier, all of the accessible field names, operators and functions are included at the top of the dialog box.
The “accessible” field names are those that are appropriate to the context of the form. An invoice form, for example, allows you to use the transaction and name fields. The transaction detail fields are also shown but these can only be used as parameters to the ExpandDetail function. This is because there will be several different values of each detail line field as invoices can have multiple detail lines. To include detail lines in an invoice form, use a List Box object.
See Calculations and things for more information on how to write a formula.
- Type the formula into the formula box, or select the item from the options above (double-click a field or function name)
MoneyWorks will check the formula for syntactic correctness when you click OK. If there is a problem, an alert box will appear to let you know what the problem is. See Errors for more information.
Result Name: The result of a calculation box can be included as part of the formula for other calculations on the form. In order to use the result of a calculation in this way, you need to give the calculation a name.
- To name a calculation box, type the name into the Result box
The name can be up to 30 characters. It must not start with a digit.
It is an error to include a calculation’s own name within it’s formula, or to use the name of another calculation that will in turn use the name of the first. This is called a circular reference.
Warning: Do not use the same name for more than one calculation box. If you do this, the results of all but the top-most calculation box will be incorrect. You will be warned of duplicates when you try to preview, print or save.
Apply Format: Use the Apply Format pop-up menu to apply a date or numeric format to the result. The format you choose will be used to format the result of the calculation provided that the result is of a type appropriate to the format.
The format will also be used whenever the calculation involves an implicit type conversion to the type for which the chosen format applies. For example, if the calculation is:
"Please pay by " + DueDate
then the selected date format (if any) will be used to convert DueDate to text so that it can be concatenated to the literal text. In this case, the final calculation result is Text, so no additional formatting is applied to this.
There are two check boxes for setting options that control the evaluation and appearance of the calculation and result.
Word Wrap: Set the Word Wrap check box if you want the text of the calculation result to be “wrapped” inside the text box. Text can only be wrapped if the box is more than one line high. The box will not grow to accommodate the text.If the word wrap option is off, and the text will not fit inside the box, it will be truncated to fit and an ellipsis (...) will be added to show that this has been done.
If you want numeric data to be correctly aligned on the right, you should turn the Word Wrap option off.
Note: If this option is off, the text will be vertically centred inside the box.
Always Calculate option: You need to set this option if the form may span multiple pages and the calculation includes the PAGE number or the SubTotal function or anything else that may cause the calculation result to be different for each page of a multi-page form.
The reason for this is that calculation boxes are normally evaluated once for each record being printed—before anything is actually printed. The same result is then output for each page if the form spans multiple pages. If the result may be different, the calculation needs to be redone for each page and you should set the Always Calculate option.
If you use the Always Calculate option, the calculation result will always be printed on top of any other objects that overlap the calculation box, i.e. an Always Calculate calculation box is forced to the front. This is because it is not evaluated until the other objects have been output to the printer.
Modifying a Calculation
To get back into the Calculation dialog box for a calculation box, double-click on the Calculation box, or select it and choose Modify Object from the Edit menu.
Errors
When you click the OK button in the Calculation dialog box, the Forms Designer will check that your formula is syntactically correct. That is, that the formula “makes sense”.
There are three possible kinds of error: Syntax Errors (where the mathematical grammar of the formula is wrong), Scope Errors (where a field or identifier name is used that does not exist or is not appropriate to use), and Semantic Errors (where the formula tries to evaluate something that cannot be evaluated).
Syntax Errors: For example the following is correct
"Minimum Payment Due: " + (Gross - AmtPaid) / 10
whereas the following is missing a bracket:
"Minimum Payment Due: " + (Gross - AmtPaid / 10
There is no way that the formula can be evaluated if its syntax is incorrect just as you can’t understand an english sentence if syntax incorrect, its is? (well maybe you can at a pinch, but you are considerably smarter than any computer program). Instead an error message is given, in this case:
Error: Expected a )
After you click the OK button in the error alert, MoneyWorks will highlight the point in the formula where it thinks the error is.
Scope Errors: Another kind of error that is checked for is the non-existence of identifiers used in the formula. For example, the formula
"Minimum Payment Due: " + (Gross - AmountPaid) / 10
is incorrect. This is because the identifier “AmountPaid” is not recognised. In this case, the error alert box allows you to exit from the dialog box because you may be planning to create another calculation box whose name will be “AmountPaid”. If not, you can click Edit to return to the Calculation dialog box and correct the error.
Semantic Errors: Often, semantic errors will not be detected until you actually try to print the form, because they are usually associated with the actual data operated on by the formula. For example:
A / B
is erroneous only if B is zero, causing a print-time “Divide by Zero” error.
Table of Errors
The following table lists common errors that you may encounter.
| Error | Explanation |
|---|---|
| Can’t add two dates | Dates cannot be added together (although you can add a number to a date.) |
| Circular reference | This error will occur at print-time if a formula includes its own name or includes the name of another formula that includes the name of the first formula. |
| Column not found | This error occurs if the column name passed to the SubTotal function does not exist in the list. |
| Date required | This error will occur if you pass a number as a parameter to a function that expects a date. |
| Divide by zero | You cannot divide by zero. |
| Expected a ( | You will get this error if you do not have an opening parenthesis following a function name. |
| Expected a ) | You will get this error if you do not have the same number of closing parentheses as there are opening parentheses in the formula. |
| Expression too complex | You may get this error if a formula is too complicated. Try breaking the formula up into sub-expressions and putting them into separate, named, invisible calculation boxes. |
| List not found | This error will occur if the list name that you supply to the SubTotal or ExpandList function is not known. Lists that are referred to in this way need to be named using the List Options command. |
| Subtotal unavailable | This error will occur at print-time if the calculation box does not have the Always Calculate option set. |
| Syntax error | This error will occur if the syntax of the formula is not correct. |
| Type conversion failed | This error will occur if you try to display the result of an ExpandDetail or ExpandList function. These functions return a special list type that can only be used as a parameter to a function that accepts a variable number of arguments. |
| Unknown identifier | The identifier is not known or is not available in the current scope. Check that the identifier is spelt correctly and that you are not trying to use a field from a subrecord in the main part of a form. |
| Unterminated quote | A literal text string did not have a terminating quote mark ("). |
| Wrong number of arguments | A function was given the wrong number of parameters (arguments). |