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:
Create or edit
a report.
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).
Type in a Column label.
This label will appear as the column heading.
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.)
Write the formula.
The formula you craft here depends upon what you want the field to
do for you. Read about
composing formulas.
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 (
) button.
If this item does not display, there is no calculated column defined.
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.
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