Using NESTED If-statements in formulas | INTERMEDIATE
If you’re not already familiar with if-statements, we suggest reading our introduction here. If you are already using if-statements, nesting if-statements within each other can make your formulas more powerful, by evaluating multiple test conditions.
Say your company conducts customer service training every year. In QuickBase you have a date field containing the [Next Training]. You can use a single if-statement to evaluate whether the employee’s training is still “Current” or if the training is “Past Due”:
Our Value Test asks if today is past the date. Our value if true returns “Past Due” and our value if false returns “Current”, indicating the training is up-to-date.
But what if we also want our formula to return the value “Due” if the training is on the current day. In this case we can extend the if-statement by nesting another if-statement inside our existing formula. In this case we add a test condition:
[Next Training]=Today() to test if the training is due today.
In this case we need to “nest” our old if-statement in the new if-statement. See below:
In the above example, our new If-Statement tests to see if the [Next Training Date] is equal to today. Our value, if true, is the value “Due”. Our value, if false, is our entire original If-Statement. In this case we are telling our formula to move on to our original statement if the Value Test is not true.
If you wanted to keep expanding this formula to additional tests and values, you can. Simply continue to nest If-Statements in your value, if true, and value, if false, formula criteria.
Below you can see the results of the two formulas under discussion. ‘_Status 1_’ uses the simple if statement, while ‘_Status 2_’ utilizes the nested formula.
Visit here to see these two fields in a live QuickBase environment:
For related subjects see our “Case formula” article here
- Author: Jake Rattner (email@example.com)
- Date Submitted: 12/10/2018