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:
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.)
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.
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:
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)) 
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]) 
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) 
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 rightclick 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 clear Treat blank values as "0" in calculations.
Related Topics:
© 19992014 Intuit Inc. All rights reserved. Legal Notices.