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.
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 Writing formulas. -
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 Writing formulas.)
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 matchUse 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", "") |
|
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.