An expression for "next record" in a shuffled slice

[Kirk_Masden]

I’ve made a flashcard app where learners work through a set of flashcards that have been shuffled randomly. When the learner taps an action indicating that he or she has successfully answered the question, the action removes that card from the slice and then automatically takes the reader to the next card (according to the order of randomized numbers assigned to the cards that remain). My app is working because I’ve figured out how to find the “key” of the card that should be next through the app’s Google spreadsheet. However, because the sync is not always instantaneous, the app occasionally shows a card that has actually already been completed. I’ve greyed out the text for such completed cards to show the user that he or she can ignore it, but I would much prefer that this kind of thing never happen.

So, here’s my question. Since the native navigation in the app obviously “knows” which card is “next,” I would like to be able to write an expression that gets the answer to the following question:

“What is the key of the next record, where ‘next’ means next in UX display order?”

My suspicion is that AppSheet cannot do this yet. If I can confirm that, I will repost this as a feature request.

Or, can anyone think of a work around that is possible with currently available expressions? As I say, I’ve figured out a work around on my spreadsheet that uses “rank” and other functions not available in AppSheet but I’d like to be able to do this in AppSheet to avoid the need to sync.

By the way, if the kind of expression I’m thinking about isn’t currently available, I think it would be a welcome addition that the currently available “context” expressions. Thanks for reading.

[Kirk_Masden]

Orderby() and Index() may help me solve this problem but I think I need one more function to work with Index. Consider the following example:

INDEX(LIST("Red", "Yellow", "Green"), 2) returns Text : Yellow
https://help.appsheet.com/expressions/functions/index

If I was currently working with “Yellow” and I wanted the next card in the list, I would first need a way to determine that “Yellow” is second in the list. If I could determine that “Yellow”=2, then I could easily get “Green” using Index() by adding 1 to 2. Is this possible? Would I need to figure out a way to use Count() to calculate how many come before “Green”?

[LeventK]

Hi @Kirk_Masden,
Is your LIST fixed or a dynamic one? For sure it could be very good provided we have a feature like indexOf just as in JavaScript, then it would be a lot easier Once I had a problem like this and @Aleksi had proposed a solution but frankly I don’t remember it. But I remember that his proposed workaround was easy to set up for small lists and would be cumbersome with long list of values.

[Kirk_Masden]

I’m not sure I understand the difference between a fixed list and a dynamic one but I think it might be “dynamic” in the sense that it gets smaller as the user works with it. When the user taps an action labeled “Got it” that card is eliminated from the slice. The user continues this way until all of the cards have been completed. Then, through another menu, the user asks for a new set of cards.

[Kirk_Masden]

I’m going to work on this today. I may be able to answer my own question about a work around. If I can, I’ll report here.

[Kirk_Masden]

When I was in elementary school I would sometimes go to my teacher to ask a question and then, in the process of asking, realize what the answer was in the process of formulating my question. I feel like an elementary school pupil again today. Here’s the work around I came up with:

index(orderby(select(D to W slice[Word],[Order]>[_THISROW].[ORDER]),[ORDER]),1)

In this expression the column “Word” contains the row key. Word is in the table “D to W” and “D to W slice” is a subset of that, which gets smaller and smaller as the user successfully answers questions. “Order” is the random number from my spreadsheet. The UX displays records in this order. This index expression correctly finds the key to the next record when there is still at least one record left. I have used Select to find all of the records with an Order number that is larger than the current record. This set is put in ascending order by Orderby and then Index picks the first of these, which is the “next” record – the one that I want.

When the user comes to the end of the list, I want the “next” record to be the first of the entire set (everything left in D to W slice). To deal with this contingency I use an “If” expression. Here’s the logic:

If we’re at the last record in D to W slice, go the the first record in it, if not go to the next one.

And here’s how that looks in my expression:

if(count(select(D to W slice[Word],[Order]>[_THISROW].[ORDER]))=0,
index(orderby(select(D to W slice[Word],[Order]<=[_THISROW].[ORDER]),[ORDER]),1),
index(orderby(select(D to W slice[Word],[Order]>[_THISROW].[ORDER]),[ORDER]),1))

Sorry to bother all of you with a question I wound up answering myself but perhaps this will be useful to others working on similar navigational issues. And, as @LeventK indicated, a feature like IndexOf in JavaScript would be nice to have in AppSheet so that this kind of complicated expression would not be necessary.

[Steve]

How do you randomly shuffle the cards?

[Kirk_Masden]

