Iโm building an educational app to help students memorize vocabulary. For this app, Iโd like the โcardsโ to be presented in an order that is randomized or shuffled occasionally. In Google Sheets I could use RAND() or RANDBETWEEN(), but those get recalculated whenever a change occurs, and thatโs not what I want. I would like periodic (say, every three hours) shuffling of the order. By the way, in AppSheet, RANDBETWEEN() doesnโt seem to work for sorting because, in a virtual column at least, it produces the same number for every row.
Iโve been bothered by this sort of problem for quite some time now but I think Iโve finally found a solution. Iโd like to share it here and see what other participants in this forum think.
Since I want the recalculation to be periodic (every few hours), Iโll start with a big number of hours divided by the number of hours Iโd like between calculations:
(hour(now()-date("12/30/1970โ)))/3
At the moment, this produces 143,052 โ a pretty big number. The next thing I want to do is divide this number by each row number and make sure I have all the decimals that get generated. Itโs the decimals, not the integers, that will become the random numbers used to sort the column:
[_RowNumber]/decimal(floor((hour(now()-date(โ12/30/1970โ)))/3))
The results for the first three rows are as follows:
0.0000137030413900365186053044
0.0000205545620850547779079567
0.0000274060827800730372106089
Now, obviously, at this point, these number canโt be used for โrandomizedโ sorting because they get gradually bigger . . . just as the row number gets bigger. However, if we can chop out a chunk of numbers from the middle, weโll be left with numbers that go up and down in a way that seems pretty random:
Moreover, these numbers will change every three hours โ again, in a way that seems pretty random.
So, the question is how to accomplish that. Iโll omit a detailed explanation, but hereโs the expression I came up with. It may not be the most efficient way to do this but it works. Moreover, you can change the 3s (the number of hours between shuffling) to the interval of your choice:
floor(
((([_RowNumber]/decimal(floor((hour(now()-date(โ12/30/1970โ)))/3)))*1000000000)-
floor(([_RowNumber]/decimal(floor((hour(now()-date(โ12/30/1970โ)))/3)))*1000000000))
*10000
)
Note: FLOOR() gets rid of the decimals and leaves the integer. I wanted to do the opposite so I subtracted the the integer from the number with all of the decimals.
Iโve been looking at the results and have noticed some patterns in how things get shuffled. So, the results are not really random. Iโm pretty sure, though, the shuffling would appear to be quite random to a user.
I donโt know if anyoneโs interested but this is one more version of the same kind of expression. I added some spots to test different row number ranges and made it possible to manually add hours to test the time. As a result of that testing, I tweaked some other parts of the expression until I was a bit more satisfied with how it works under various conditions:
floor((((([_RowNumber]+0)/decimal(
floor((hour(now()-date(โ12/27/1900โ))+0)/3)
))*1000000000000)
-floor((([_RowNumber]+0)/decimal(
floor((hour(now()-date(โ12/27/1900โ))+0)/3)
))*1000000000000))*10000)
Again, itโs not truly random but I think its close enough to give the impression of randomness to users.
Maybe I donโt fully understand the question, but couldnโt you just fire an action that is prominent, and when you click on it, it sets a particular column on your table to UNIQUEID(). Then, you can set it to sort by that column, and it will. So whenever you want to shuffle it, you just click the prominent action.
I think that would mean writing a new figure to every row in the table. I think that would be very time-consuming (that writing process, that is). What Iโve described works in the background and itโs not necessary to do anything. Thatโs what I was hoping for.
May I ask where you put this expression? In a virtual column? In the google sheet? I am trying a variant of it. Canโt seem to get it to work right.
Hi! Here is the expression exactly as I have it in a virtual column in my app now:
floor((((([_RowNumber]+0)/decimal(
floor((hour(now()-date("12/27/1900"))+3+(LOOKUP("Cram", "Settings", "Key", "Number")*3))/3)
))*1000000000000)
-floor((([_RowNumber]+0)/decimal(
floor((hour(now()-date("12/27/1900"))+3+(LOOKUP("Cram", "Settings", "Key", "Number")*3))/3)
))*1000000000000))*10000)
LOOKUP(โCramโ, โSettingsโ, โKeyโ, โNumberโ) is the only part that should be unique to my app but itโs not really necessary. If you replace this with any number, it should work. Good luck!
Donโt know how many questions youre working with but maybe:
-Iโm thinking in the Vocab Cardโs table have a unique random string of 10 letters (Sort ID)
-User Table would have 2 columns that is their โsort by1โ โsort by 2โ numbers
-Set up a Virtual Column (Current Sort) for where if the โsort by 1โ was 8 itโs value would take out the 8th character and if โsort by2โ was 6 itโd add the 6th charter next.
Ex.) 'sort by1' =4 / 'sort by2' =6 / the card's 'Sort ID' =ajdyehcyso
'Current Sort' for that card would =yh
-Allow the View to be sorted by the Current Sort VC.
-An action button could be added that generations a new โsort by1โ and โsort by2โ
Didnโt see you found answer. Like that your answer is based off of time; clever!
Was a fun problem to think through nonetheless ha
Thanks for helping me think about this. I tried something a little bit similar to the idea you posed with the random letters but ultimately settled on the the solution I described here.
I think random ordering is very useful in some situations but constant recalculation can be a problem. I would be nice if AppSheet could come up with some randomizing functions that would only be invoked when they were triggered. For now, I think Iโll stick with what Iโve described for my flashcard app.