Get familiar with operator precedence
Quickbase acts on certain operators before it acts on others. This is called operator precedence. Operator precedence impacts the outcome of your formula.
- Quickbase reads operators in a predetermined order, not left to right
- You can change the order of operator evaluation by using parentheses
- Quickbase always starts reading a formula within the deepest set of parentheses
- Review operator precedence
Add comments to formulas
Comments help you and others understand parts of your formula. Use double slashes (//) to include comments in your formula. Everything you type from the double slashes to the end of the line is a comment, not part of the formula.
Example of a formula with comments
If ( Abs([x]) < 5, //test the value"Less than 5", //return one result"Not less than 5" //otherwise return another result)
Create formula variables
Formula variables can help you simplify complicated formulas. See Formula variables.
Working with null values
Most fields can have a special value called the null value. Null means that a field's value is undefined, or that no data is entered in the field.
Things you should know:
-
Checkbox and text fields are never null. A Boolean value is either true or false. For example, a checkbox field is either checked or unchecked.
-
Empty text fields ("") are considered text strings with zero characters, not null values
-
Not all functions can handle null values. For example, in the formula
[Field name]=nullthe equals operator requires a value so this example doesn't work. The equals operator can handle a zero, but not a null value.
Null is useful in formulas
For instance, if you want to get an invoice total only if the Delivered on field is filled out, create a formula in the total field that says: "If there's data in the Delivered on Date field, then add subtotal and tax.
Example of a formula with a null value
If(not IsNull([Delivered on]),[subtotal] + [tax],null)This means that if the Delivered on date field contains a value, then take the value in the subtotal field and add it to the value in the tax field. Otherwise, leave the field empty (null).
Few functions accept null as an argument
-
IsNull()function returns True if the field is null and False if it's not -
Nz()function returns zero if the field is null; otherwise it returns the value of the field
This is useful for calculations (see Null formula examples)
Tip: If you don't use theNz()function, you can treat a null in a numeric field as a zero to use it in calculations. To set this up, open the field's properties page and turn on Treat blank as zero in calculations checkbox. For numeric fields, Quickbase turns this setting on automatically.
Null formula examples
|
Get and display the value from the Temperature field. If the field is empty (null) use 98.6 instead |
|
If the Temperature field is null, then display the result 98.6. Otherwise, display the value from the Temperature field. |
|
Add the number of hours worked in a week |
|
Note: You'd use Nz() here instead of IsNull(). To add these values together, Quickbase needs the result to be a number. Nz() generates a zero for a null, which the program can use in the calculation. |
|
Calculate the Revenue field if a date is entered in the Submitted for Billing field Tip: To return a result where a value is not null, add NOT in front of the IsNull() function |
|
If there's a value in the Submitted for Billing field, then display the value from the Revenue Forecast field
|
Note: If a formula that includes a null function isn't working, access the field's properties page and turn off the Treat blank as zero in calculations checkbox.
Requiring unique values in formula fields
-
Require unique values in the formula field property page
Unique values could be used to:- Create autonumbering
- Concatenate multiple fields in a formula - text field to avoid duplicate records
- Formula fields that reference the following field types cannot be unique:
- Lookup
- Date/Time
- Address
- Record ID