Use a Calculated (Formula) Column in a report

Imagine that you'd like to use a Formula field to show some special information in a report, but you don't want to clutter up your actual data with an additional field. Or say you don't have administrative rights to the application and therefore can't create a formula field of your own. No problem. QuickBase lets you create a Formula field as part of a report.

To create a calculated column:

  1. Create or edit a report.

  2. In the Report Builder, select Define a calculated column. Doing so creates a custom formula field that is part of the report, but not part of the application. This checkbox is usually in the Columns section of the Report Builder, but for report types where no Columns section is shown, it will appear in the Options section (Chart and Calendar reports) or the Summarize Data section (Summary reports).

  3. Type in a Column label.
    This label will appear as the column heading.

  4. Select a field type.
    Click the Type dropdown and select a field type. All these field types are Formula fields, even though they don't explicitly say so. (After all, you're creating a formula column.) The type you choose depends upon what result you'd like your formula to generate. (Get help with formulas.)

  5. Write the formula.
    The formula you craft here depends upon what you want the field to do for you. Read about composing formulas.

  6. Add the calculated column to the report.
    Within the Columns section, select Custom columns. Within the Available list on the left, select <Calculated column> and click the Add (addcolumn.png) button. If this item does not display, there is no calculated column defined.

  7. Click Display to see your new custom column.
    The new column should display the result of the formula you entered. This column looks and acts like it's part of the application.
    Did your formula return an error? Read about troubleshooting formula problems.

  8. Don't forget to save your changes.

Using a formula to shape your report's matching criteria

You don't always want to display the custom column. Many developers use a custom column to set special matching criteria that requires the help of a formula. Say you're creating a report, and you want to compare values in one field to values in another field. Or maybe you need to find records where one value is found within any of several fields. You can only accomplish these feats with the help of a formula. To achieve your goal, you first need to create a custom formula column, following the instructions in the preceding section. When you do so, select the field type Checkbox (again, all field types that appear in this dropdown are formula field types).

The formula you enter in the formula box depends upon what you want your report to show. For example, if you want to display records where start and finish dates have been entered incorrectly, you might enter the formula:

If([Finish Date] < [Start Date])

Or, say you want to search for records where a value may appear in a number of different fields

contains([Task name], "infrastructure") or
contains([Rollup Category], "infrastructure") or
contains([description], "infrastructure")

This formula says: If the Task Name field contains the text "infrastructure" then turn on the checkbox. Or, if the Rollup Category field contains the text "infrastructure" then turn on the checkbox. Or, if the Description field contains the text "infrastructure" then turn on the checkbox. Otherwise, turn of the checkbox.

Or, say that you want to show records where the current user's name appears in any one of three fields:

[Owner]=User() or
[Author]=User() or
[Reviewer]=User()

This formula says: If the Owner field contains the value that is the current user, turn on the checkbox. Or if the Author field contains the value that is the current user, turn on the checkbox. Or if the Reviewer field contains the value that is the current user, turn on the checkbox. Otherwise, turn off the checkbox.

Tip: You'll notice that none of the formulas above contains the usual arguments like "true" and "false" that tell QuickBase to turn on or off the checkbox. That's because you don't actually need them. QuickBase understands that if the condition is met, the result is true and turns on the checkbox. If not, the program automatically turns off the checkbox. This shorthand works with checkbox fields because there are only two possible results.

Next, put this new formula column to work in your report by including the Checkbox field within your matching criteria. To do so, go to the report builder's Filters section and select <Custom Column> as the field. Then, within the operator dropdown in the center, choose "is" and then type in "yes" as the matching criteria.

Related Topics

 

This page refers to an older version of QuickBase. Online help is now located at http://www.quickbase.com/user-assistance/default.html.

 

Return to top   

© 1999-2013 Intuit Inc. All rights reserved. Legal Notices.