Format rule slow to execute. How to speed up?

Hi All,
Is there a way to change the behaviour of a format rule to execute BEFORE a data sync has occurred?

I’m porting some app config across from our DEV app to our Test app but I’m stuck on the performance of a Format Rule.

In our DEV app, we have a format rule to change an action icon to a green tick after it’s executed.
In DEV, it changes the icon instantly, even before the action’s changes have synced to the sheet.

However our TEST app waits until AFTER the changes have synced to the sheet before the graphic changes.

In TEST, the icon does change correctly, so the format rules are working, but it’s significantly slower compared to the DEV version.

The source data sheet and calculations are (should be) close to identical.

App spec & goals:
Goal: Allow known people to check-in to our venue by selecting their own profile pic from a deck view.

Setup:

  • G-Sheet holding a list of members of our organisation.
  • Action to write an attendance log to a google sheet.
  • UX View to list all members in a deck view with the “Check in” action button on the action bar.
  • V-Column to count the number of checkins for that member today (see code below)
  • Format rule to change the check-in action button to Green if the v-column count is >0

v-column count code:
Attendance Count: Count a person’s check-ins today. Used by the format rule to mark a green tick.

COUNT(
  FILTER(
    "Attendance",
    AND(
      ([check-in Date] = today()),
      ([Membership Number]=[_THISROW].[Membership Number])
    )
  )
)

Pics attached below for reference.

Thanks in advance.
Tim

image2

A virtual column’s App formula expression is recomputed only when either of the following conditions occur:

  • A non-virtual column in the same row is updated, either in a form view or by an action.

    OR

  • When a sync occurs.

If you want the virtual column’s value to update outside of a sync, you have to make a change to its row.

2 Likes

Thanks @Steve , What I’m really trying to achieve is to quickly update the action’s graphic after the button has been pressed.

It works somehow in Dev, but not in Test. Here’s a screen-cap of the behaviour.

Dev (Fast)

Test (Slow) (Wait for the refresh…)

1 Like

It appears you’re using different approaches. Use the dev approach in test.

@Steve yes, that’s what I’m trying to figure out - The behaviour indicates there is a difference, but I’m struggling to identify where that difference could be configured.

I would fundamentally change how you’re going about this.


FYI: it’s a bad idea to have special characters in column names (the dash has got to go!)

I propose the following system instead:

  • Create a slice of your Attendance table with the following formula
    • TODAY() = [Attendance_Check_In_Date]
  • Inside the User table (or whatever you call it), create a Ref_Rows() to the slice.
    • You should already have a system built [Related Attendance] column
      • You can copy that formula, just switch out the first value in quotes (the table name) to the slice name.
    • Name it something like: [User_Todays_Attendance]

This will create a column inside your table that will hold the “today’s attendance records” for each user.

  • From here, switch up your Formatting Rule to the following:
    • ISNOTBLANK([User_Todays_Attendance])

The difference here is that by utilizing the Ref_Rows() system with a slice, you’re eliminating hundreds (if not thousands - if not tens of thousands (if you’ve got a lot of data)) of calculations that your app has to do all at once and on demand.

  • This system makes use of all the efficiencies built into the AppSheet platform
3 Likes

Thanks MultiTech, I really appreciate your reply.

I’ll have a go at switching to the method you suggest and report back.

For what it’s worth, I also discovered the reason why the TEST version was slow!
There was a mis-match between the key columns of the two versions.

The DEV Members table used MemberID as the key, and the Related Attendances ref’d this to the Attendance table.

The TEST Members table used a UniqueID column as the key, but “MemberID” was ref’d to the Attendance table.

I updated both apps to use (and properly reference) the UniqueID column as the key, and now the format icon updates instantly, while the data sync occurs in the background.

2 Likes