CONTEXT("View") not operating as expected

[Daisy_Ramirez]

I’m needing to update key expressions to improve performance in an app. Based on my reading here, the recommendation is to include CONTEXT in the more expensive expressions. A requirement would need to be met before the calculations is completed, reducing the overall sync time. I’ve attempted the following expression and although the correct view is active the data is not appearing at all.

I’m not getting any errors but wondered if the expression is incorrect?

IF(
OR(CONTEXT(“View”)=“chart_quotes_by_sales”,CONTEXT(“View”)=“dash_administrator”),

Sum(
SELECT(Quote Header[vir_total_price],
AND([Status]=“Active”,[_THISROW].[Email]=[Assigned to],[vir_total_price]>0,[Quote Date]>=EOMONTH(Today(), -1)+1,[Quote Date]<=EOMONTH(Today(), 0))
)),

“”)

[Steve]

Your expression, reformatted for my clarity:

IF(
  OR(
    (CONTEXT("View") = "chart_quotes_by_sales"),
    (CONTEXT("View") = "dash_administrator")
  ),
  SUM(
    SELECT(
      Quote Header[vir_total_price],
      AND(
        ([Status] = "Active"),
        ([_THISROW].[Email] = [Assigned to]),
        ([vir_total_price] > 0),
        ([Quote Date] >= EOMONTH(TODAY(), -1) + 1),
        ([Quote Date] <= EOMONTH(TOdAY(), 0))
      )
    )
  ),
  ""
)

How I would write it for maximum efficiency:

IFS(
  IN(
    CONTEXT("View"),
    {"chart_quotes_by_sales", "dash_administrator"}
  ),
  SUM(
    SELECT(
      Quote Header[vir_total_price],
      IFS(
        NOT("Active" = [Status]),
          FALSE,
        NOT([Assigned to] = [_THISROW].[Email]),
          FALSE,
        NOT([vir_total_price] > 0),
          FALSE,
        NOT(EOMONTH([Quote Date], 0) = EOMONTH(TODAY(), 0)),
          FALSE,
        TRUE,
          TRUE
      )
    )
  )
)

[Daisy_Ramirez]

Thanks Steve. I just copy pasted the expression and received the following error. I can’t seem to figure out what missing. Replaced the curly brackets but still got the error.

[Steve]

Found and removed an extra parenthesis. Please try the updated expression above. Please also note that the curly braces are intentional.

[Daisy_Ramirez]

Got it. I tried the expression above. Here’s what I have:

[Steve]

Found and fixed a missing comma. Please try again.

[Daisy_Ramirez]

Got it. Ok formula generated no errors but unfortunately the field still doesn’t appear in those Views.

[Marc_Dillon]

Interesting method. Can you link some of the other threads that you found this in?

I don’t believe a VC is going to recalculate on a change in views. Only on a sync or a data change in the record. Perhaps if you hit the sync button once in the view? I’m not sure how CONTEXT(“View”) evaluates when the VC is recalc’d by the server on a sync though.

[Steve]

Marc_Dillon:

I don’t believe a VC is going to recalculate on a change in views.

Correct!

Marc_Dillon:

Only on a sync or a data change in the record.

Correct!

Marc_Dillon:

I’m not sure how CONTEXT(“View”) evaluates when the VC is recalc’d by the server on a sync though.

Likely to return a blank value, but also possibly not blank.

[Daisy_Ramirez]

Hi Marc, I’m looking for a way to prevent expensive expressions from calculating until the screen that requires them is displayed. Unless of course there’s a better way to improve sync performance as I have a client reaching 1.5 minutes in sync times now.

Any help would be amazing - thanks so much!

Here is the post I was referencing earlier:

Why certain virtual columns slow down syncs Questions

That is great great to know @Kirk_Masden. Thank you.

[Marc_Dillon]

I see. I think you’re missing a critical part, forcing a sync/calculation while in the context-matched view. See these quotes/posts from that thread that you linked:

Why certain virtual columns slow down syncs

Like you can put a quick edit toggle on the detail view to show hide the REFROWs, which is also tied to the virtual columns… So it only calcs the lists on demand…

Why certain virtual columns slow down syncs

As @Grant_Stead mentioned, if you tuck your Ref_Rows inside something like the formula above, but instead include a separate column (of the enum type) where people can toggle the ref-row on/off, you’ll find your sync times will decrease and performance in general will get better.

Notice that they’re referring to a quick-edit enabled Yes/No column in the detail view. This is their easy way to cause a data change, which then allows the virtual column to calculate.

[Grant_Stead]

You nailed it, you have to do/poke something to make the calcs run… Just passively going to the view probably won’t be enough.

[MultiTech]

Daisy_Ramirez:

Unless of course there’s a better way to improve sync performance as I have a client reaching 1.5 minutes in sync times now.

• Security filters
  • the biggest of them all right here; figuring out how you can limit the data inside the app is the best way to hepl improve speed;
• Try and eliminate any SELECT() formulas you’ve got running anywhere;
• Formatting rules need to be kept as simple as possible (basic equalities and IN() statements only);
• Reduce the number of Ref_Rows() if you can;

[Daisy_Ramirez]

Thank you. I used a security filter on the most complex table but there was minimal improvement. I’ll try to use it along with other adjustments such as the ones you mentioned. A few questions:

On replacing SELECT() formulas, what type of expression could I use that would provide the same result? Here’s a sample expression:

IF(COUNT([Related Quote Details])>0,
SUM(SELECT(Quote Detail[vir_price_extended],[quote_id]=[_THISROW].[quote_id]))+[vir_calc_commission],0)

On reducing the number of REF_ROWS():
How can I eliminate Ref Rows from the “Users” table for example when I’m needing to reference the name of an employee in the Quotes table? I have quite a few scenarios like this where we can do without the REF_ROWS in the “Users” table but the system automatically generates it due to the relationship.

Thanks so much for your help!

[Marc_Dillon]

Daisy_Ramirez:

On reducing the number of REF_ROWS():

Use Enum/EnumList (Base Type: REF) to De-Reference Data & Reduce Reverse-Reference Clutter Tips & Tricks 💡

Hey Everyone. We all know the power of references inside an AppSheet app, but there’s also some overhead associated with them; and by that I’m talking about: Reverse-References These are the [Related Whatever] virtual columns that appear when you create a reference connection between two tables. These are great, essential even, allowing you to now only view your data in a clear way, but also customize the functionality around your app as well. But sometimes you don’t need the reverse refer…

[Daisy_Ramirez]

Thanks Marc, love it!!

I’ll dig into the details and report back on any learnings.

Thanks again!

[Daisy_Ramirez]

I was able to shave off about 25 seconds from the ENUM changes and Security Filter on my largest table thus far! I’m still at a 1 min. and 10 sec. sync time.

My challenge is truly with the SELECT() expressions.

[Daisy_Ramirez]

Sorry but wanted to follow up on a good replacement for the SELECT() expression. Is there a good expression I can use that would generate the same results?

Thanks so much

[Marc_Dillon]

Daisy_Ramirez:

Sorry but wanted to follow up on a good replacement for the SELECT() expression. Is there a good expression I can use that would generate the same results?

I think the alternative is not to use them. Certainly move them out of virtual columns as much as you can.