Randomize sort order every 3 hours

[Kirk_Masden]

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.

[Kirk_Masden]

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.

[Kirk_Masden]

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.

[retailpartnerco]

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.

[Kirk_Masden]

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.

[Work_Goscinski]

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.

[Kirk_Masden]

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!

[QREW_Cam]

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’

[QREW_Cam]

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

[Kirk_Masden]

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.