Did you know you can now optimize OR statements in formulas?

In our last release, we made a change under the hood where we process OR statements in formulas faster. Consider this formula for instance:

 

IF(

            [SummaryField]>0 OR [My Checkbox field]=true,

            "Foo",

            "Bar"

)

 

Before the June release, even if the condition [SummaryField]>0 were true, Quick Base would also evaluate [My Checkbox field]=true. However, if the condition before the OR is true, than the whole OR statement will also be true. So, the optimization we just made was to stop evaluating the condition after an OR operator, if the condition before the OR is true. This means that your formulas which contain OR statements have gotten faster. But the main reason for my post, is you now have a new option for optimizing your formulas. If your formula contains an OR operator, the formula will run faster if you place the condition which will run faster BEFORE the OR operator.

 

But how can you tell which conditions will run faster?

 

A condition will take more time if...

-it includes a derived field (formula, lookup, or summary).

-It will take even longer if the derived field has a chain of derived fields behind it (ie � a lookup of a formula field)

-If it�s a summary field, it will take even longer if the child table has a large number of records.

-If it�s a summary field, it will take even longer if the field has additional criteria.

-it includes a Text � Multiple Choice field with a large number of options.

-it includes a Text � Multi-line field where -the records on the table have a lot of data.

-the condition uses the Contains function.

-two fields are being compared.

 

A condition will take less time if...

-it does not include a derived field.

-it uses the equals operator.

-a field is being compared with a static value.

Hi Brian, 

I have a text list of "Country" (USA, GBR, JPN), and I'd like to use a text formula to populate values for "Country Desc", where USA = United States, GBR = Great Britain, JPN = Japan. Can I use an OR function for this? 

Hi Andrew,

Do you have a different country description for each one of your countries? If so, I would not use an OR statement but you could instead use a formula similar to this:

Case([Country],
"USA","your USA description here",
"GBR","your GBR description here",
"JPN","your JPN description here"
)

It would be really cool to be able to identify all of my formulas across my app that have an OR condition in them, so that I could visually view them one-by-one to make sure the logic is optimized for short-circuit evaluation. I was hoping that maybe the Replace Term in Schema utility would help me here, but alas, it's not looking inside the formula code - just the names of the objects. Is there any way to do this, or do I have to go manually through all my 71 tables and probably over a thousand formula fields across my whole application to check on this? I doubt it would be worth the time, but it would be if I could quickly identify those formula fields that involved ORs.