Solved: An expression for "next record" in a shuffled slic... - Page 2

Kirk_Masden · 04-26-2019 07:04 AM

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

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.

View solution in original post

An expression for "next record" in a shuffled slice