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.

Formula checking

Troubleshooting formula errors

Resolving Formula Syntax Errors

Type Mismatch - Resolving Validation Errors

When Your Formula Draws a Blank - Resolving Evaluation Errors

Formula checking

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

Syntax Checking – Quickbase checks the formula syntax Quickbase also checks parentheses as you enter a formula. If you place your cursor to the right of an opening or closing parenthesis, Quickbase 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 Quickbase.
Validation – If the syntax of a formula is correct, then Quickbase validates the formula. During validation, Quickbase 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. For example:
Evaluation – When a formula passes validation, Quickbase evaluates it. Quickbase 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, 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.

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. This section reviews some of these reasons.

Resolving Formula Syntax Errors

If there is a syntax error, an error message pops up, for example:

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

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:

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