Troubleshoot Formulas

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.

Troubleshooting Formula Errors

So what happens when QuickBase rejects your formula? The answer depends upon where the wreck occurred.

Resolving Formula Syntax Errors

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.)

Type Mismatch - Resolving Validation Errors

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. To do so, you'll need the email address or user name of the user.

If([Project Lead]=ToUser("Ebenezer@ScroogeandMarley.biz"), "pink", "")

or use a user name instead of an email address:

If([Project Lead]=ToUser("EScrooge"), "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.

When Your Formula Draws a Blank - Resolving Evaluation Errors

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:

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 clear Treat blank values as "0" in calculations.

Related Topics:

 

Return to top   

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