Setup (General)

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. 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.

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 select the
    Reset
     button to start over, remember to re-enter the description.
  4. If the formula involves DT Practice data, select the Expand 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, select one of the operator buttons to add, subtract, multiply, or divide.
    note
    • Some data items, such as client IDs, dates, or text field entries, cannot be used with operators.
    • When you select 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, select
    OK
    . The formula is saved, and appears in the Fields pane under the Custom Formula item.

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, select the Expand 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.

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.

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.

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. Select
    OK
     to 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 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.
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 must also select SubTotal in the
Summary Type
 field.
Running Total
For interim calculations such as running totals, choose
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; must be used when a selection is made in the
    Group Total
     field
  • 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.
  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, select the
    Apply IF
     button to insert it in the text field for the custom formula.
    note
    You can select the
    Clear IF
     button to clear the IF Expression tab and start over.

This article applies to:

  • Product: DT Practice