Force immediate calculation

expressions
behavior
(Kirk Masden) #1

I’m working on an app where adjusting one value changes the number of records to be displayed in a slice. Apparently, slices are repopulated immediately, which is good. However, I would also like the count of that slice to be display immediately – and, of course, accurately. I tried a number of methods to get the count of the new slice using virtual columns but the correct count was only displayed after the sync had been completed – at least several seconds later. Worse yet, an incorrect count, based on previous data would be displayed until AppSheet finally allowed the virtual column to make the correct calculation.

Well, after a lot of trial and error (mostly error :sweat_smile:), I finally found a way to force AppSheet to display the new count immediately, without waiting for the sync:

Make an action that writes the new count (or whatever value you want) to your spreadsheet. Then, if you configure your app so as to display the content of that cell, it will reflect the new value immediately, even before the sync is complete.

Of course, writing to the spreadsheet takes time. Ironically, however, it seems that putting the expression in an action and telling AppSheet to write it to the spreadsheet tells the platform to prioritize the calculation.

This trick isn’t practical if you need to recalculate the value frequently. In my case, however, users will only wind up invoking this action occasionally, so I can live with it. The sync itself will occur in the background but the user will see the correct count immediately.

Hack to make versatile ref-like actions
(Aleksi Alkio) #2

Would you please describe this sentence little deeper… " adjusting one value changes the number of records to be displayed in a slice". What kind of value are you changing and how it it should reflect with the slice.

(Kirk Masden) #3

Thanks for your interest. For quite a long time now, I’ve been working on a flashcard app for language learners. With this app, students following a review schedule that is divided into “sessions.” The “cards” (records) that a student is to study in each session are selected through a slice, according to the session number that each individual card was begun in comparison with the current session number. It its the “current session number,” stored in a separate small table, that completely changes the content of the slice. The slice formula is a bit complicated, as a number of factors can lead to the selection of a card, but here’s a simplified version of it:

In(number(concatenate(SELECT(Session[Session number], ([Key] = 1))))-[Start at session no],{0, 2, 6, 14, 30, 63})

As you can see, the session number in the Session table is used to select cards for study based on the session number at which study was begun [Start at session no], and the number of sessions that have been completed since that first, beginning session: {0, 2, 6, 14, 30, 63}. This type of review schedule is called “spaced repetition” and a key feature of my app is allowing students to follow this kind of schedule in their review sessions.

So, when the value of “Session[Session number], ([Key] = 1)” changes, the content of the slice changes completely. As of yesterday the slice was working without having to wait for the spreadsheet to sync. However, I was having trouble getting the app to display the number of cards that remain in each session without delay. As the student studies, cards that have been successfully completed are set aside (I left that part of the slice expression out in order to simplify it for the purposes of this discussion). I wanted to give the each student an accurate, real-time count of the cards that remain but found that combining count() and select() did not work immediately when placed in a virtual column. Writing the first count() of the slice to another table, combining that with another expression to count the cards that had be set aside, and linking to that via a ref column solved my problem.

By the way, you @Aleksi, walked me through the method of using a ref column to display a number calculated on another sheet. I’m very grateful for that help. It has helped me get to my current situation, in which the app user can work unhampered by syncing delays, and I’m very grateful for that.

2 Likes