My current method is to use a column in my spreadsheet with the randomly assigned numbers. If the constant changing of these numbers becomes a problem, I’m considering a spreadsheet work around to generate a quasi random set of numbers that won’t change while the work is in progress.

[Kirk_Masden]

Initial tests of this work around gave me the impression that it was working properly but I later realized that it is very slow – too slow to be useful in my app. I think the app is trying to make the calculation on every record, even records that are not in the slice. I have hundreds of records but usually only deal with about 20 in any given slice, so restricting virtual formula calculations to a slice might make a big difference if it could be done. Does anyone have any ideas about how to make an expression like this one more efficient?

[Steve]

Kirk_Masden:

I think the app is trying to make the calculation on every record, even records that are not in the slice.

Correct.

I have hundreds of records but usually only deal with about 20 in any given slice, so restricting virtual formula calculations to a slice might make a big difference if it could be done.

It cannot, to my knowledge.

[Kirk_Masden]

In regard to the problem of the very slow response time, I’ve come up with another work around that replicates the conditions of the slice but doesn’t directly invoke a slice as a dataset. I’m not sure but the way I invoked slices in my expressions seems to have slowed things down. For some reason, the app seemed to be waiting for a sync before doing all of the calculations. Now my app is working reasonably well. The current approach (with AppSheet list expressions) is working better than what I started with 24 hours ago – a spreadsheet-based work around.

[LeventK]

@Kirk_Masden
The closest matching expression I can find is this for retrieving the index number of an item in a list:

(FIND("Yellow",CONCATENATE(LIST("Red","Yellow","Green"))) - FIND("Yellow",SUBSTITUTE(CONCATENATE(LIST("Red","Yellow","Green"))," , ","")))/3+1

Above expression returns:
1 - For “Red”
2 - For “Yellow”
3 - For “Green”

[Steve]

What happens if more than one “learner” (student?) is using the app at a time? How do you ensure the card shuffling done for each doesn’t affect the other?

Why do you remove the card from the slice after the learner has finished with it? How do you remove it?

My vision for this app:

Each student has a row in a Learners table. The app associates the current user with their own row.

A learner begins a flashcard session by tapping on a Shuffle or Start button that invokes an action that selects a set of cards, shuffles them, saves them as a list in the learner’s row, and resets a counter in the learner’s row to 1.

The app displays the card in spot 1 (per the counter from the learner’s row) of the learner’s shuffled list. When the learner taps the button to indicate they’ve finished the card, an action is triggered to increment the counter by one and display the card in the slot corresponding to the new counter value. The cycle continues until the counter is greater than the number of cards in the list.

If you wanted to record the learner’s answers, or individual card-completion times, you could also capture that in the learner’s row.

[Steve]

If you don’t want to identify each learner, you could instead identify each device. Add a user setting with an initial value of UNIQUEID(), set Editable? to OFF, and reference that column through USERSETTINGS() rather than using the user’s email address.

No matter what, you’ll need a shared table that differentiates devices or users to prevent concurrent users from interfering with each other. That table then gives you a lot more flexibility.

[Kirk_Masden]

As I alluded to above, I have each student sign up for AppSheet, copy my prototype and then use it as a single user app. I monitor their progress via their spreadsheets, which I have then share with me.

[Kirk_Masden]

I’ve made a “single user” app. Each learner has their own app and spreadsheet.
The reason for removing the cards is to focus on the learning tasks that remain. It’s a spaced repetition app which means that the cards that one “finishes” today will come back for further review later but if you’re able to answer correctly once, that’s good enough for the current session.
Each session has a session number. When a student taps “Got it” (the action designated for this purpose) that session number is written to the spreadsheet. If the session number in that column equals the current session number the card is removed by the slice.
Actually, I think my app is working pretty much the way you indicated. If you’re interested, the current version is here:

https://www.appsheet.com/portfolio/230844

All in all, I feel like I’ve built in most of the functionality I want but I’m trying to tweak it and redesign parts of it to make it sync faster and to eliminate the navigation problem I started this thread with.

[Kirk_Masden]

Quite a trick! Thank you very much! I’ll experiment with this. One thing I like about it is that, when you have an ordered slice, but are in a situation where Orderby() isn’t applicable, this may be a better way to find the next item in a list than my current method. Thanks again!

[Aleksi]

@praveen Could we have this kind of “IndexOf” expression?

[Kirk_Masden]

I’ve been continuing to work on this and would like to underscore @Aleksi’s call for an “IndexOf” expression. I’m currently building a new version of my app from the bottom up and have been paying attention to sync times as I add features. I just came across the following, rather amazing issue:

