Before QuickBase accepts your formula, the program puts the code through its paces, checking to see if it will work. QuickBase executes this process in three phases:
1. Syntax Checking
First, QuickBase checks your grammar, or formula syntax. You can
glean meaning from the sentences you're reading here because the letters
are arranged in a specific order which makes sense to all readers of English.
QuickBase asks for the same consideration when you're composing formulas.
You must follow the grammatical rules of the QuickBase formula language.
If you're missing a comma or a parenthesis, QuickBase stops cold until
you fix it. This phase is called syntax checking and executes as soon
as you try to save the formula. (To learn how to construct a formula,
read Using Formulas in QuickBase.)
2. Validation
If the syntax of a formula is correct, then QuickBase validates
the formula. During validation, the program checks the meaning of a formula
(to the best of its ability, anyway—QuickBase is impressive, but it can't
read your mind). During this process, the program makes sure that field
references refer to actual fields in the application (watch your spelling!),
and that the data type of your formula field and field references match
those required by the functions and operators that you've used. If QuickBase
can make sense of a formula, the code passes validation.
3. Evaluation
When a formula passes validation, QuickBase can run or evaluate
it. Evaluation is when QuickBase puts your code to work for you by processing
the formula and producing the result. Some errors elude detection until
the evaluation stage. For example, if a formula refers to a field that
has since been deleted, the formula cannot work, and the result of the
evaluation is null. (In other words, your formula field draws a blank.
Read more about nulls.)
Evaluation occurs at many times, some of which might surprise you. For
example, whenever you display a record, QuickBase evaluates any formulas
it contains, so the program can display the result of the calculation.
Also, when you sort a table on a formula field, QuickBase evaluates the
formula within each record.
Tip: We have designed the QuickBase formula language so that evaluating formulas multiple times, or at unpredictable times, has no adverse consequences. For example, there are no language features or functions that would make the result of evaluation differ depending on how many times the formula is evaluated.
So what happens when QuickBase rejects your formula? The answer depends upon where the wreck occurred.
If the fault lies in your syntax, QuickBase cries out when you try to save
the formula. An error message pops up, which attempts to explain where
you went wrong.
QuickBase gives you a brief description of the problem and displays the formula. Often, the program starts underlining your formula where the code stopped making sense. In the illustration, the culprit is an extra bracket. That bracket is a typo and deleting it makes this formula work. While QuickBase's redline guidance can be very helpful, it can sometimes be misleading. For example, when QuickBase tells you that there's an extra character at the end of your formula and it underlines a parenthesis, the problem may be that you forgot to open a parenthesis somewhere in the beginning of your formula. Or your structure might be off in other ways. Because QuickBase doesn't know exactly what you want to do until you get your formula right, it's hard for the program to tell you where you've gone wrong. In fact, often a syntax error displays without any underlining at all.
To resolve a syntax error, start your investigation with the message QuickBase displays and work from there. You may need to edit and try the formula a few times before you find out exactly where you went wrong. (For details on creating and structuring formulas properly, please read Using Formulas in QuickBase.)
The most common errors that pop up during the validation phase are incorrect type errors. "Incorrect type" means that a function or an operator in your formula expects a different data type than the one you've referenced in your argument or expect in your result (which you've indicated by choosing a formula type like Formula - Checkbox or Formula - Date). Usually, QuickBase expresses this conflict with an error like: bad arguments, type mismatch, and so on. Sometimes QuickBase is more frank, telling you: "The field is a Date but the formula returns a Numeric result."
To resolve incorrect type errors, take a hard look at what you're trying to accomplish and make sure that you really do want to combine different types of values. If, after that, you remain convinced of your mission, you can make conflicting values play nicely together by converting one or more of the data types within your formula. You do so by inserting some special functions in your formula. Not surprisingly, these nifty tools are called "type conversion" functions. (To see a comprehensive list of all functions, consult the Formula Functions Reference.)
But how do you identify the best fix? Each situation is different. Here are some basic rules and accompanying examples to illustrate solutions to common data mismatch boondoggles:
Work Dates don't mesh with Dates. Use the ToDate() function to convert Work Date to a Date.
You want to... |
Result field type: |
Formula that returns an error |
More information... |
Diagnosis |
Solution |
---|---|---|---|---|---|
Find the duration between two dates |
Formula - Duration |
[Actual Finish]-[Actual Start] |
Actual Finish is a date field and Actual Start is a Work Date field. |
Must convert Actual Start to a date value. |
[Actual Finish]-ToDate([Actual Start]) |
Find the duration between two date/time values |
Formula - Numeric |
WeekdaySub([Date Modified],[Date Created]) |
Date Modified and Date Created are Date / Time type fields. |
The WeekdaySub() function requires dates as arguments and can't handle date/time fields. |
WeekdaySub(ToDate([Date Modified]),ToDate([Date Created])) |
Generate a date that's two weekdays after the event in a date / time field |
Formula - Date |
Weekdayadd ([date of meeting], 2) |
Date of meeting is a Date / Time type field. |
Must convert the whole result to a date. |
ToDate(Weekdayadd ([date of meeting], 2)) |
Numbers don't mix with Dates and Durations. Convert the number to a duration using a function like Days().
You want to... |
Result field type: |
Formula that returns an error |
More information... |
Diagnosis |
Solution |
---|---|---|---|---|---|
Calculate how many gallons are used per hour |
Formula - Numeric |
[gallons consumed] / [hours of operation] |
gallons consumed is a Numeric type field and hours of operation is a Duration type field. |
You can't divide a number by a duration. Convert the duration into a number. |
[gallons consumed]/ToHours([hours of operation]) |
Calculate the difference between the number of days a staff member estimates a job will take and the number of days it actually does take |
Formula - Numeric |
[Estimated Duration] - [Actual Duration] |
Estimated Duration is a numeric field into which staffers enter their estimated # of days. Actual Duration is a Formula - Duration type field which shows the number of days between the Start Date and Finish. |
You can't subtract a duration from a number. Use the ToDays function to convert the duration into a numeric value that shows number of days. |
[Estimated Duration] - ToDays([Actual Duration]) |
Find the number of months between two dates |
Formula - Numeric |
[end date] - [start date])/30 |
end date and start date are both Date fields. |
QuickBase can't divide time by a number. But it can divide time by a number of days or hours. Convert the number 30 to days. |
(([End Date] - [Start Date]) / Days(30)) |
Calculate depreciation based on time passed |
Formula - Numeric |
[elapsed months]*[depreciation this period] |
elapsed months is a Formula -Duration type field and depreciation is a Numeric field. |
Since you want to calculate a number, convert the duration value so you can multiply by it. |
ToNumber([elapsed months])*[depreciation this period] |
Calculate a task's finish date, based on the start date and number entered in the Estimated # of Days field. |
Formula - Date |
[Actual Start Date]+ [Estimated # of days] |
Estimated # of days field is a Numeric field. |
QuickBase can't add a numeric value to a date. It can only add a duration to a date. So, convert the number to a duration value of days with the Days function. |
[Actual Start Date]+ Days([Estimated # of days]) |
Literals often need to be converted to a data type in order for QuickBase to understand them. (What's a literal?)
You want to... |
Result field type: |
Formula that returns an error |
More information... |
Diagnosis |
Solution |
---|---|---|---|---|---|
Color all projects assigned to the user Ebenezer pink |
Formula - Checkbox (in the Report Builder) |
If([Project Lead]="Ebenezer", "pink", "") |
Project Lead is a User type field. |
"Ebenezer" isn't enough info for QuickBase to match this text to a user value. You must use the ToUser() conversion function for this formula to make sense. In order to do so, you'll need the email address or user name of the user. |
If([Project
Lead]=ToUser("Ebenezer@ScroogeandMarley.biz"), "pink",
"") |
Mark records where the Start time is after 6am |
Formula - Checkbox |
If([Start Time]>"6:00 am", true, false) |
Start time is a Time of Day type field. |
6:00am is just some text. You must
specify what this text represents with a conversion function. |
If([Start Time]>ToTimeOfDay("6:00 am"), true, false) |
Boolean (true or false - checkbox) values don't mix with numbers.
You want to... |
Result field type: |
Formula that returns an error |
More information... |
Diagnosis |
Solution |
---|---|---|---|---|---|
Calculate the number of checkboxes that a user turned on |
Formula - Numeric |
[end date] - [start date])/30 |
end date and start date are both Date fields. |
The ToNumber formula returns a 1
for true or yes and a zero for false. |
ToNumber([Checkbox1]) + ToNumber([Checkbox2]) ...etc. |
You type in your formula and save it without seeing an error message. Woohoo! But when you go to a report to see the formula's magical calculations in action, the field is completely blank. What happened? The culprit is often a problem in another field that the formula calls. Some things to check are:
Are all the fields that your formula refers to still part of your application?
If any of these are formula fields, are their formulas working correctly?
Does a field you reference include any strange values? For example, if a formula calculates the result of one field divided by another field, there will be an error if the divisor is 0.
Tip: If a formula that includes a null function isn't working, access the field's properties (either right-click the field in a table report and select Edit this field's properties, or select Customize > Tables then click the name of the table and field) and turn off the Treat blank values as "0" in calculations checkbox.
This page refers to an older version of QuickBase. Online help is now located at http://www.quickbase.com/user-assistance/default.html.
© 1999-2013 Intuit Inc. All rights reserved. Legal Notices.