Using formulas
When modifying custom formats, you may want to add a field to a report or other document that displays the results of a calculation performed on application data. 1 way to do this is to create a custom formula on the
Custom Formats
screen.To open the
Custom Formats
screen, select .With custom formulas, you can create a formula once and save it for use on multiple reports. You can also create formulas by entering them directly into text boxes, but these won’t be saved for easy access with your other custom formulas.
note
Depending on the action you are taking, you may be working in the
Custom Formula
window or the OutputValue Formula
window, though the 2 windows are essentially the same. The Custom Formula
window is where you add a new custom formula (described here). The OutputValue Formula
window is where you modify an existing formula or text item from a custom format.Creating a simple custom formula
To add a custom formula to a custom format, first you need to set up the formula.
- In the list of formats on theCustom Formatsscreen, double-click the format you want to add the formula to. The format opens in the designer screen.
- In theFieldspane, right-click the top item,Custom Formula, and selectAdd Custom Formula. This opens theCustom Formulawindow.
- In theDescriptionfield of theCustom Formulawindow, enter a name for the formula. This should be descriptive so you can easily identify the formula later.noteTheDescriptionfield is required before you can save a custom formula. If you use theResetbutton to start over, remember to re-enter the description.
- If the formula involves Practice CS data, select theExpand
button to expand folders in the left pane until you find the appropriate item.
noteThe availability of data items depends on the type of document you are working with. For example, client information is available for a report related to clients, but other categories of information may not be available or limited to the most pertinent items. - To add an application data item to the formula, place the cursor where you want to insert the item and then double-click the item in the left pane.
- To perform a calculation on the data item after you add it, select 1 of the operator buttons to add, subtract, multiply, or divide.note
- Some data items, such as client IDs, dates, or text field entries, can't be used with operators.
- When you select 1 of the operator buttons, you won't notice any change until you add another item to the formula. The formula will then be properly formatted with the operator between the 2 fields.
- Double-click another data item to add it to the formula. Note that you can also enter parts of the formula manually in the text entry field.You can further refine the formula manually by editing it directly in the text entry field.
- When the formula is complete, selectOK. The formula is saved, and appears in theFieldspane under theCustom Formulaitem.
Adding a custom formula to a custom format
Once you have created a custom formula, you can add it to your custom format.
- In theFieldspane, select theExpand button to expand the Custom Formulaitem.
- Click and drag the formula you created into the formula designer, and drop it where you want it to appear.
- Modify the formula item as needed, just as you would for any other item, such as a text box.
Modifying a formula or text item
To modify a formula or text item on a custom format, double-click that item in the formatter. The
OutputValue Formula
window opens, where you can make changes to the text and/or formula, as described in the other sections of this topic.System formulas
The
Custom Formula
window includes, in the right pane, a collection of system formulas that address specific situations. The formulas available include dates, expressions, and math formulas.Dates - Add Days / Add Months
[Add Days(Today’s Date, 1)]
The
Add Days
formula enables you to output a date that is a specified number of days away from another date, such as today’s date. You can substitute another date variable for Today’s Date
by inserting it from the available application data items.The number after the comma determines the number of days away from the specified date that the formula should display. The default formula provided by the application, shown previously, would output tomorrow’s date (today’s date + 1 day). You can use a negative number to output an earlier date.
[Add Months(Today’s Date, 1)]
The
Add Months
formula is similar to Add Days
but adds months rather than days.Minimum Date
[Minimum Date]
This is a static reference date, 1/1/1900.
Date Format
[Date Format(Today’s Date, "MMMM d, yyyy")]
The
Date Format
formula enables you to specify the format for the date used in the formula. In the default formula, shown earlier, a sample date would be displayed as January 1, 2009. Here are some other examples:MM/dd/yy —
01/01/09
MMMM dd, yyyy —
January 01, 2009
yyyy-MM-dd —
2009-01-01
Today’s Date
[Today’s Date]
Outputs the current date when printed.
Date span
This
Formula Variable
can be used to count the span between 2 date variables. Place the earliest date first and the latest date last. For instance, if you wanted to calculate a client’s age you could compare the Individual Date of Birth
variable with the Today’s Date
variable.Expressions
IF
[IF(formula,true,false)]
Lets you create a condition that outputs different results based on whether the condition is met. Substitute a condition for "formula," such as a dollar value being greater than or equal to a specified amount, and substitute static values, application data items, or expressions for "true" and "false." The true value will print when the condition is met, and the false value will print otherwise.
To create the condition, you can use comparison operators such as greater than (>), less than or equal to (<=), not equal to (<>), and so on.
note
You can also create IF expressions using the
IF Expression
tab at the bottom of the Custom Formula
window.Math
Abs
[Abs(-2 * 12.3456)]
Outputs the absolute value, which is always positive. If a calculated amount results in a negative number, the Abs formula drops the minus sign.
Round
[Round(12.3456,2)]
Rounds numbers to a specified number of decimal places. Substitute an application data item or expression for "12.3456." The value after the comma is the number of decimal places you want used for the rounded result.
Truncate
[Truncate(34.5678)]
Converts a decimal to an integer by dropping the digits after the decimal point instead of rounding. In the example, 34.5678 = 34.
Combining formulas
You can combine formulas by inserting 1 into another and removing extraneous brackets. For example, if you want to output a future date and specify the date format, you can combine the
Add Days
and Date Format
formulas as follows.- In theCustom Formulawindow, double-click theDate Formatformula to add it to the text field.
- Enter a name for the new custom formula in theDescriptionfield.
- Double-click theAdd Daysformula to add it to the text field after the date format formula.
- Select the entire add days formula —[Add Days(Today’s Date, 1)]— and press CTRL + X to cut it.
- HighlightToday’s Datein the date format formula, then press CTRL + V to paste the add days formula in place of the highlighted text.
- Delete the square brackets from around the add days formula that you pasted, so that the only square brackets are at the beginning and end of the combined formula.
- Adjust the number of days added (plus or minus) and the date format as needed.
- SelectOKto save the formula.
Summary Properties tab
Use the
Summary Properties
tab when adding or modifying a field on a format that calculates a sum total, or other function, for a group of fields. You'll need to specify a calculation method (such as sum) for such a field, and you can also specify other properties as needed.Calculation Method
Determines the type of calculation to be used. For example,
Sum
is a simple total of the relevant fields.Group Total
Select the group section on which your summary type should reset. The selections are group headers, but the calculation reset also applies to footers.
If you make a selection in this field, you need to also select
SubTotal
in the Summary Type
field.Running Total
For interim calculations such as running totals, select
All
in this field. The nature of the calculation depends on your selection in the Summary Type
field and the placement of the field you are adding or modifying. Summary Type
Specifies the type of summary used in the field.
- GrandTotal— Total for entire report
- PageTotal— Total for current page
- SubTotal— Total for selected group section; needs to be used when a selection is made in theGroup Totalfield
- PageCount— Total number of pages in the report (value is the same regardless of the section in which it is displayed)
IF Expression tab
The
IF Expression
tab enables you to create IF expressions more easily using a set of fields. To use this tab, follow these steps.- Drag an item from the panes at the top of the window to the 1st field in theIF conditionrow. You can use the operators buttons to create a formula in this field using additional dragged items or manual entries.
- In theOperatorfield, select the appropriate operator, such as >= for "greater than or equal to."
- In the last field of theIF conditionrow, enter a condition (such as a numeric value) or drag a field from the panes at the top of the window to this field. You can use the operators buttons to create a formula in this field using additional dragged items or manual entries.
- In theThen printfield, enter or drag the value to be printed if the criteria are met, or leave the field blank to print nothing. You can use the operators buttons to create a formula in this field using additional dragged items or manual entries.
- In theOtherwise printfield, enter or drag the value to be printed if the criteria are not met, or leave the field blank to print nothing. You can use the operators buttons to create a formula in this field using additional dragged items or manual entries.
- When you are finished building the expression in theIF Expressiontab, use theApply IFbutton to insert it in the text field for the custom formula.noteYou can use theClear IFbutton to clear theIF Expressiontab and start over.