Create Calculated Columns

Create Calculated Columns

Calculated columns allow you to include multiple data elements in a single cell on the Data Grid. For example, if you want to view a Profile’s address information in the data grid, you can include the entire address in a single block instead of displaying each field in the address in its own column. This allows you to see more information in a smaller space.

Note: Be sure to set the row height for your data grid to accommodate all of the calculated field data, otherwise you will not be able to see all of the data on the screen.

This procedure takes you through the process of creating an address block column, but you can use these steps to create other calculated fields – simply select different data to include in your field.

1. Follow the steps for Configuring View Settings.

2. On the Columns tab, add all of the fields that you want to include in your calculated column.

Tip: Set the fields that make up your calculated field as Hidden if you do not want them to display separately in the data grid.

Note: The Hidden option is only available in Full Data Grid views.

3. Click Add Calc to create a new calculated field.
Example: Calculated Field on Columns Tab

4. In the Label field, enter the label that you want to appear as the column heading in the view. The default value is calc-1 for the first calculated field, calc-2 for the second, and so on.

5. (Optional) If you want to change the default width for any of the columns, click in the W (width) field, and enter the desired width, in number of characters. To use the system-default value, leave the number at 0.

Note: You can also change the change column widths from directly within the data grid view itself. Refer to Resizing Columns for more information.

6. Click the arrow in the Formula drop-down box. The Select Formula window opens.

7. Select one of the following:

• Character: Displays the selected character fields in the data grid. This option is usually used to combine multiple text fields (for example, First Name and Last Name).
• Formatted Character: Displays the formula exactly as entered. Any text or carriage returns that you enter are displayed in the data grid. This option is usually used to create blocks of text (for example, an Address Block).
• Numeric: Calculates the selected numeric fields (for example, fee calculations, total gifts, or percentage of gifts given per year).

Note: If you select the Remove Blank Lines checkbox, the system will automatically remove any lines without data from the view. For example, if you have some Profiles that use Address 1 and Address 2, but others that only use Address 1, selecting this checkbox will show both lines for the Profiles that use both, but will remove the blank Address 2 line for Profiles that do not use it.

8. Select the field that you want to add to the calculated field and click Add Field.

9. Repeat this process to add any additional fields.

10. If you want to enter additional text elements to the formula, place your cursor where you want the text to display, and enter a plus sign (+) followed by a space, followed by the desired text, in double-quotes (for example, to add a comma and a space between fields, enter + “, “).

IMPORTANT: Always leave at least one space on each side of each element in your formula.

11. Click Check Formula to make sure that your syntax is correct.

12. Once you have verified your syntax, click Save Formula to save this formula for future use.

13. Click Select Formula to use this formula and return to the View Settings window. The selected formula will appear in the Formula field for the column.

Note: NOTE: If you click Cancel, the formula will be saved, but will not be associated with your calculated field.

14. Click Save to save the new view, and then click Generate View.

Tip: Calculation Methods

Character:

Displays the selected character fields in the data grid. Typically, this is used to combine two or more text fields.  An example might be to combine First and Last Name fields.

To use this calculation method, select the fields in the order you want to combine them and either click the Add Field button, or double-click the field to add it to the calculation. The system will automatically insert plus signs between the fields to indicate that they will be combined.

Formatted Character:

Displays the formula exactly as entered. Any text or carriage returns that you enter are displayed in the data grid. Typically, this is used to create a block of text like an address block.

Note: If you select the Remove Blank Lines checkbox, the system will automatically remove any lines without data from the view. For example, if you have some Profiles that use Address 1 and Address 2, but others that only use Address 1, selecting this checkbox will show both lines for the Profiles that use both, but will remove the blank Address 2 line for Profiles that do not use it.

Numeric:

Performs numeric calculations using the selected numeric fields (for example, fee calculations, total gifts, percentage of gifts given in a year).

When you add fields for a numeric calculation, a plus sign (+) is automatically inserted between the last field you entered and the new field. If you want to add the two fields together, leave the plus sign in place. However, if you want to perform a different calculation, select the plus sign and replace it with a different operator. Valid operators include:

• Addition (+): Adds two values together.
• Subtraction (-): Subtracts one value from another.
• Multiplication (*): Multiplies two numbers.
• Division (/): Divides one number by another.

Tip: Saved Formulas

When you create a formula to add to a calculated field, FIMS gives you the option to save the formula for future use. Keep the following in mind when you save formulas:

• Saved formulas will only be available in the formula list if all of the necessary data fields are present. For example, if you have a formula that combines First Name and Last Name into a single column, you must include both First Name and Last Name in your view in order to use the formula.
• Formulas are shared elements. If one user creates a formula, another user can use it (as long as they have included the appropriate data fields in their view).
• You can select a previously created formula, modify it, and save it as a new formula. The system will not overwrite the original formula.