FILTER(“Main Data”, (and([DW Set aside]<>[Current session no],or(In([Current session no]-[Start at session no],{0, 2, 6, 14, 30, 63}),and([Current session no]=[Extra review],[Extra review]>0)))))

This expression works in a virtual column and its presence doesn’t seem to affect the sync time, at least not much. However, if I place this inside of an ORDERBY() as follows

ORDERBY(FILTER(“Main Data”, (and([DW Set aside]<>[Current session no],or(In([Current session no]-[Start at session no],{0, 2, 6, 14, 30, 63}),and([Current session no]=[Extra review],[Extra review]>0))))),[ORDER])

. . . the sync time suddenly jumps up from a couple of seconds to over two minutes. This is consistent. I get the same effect when I use “SELECT” instead of “FILTER.” Such problems make my want an “IndexOf” expression all the more.

By the way, this may be of interest to @Steve because of the work he has put in to documenting the ORDERBY() expression.

[Kirk_Masden · Accepted solution]

I think I finally have a solution. As first I tried to put Orderby() in a virtual column so that I could draw on the result later in in a LINKTOROW navigational expression but that use of the virtual column seems to have slowed things down (though some syncs not as slow as others, for some reason). Instead, I skipped the virtual column step and put the Orderby() expression inside the LINKTOROW expression, and now everything works and there don’t seem to be any speed problem. Here’s my solution:

LINKTOROW(INDEX(orderby(D to W[Key],[Order]),if(count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))>=count(D to W[KEY]),1,count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))+1)), “D to W_Detail”)

This is slightly complex so I’ll try to parse it:

LINKTOROW(INDEX(orderby(D to W[Key],[Order]),if(count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))>=count(D to W[KEY]),1,count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))+1)), “D to W_Detail”)

First of all, the part of the expression in bold determines the key of the record to navigate to in the “D to W_Detail” view. This can be further parsed as follows:

INDEX(orderby(D to W[Key],[Order]),if(count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))>=count(D to W[KEY]),1,count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))+1))

In this index expression, D to W is a slice. The list of records to be indexed within that slice is orderby(D to W[Key],[Order]. It’s put in the proper order with the Orderby() expression. The “Order” column has random numbers from the spreadsheet that are to be used to “shuffle” the records. Now, for the Index expression to work, we need to determine the number of the next record. There are two possible situations that need to be dealt with. First, if the record being shown is the last record in the list, then we need to navigate to the first record in the list, so we need the number one. That’s done in this part of the “if” expression:

INDEX(orderby(D to W[Key],[Order]),if(count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))>=count(D to W[KEY]),1,count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))+1))

The last part of the if expression is for records that are not the last one in the list. I add one to the number generated, which indicates what it’s “rank” in the list is, to identify the next record to be shown:

INDEX(orderby(D to W[Key],[Order]),if(count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))>=count(D to W[KEY]),1,count(FILTER(“D to W”, [Order]<=[_THISROW].[ORDER]))+1))

Well, that’s it. It was a difficult task for me but this seems to work correctly and, as far as I can tell at this point, there aren’t any speed issues.

[Kirk_Masden]

A footnote on my solution:

@LeventK’s innovated solution was based on counting the number of letters. That works but I tried something different – counting the number or records in the slice that have higher or lower order numbers. In both cases, the way of finding the “IndexOf” value that @Aleksi referred to was to compare counted values to determine the “rank” of the item in the list. I use “rank” fairly often in spreadsheets and I think it would be nice to have something like that in AppSheet.

Thanks for everything. Though it’s a little bit hard to implement, I’m glad to have a solution that works and appreciate all of the help I got along the way.

[Ami]

Hi
I have read through this and perhaps this is the solution i need.
I have a table called Curriculum. In it i have collumns: ID, ORDER, SUBJECT. The order is fixed per subject.
In another table i ref to the curriculum table to the subject as a label and the id as a key. I want my user to be able to select subjects only by their order and unless a subject is marked completed ( in a column) he cant progress to the next subject.
Is that what you have built?

[Kirk_Masden]

Thanks @1111!

I think you’ve come up with a good solution. I’m doing something similar. The thing about the native “next” function is that it can change depending on how data is sorted. If, for example, a header of a table is used to sort it and then the user goes to a detail view via that table, the “next” detail view will be calculated according to the sort that occurred previously. That’s hard (or, impossible?) for us to emulate.

[Aleksi]

Another way to calculate the rank… count the value from the original list and substitute with the list starting from the search value. I believe that would be the “shortest” formula.

[Kirk_Masden]

Interesting. I hadn’t thought of that. I’ll have to experiment with it. Thanks!