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 custom column with a Formula:
In the report builder, select Custom
Options and then select Define a custom formula column
for this report.
Doing so creates a custom formula field that is part of the report,
but not part of the application.
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 custom formula column.) The type you choose
depends upon what result your formula will generate. (Get
help with formulas.)
Type in a label.
This label will appear as the column heading.
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 custom column to the report.
Within the Columns to display section, select Custom
columns. Within the list on the left, select <custom
column> and click the Add to report button.
Click Display to see your custom
column work its magic.
Your custom formula 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.
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 Filtering 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.