Generating a random number and then one based on that number

Hi! I’m using RandBetween() to make my app pick a quote randomly from a column. That’s easy enough. Let’s say I have 120 quotes and I want to make a slice that will pick out just one record to display in the detail view that is shown when the app is opened. I number the [Key] column from 1 to 120 and used this make the slice:


So far so good. Actually, though, I would like the slice to result in two records – the English quote and a corresponding Japanese translation. The English should display first, as the default, and my Japanese users should be able to swipe to see the second record with the Japanese.

I have a solution and my question is “Is there a better, simpler way to do this?”

My solution: I put the corresponding Japanese translations into records, in the same column on the same table, that equal the English key + 1000. So, the record with the Japanese translation for quote no. 24 would be 1024. I wanted to use RandBetween to find the number for the English quote and then add 1000 to that to find the Japanese quote. Unfortunately, however, if I use RandBetween twice I’ll get two different numbers. So, I put RandBetween() in virtual column in another table (a very small one – only a couple of records). That happens to be a table called “Stats” and I named the virtual column “Random no for Welcome”. Here’s the expression I’m using now:

or([Key]=number(SELECT(Stats[Random no for Welcome], ([_RowNumber] = 2))),[Key]=number(SELECT(Stats[Random no for Welcome], ([_RowNumber] = 2)))+1000)

It works but, as I wrote above, I’m wondering if there might have been a simpler solution. Thanks for your consideration.

P.S. I used to do this on the spreadsheet side but I would prefer to do this all within AppSheet. Relying on the spreadsheet causes minor problems I’d like to avoid.

Also, the following post also deals with the issue of how to get an app to open with a random quote, but the specific question I’m posing here isn’t addressed:

Welcome Wisdom (Table)

Set (Column)

  • Type: Text
  • Suggested values: SORT(SELECT(Welcome Wisdom[Set], TRUE, TRUE))
  • Required? ON

Language (Column)

  • Type: Enum {“EN”, “JP”}
  • Valid If: ISBLANK(FILTER("Welcom Wisdom", AND(([Set] = [_THISROW].[Set]), ([Language] = [_THISROW].[Language]))) - LIST([_THISROW]))
  • Initial value: INDEX((LIST("EN", "JP") - SELECT(Welcome Wisdom[Language], ([Set] = [_THISROW].[Set]))), 1)
  • Required: ON

Quote (Column)

  • Type: LongText
  • Valid if: ISBLANK(FILTER("Welcome Wisdom", ([Quote] = [_THIS])) - LIST([_THISROW]))
  • Required: ON
  • Label: ON

ID (Column)

  • Type: Text
  • Initial value: UNIQUEID()
  • Required: ON
  • Editable: FALSE
  • Key: ON

Stats (Table)

Welcome Wisdom set (Virtual Column)

  • Type: Text
  • App formula: INDEX(Welcome Wisdom[Set], RANDBETWEEN(1, COUNT(Welcome Wisdom[Set])))

Welcome Wisdom (Slice)

  • Row filter: IN([Set], Stats[Welcome Wisdom set])
1 Like

Wow! I’m amazed by and very grateful for this detailed response. I think it may take me longer to digest it that it did for you to write it but I work on it and try to implement it. Thanks again!

P.S. The solution I already have works well enough but I take it that what you are suggesting will be faster and more efficient. Is that right?

1 Like

Having looked at your solution a bit more, I think you have confirmed for me that it was necessary to put my RandBetween virtual column in a different but originally unrelated table. That was my primary question. Thanks, too, for that confirmation.

Maybe. I thought the problem you had was interesting so thought I’d take a stab at it. :slight_smile:

Yep. You definitely don’t want to be computing that value for every row of a large table. :slight_smile:

1 Like

Thanks again!!

1 Like