Troubleshoot Formulas

Real-time error checking for formulas lets you see if each line of a formula is correctly formatted as you enter it. Click the icon to view specific error messages.

In addition to checking for formatting errors, the real-time error checking feature also ensures formula arguments are the correct type (e.g., text, number, Boolean) and suggests fields and variables based on the expected type.

Once you save your formula, Quick Base performs three levels of checks:

  1. Syntax Checking – Quick Base checks the formula syntax Quick Base also checks parentheses as you enter a formula. If you place your cursor to the right of an opening or closing parenthesis, Quick Base will 1) draw a gray frame around the character, and 2) draw a gray frame around the matching parenthesis, if there is one. To learn how to construct a formula, read Using Formulas in Quick Base.

  2. Validation – If the syntax of a formula is correct, then Quick Base validates the formula. During validation, Quick Base makes sure that field references refer to actual fields in the application, and that the data type of your formula field and field references match those required by the functions and operators that you've used.

  3. Evaluation – When a formula passes validation, Quick Base evaluates it. Quick Base processes 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. (more about nulls.) Evaluation occurs at many times. For example, whenever you display a record, Quick Base 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, Quick Base evaluates the formula within each record.

Note: The result of the evaluation is not based on how many times the formula is evaluated.

Troubleshooting Formula Errors

There are a number of reasons why your formula might trigger an error.

Resolving Formula Syntax Errors

If there is a syntax error, an error message pops up.

For more about creating and structuring formulas properly, read Using Formulas in Quick Base.)

Type Mismatch - Resolving Validation Errors

The most common errors during the validation phase are Incorrect type errors. Incorrect type errors arise when a formula function or operator expects a different data type than the one referenced within the formula or in your expected result. Sometimes, you will receive a specific error message, such as "The field is a Date but the formula returns a Numeric result." Other times you will receive a more general message, such as "type mismatch."

To resolve incorrect type errors, you can convert one or more of the data types within your formula. You can insert special functions in your formula called "type conversion" functions. (To see a comprehensive list of all functions, consult the Formula Functions Reference.)

Below are some suggested fixes:

Work Dates and Dates don't match Use the ToDate() function to convert Work Date to a Date.

You want to...

Result field type:

Formula that returns an error

More information...

Solution

REvised FormulA

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.

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.

Convert Date/Time fields to Date 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.

Convert the 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...

solution

revised formula

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.

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. Actual Duration is a Formula - Duration type field.

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.

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.

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.

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. (What's a literal?)

You want to...

Result field type:

Formula that returns an error

More information...

Solution

revised formula

Color all projects assigned to the user Ebenezer red

Formula - Checkbox (in the Report Builder)

If([Project Lead]="Ebenezer", "pink", "")

Project Lead is a User type field.

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"), "red", "")

or use a user name instead of an email address:

If([Project Lead]=ToUser("EScrooge"), "red", "")

Mark records where the Start time is after 6 am

Formula - Checkbox

If([Start Time]>"6:00 am", true, false)

Start time is a Time of Day type field.

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

solution

revised formula

Calculate the number of checkboxes that a user turned on

Formula - Numeric

[Checkbox1] + [Checkbox2] + [Checkbox3]....

Checkboxes cannot be added like numbers even though they represent Boolean values (1 or 0).

Use the ToNumber() formula to return 1 for true or yes and a zero for false or no.

ToNumber([Checkbox1]) + ToNumber([Checkbox2]) + ...etc.

When Your Formula Draws a Blank - Resolving Evaluation Errors

Sometimes your formula will save, but when you go to the report, the field is completely blank. In these cases, the issue is often with 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:

 

Go back      |       

© 1999-2018  QuickBase, Inc.  All rights reserved.  Legal Notices.