Forum Discussion
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.- This is called short circuit evaluation. If you optimize for OR's for an early appearance of a true value you can likewise optimize for AND's for an early appearance of false. But wait, just replace the Formula language with JavaScript and you will get these two specific optimizations for free and a whole lot more.
- 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.
- Hi Michael,
Unfortunately we don't currently have a way to search the contents of formulas (meaning the formula code, rather than the formula results) so I recommend you consider the few tables which either have the most records or see the most traffic from your users. If those tables themselves have a very large number of formula fields you can go to the table settings > Reports & charts and see which reports are used the most. That way you can review the formulas which are used on those reports.
Here are a few other strategies you could use which may be quicker:
- Review formulas which are used on big reports which take a while to run
- Review formulas used on the app/table home pages of your most used apps/tables.
I hope this helps!
