I have an unhealthy "ANY(SELECT(" addiction, please help!

[Jesse_Prud_Homm]

Throughout my novice app building endeavors, I've resorted to a lot of expensive, resource-hogging expressions. Here's one example...

"ANY(SELECT(Record Statuses[Status Type],AND([Record Type]="Work Order",[Status]=[_THISROW].[Status])))"

Would any of you have a better way to accomplish the above without looking up entire tables? We have been using my app for a couple years... works great but is now getting bogged down and I suspect these kinds of expressions are not helping.

Any insight is greatly appreciated!

[Joseph_Seddik · Accepted solution]

The expression itself is OK, what you need is to figure out ways to avoid putting such expressions in virtual columns, and in the grand majority of cases this is possible. 

What you can do for example:

• If the result of the calculation tends to be static, meaning that it is not expected to change with every sync, move your expression to a normal column instead of a virtual one.
• Consider using slices so that your data gets filtered beforehand and you'll get the chance to use dereference expressions instead of table lookup functions like SELECT() and co.
• Use behavior actions!! This is a very efficient way to update values based on data changes. You can trigger these actions through bot, or preferably when possible upon saving forms.  Usually calculations need to be re-evaluated following specific data changes well known to the app creator, thus you can:
  • Instead of embedding calculations into columns, do the calculation inside the action and update the relevant columns, or
  • Leave the calculation in normal columns, not virtual, and add a "Refresh" column of type number, in the table, and just make the action do a simple increment of this column, so that all other columns having formulas in the same row are forced to recalculate.
    
    

[Jesse_Prud_Homm]

This makes a lot of sense, thank you!

Making better use of bots seems like a huge efficiency boost!