Using formulas

Alerts and notices

Check out the trending topics below or search for an answer.

Getting started
Contact us
Leave feedback

Internal Employees: Submit feedback using the new SharePoint request form

Please rate this topic.

Excellent    Good    Average    Poor

Did you find the information you need?

Yes    No

Name (optional):

Leave this blank:

Please tell us how we can make this topic more helpful.


Characters left:

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. One way to do this is to create a custom formula on the Custom Formats screen.

To open the Custom Formats screen, choose Setup > Custom Formats.

Custom formulas enable you to 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 will not 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 dialog or the OutputValue Formula dialog, though the two dialogs are essentially the same. The Custom Formula dialog is where you add a new custom formula (described below). The OutputValue Formula dialog is where you modify an existing formula or text item from a custom format.

Top of page

Creating a simple custom formula

To add a custom formula to a custom format, first you must set up the formula.

  1. In the list of formats on the Custom Formats screen, double-click the format to which you want to add the formula. The format opens in the designer screen.
  2. In the Fields pane, right-click the top item, Custom Formula, and choose Add Custom Formula from the context menu. This opens the Custom Formula dialog.
  3. In the Description field of the Custom Formula dialog, enter a name for the formula. This should be descriptive so you can easily identify the formula later.

    Note: A custom formula must be given a description, or it cannot be saved. If you click the Reset button to start over, remember to re-enter the description.

  4. If the formula involves Practice CS data, click the Expand Expand button button to expand folders in the left pane until you find the appropriate item.

    Note: The 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 be unavailable or limited to the most pertinent items.

  5. To add an application data item to the formula, place the cursor where you want insert the item and then double-click the item in the left pane.
  6. To perform a calculation on the data item after you add it, click one of the operator buttons to add, subtract, multiply, or divide.

    Operator buttons

    Notes

    • Some data items, such as client IDs, dates, or text field entries, cannot be used with operators.
    • When you click one of the operator buttons, you will not notice any change until you add another item to the formula. The formula will then be properly formatted with the operator between the two fields.
  7. 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.

  8. When the formula is complete, click OK. The formula is saved, and appears in the Fields pane under the Custom Formula item.

Top of page

Adding a custom formula to a custom format

Once you have created a custom formula, it is quite simple to add it to your custom format.

  1. In the Fields pane, click the Expand Expand button button to expand the Custom Formula item.
  2. Click and drag the formula you created into the formula designer, and drop it where you want it to appear.
  3. Modify the formula item as needed, just as you would for any other item, such as a text box.

Top of page

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 dialog opens, where you can make changes to the text and/or formula, as described in the other sections of this topic.

Note: For information on formatting text, see the appropriate sections of the topics Modifying custom formats and Custom formats toolbars.

Top of page

System formulas

The Custom Formula dialog 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 above, 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 above, 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.

Expressions

IF

[IF(formula,true,false)]

Enables you to 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 dialog.

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)]

Enables you to round 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 above, 34.5678 = 34.

Top of page

Combining formulas

You can combine formulas by inserting one 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.

  1. In the Custom Formula dialog, double-click the Date Format formula to add it to the text field.
  2. Enter a name for the new custom formula in the Description field.
  3. Double-click the Add Days formula to add it to the text field after the Date Format formula.
  4. Select the entire Add Days formula — [Add Days(Today’s Date, 1)] — and press CTRL+X to cut it.
  5. Highlight Today’s Date in the Date Format formula, then press CTRL+V to paste the Add Days formula in place of the highlighted text.
  6. Delete the square brackets [ ] from around the Add Days formula that you just pasted, so that the only square brackets are at the beginning and end of the combined formula.
  7. Adjust the number of days added (plus or minus) and the date format as needed.
  8. Click OK to save the formula.

Top of page

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 will need to specify a calculation method (such as Sum) for such a field, and you can also specify other properties as needed. More information on the properties on this tab is provided below.

Top of page

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.

  1. Drag an item from the panes at the top of the dialog to the first field in the IF condition row. You can use the operators buttons to create a formula in this field using additional dragged items or manual entries.
  2. In the Operator field, select the appropriate operator, such as >= for "greater than or equal to."
  3. In the last field of the IF condition row, enter a condition (such as a numeric value) or drag a field from the panes at the top of the dialog to this field. You can use the operators buttons to create a formula in this field using additional dragged items or manual entries.
  4. In the Then print field, 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.
  5. In the Otherwise print field, 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.
  6. When you are finished building the expression in the IF Expression tab, click the Apply IF button to insert it in the text field for the custom formula.

    Note: You can click the Clear IF button to clear the IF Expression tab and start over.

Top of page

Related topics

Custom formats overview

Practice CS Custom Formatting WalkThrough

Modifying custom formats

Custom formats field descriptions

Top of page

Share This