How to resolve "Expensive Format Rule" warning when checking child records from a parent table

AppSheet AppSheet Q&A
1

I am facing a performance warning in my app: “The condition of format rule ‘Overdue Alert’ may be expensive and slow the user experience of your app.”

My Setup:

  • Parent Table: Main (Key: Main_ID)

  • Child Table: Cost (Ref column: Main)

  • Relationship: The Cost table is set as “IsPartOf” the Main table. I have a Virtual Column in the Main table called [Related Costs] using the formula REF_ROWS("Cost", "Main").

The Goal: I want to apply a Format Rule (red text and an alarm icon) to the Main table rows if any of their related Cost records meet these specific criteria:

  1. End_Date is not blank.

  2. TODAY() is past the End_Date.

  3. [Total Days Run] is between [Total Time] and 1.5x [Total Time].

Current Expression in Format Rule:

Excel

ISNOTBLANK(
  SELECT(
    [Related Costs][Cost_ID],
    AND(
      ISNOTBLANK([End_Date]),
      TODAY() > [End_Date],
      [Total Days Run] > [Total Time],
      [Total Days Run] < FLOOR([Total Time] * 1.5)
    )
  )
)

The Problem: Even though I am using the [Related Costs] virtual column to limit the scope, AppSheet still flags this as “expensive”. I suspect it might be due to the TODAY() function or the math calculations running inside a Format Rule.

My Question: What is the best practice to trigger this formatting on the Parent table without hitting performance issues? Should I move this logic to a Yes/No Virtual Column in the Parent table, or is there a way to optimize the SELECT() / FILTER() expression further to satisfy the performance checker?

2

I believe any time you have a LIST function implemented you are going to get that warning. It’s just a warning that it MAY be expensive and that will depend on your application of the Format Rule and how big the [Related Costs] list grows to.

Also, keep in mind that Format Rules must search the entire app for usage of the target items you want the rule applied towards. If it is a heavily utilized area with lots of views displaying your target items, that adds to the overall performance degradation. Each time the data associated with the rule changes, the Format Rule needs to be re-evaluated for updates. This can be more expensive than a virtual column depending on frequency of the data changes.

Basically, your expression is creating a Slice of data on the fly. I would recommend creating a Virtual Column to collect these rows and move the expression to there. Then your Format Rul will only need to do ISNOTBLANK([New Row List]).

I hope this helps!

5 Likes
3

Please try below, in the Cost table , please add a Y/N type VC called say [Flag_Red_C] with an expression something like

AND(
ISNOTBLANK([End_Date]),
TODAY() > [End_Date],
[Total Days Run] > [Total Time],
[Total Days Run] < FLOOR([Total Time] * 1.5)
)

In the parent table you could have another VC of type Y/N called say [Flag_Red_M] in the parent table with an expression something like

IN(TRUE, [Related Costs][Flag_RED_C])

Now the format rule could have an expression something like [Flag_Red_M]

Yes, the above suggestion is based on 2 VCs. However the VC [Flag_Red_C] 's expression is based on values from the same record. The second VC in the parent table, [Flag_Red_M] is based on system generated reverse reference column. So it is likely less expensive.

The above suggestion basically shifts the computation to VCs rather than in the format rule.

6 Likes
4

Thank you to everyone who shared insights and suggestions :glowing_star:
Summary: I moved the logic from the Format Rule into Virtual Columns — one in the Cost table to flag the condition, and another in the parent table using the reverse reference — then made the format rule depend only on that column. This improved performance and removed the warning. :white_check_mark:

3 Likes
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.