Generating a random number and then one based on that number

[Kirk_Masden]

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:

[Key]=RANDBETWEEN(1,120)

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:

Dear all, If I want a random "Quote" or "Wor... Questions

Dear all, If I want a random “Quote” or “Words of Wisdom” for example, I will use randbetween in say column X and sort by column X . This works for excel. But does not not work in google sheet and in Virtual Column formula. Am I correct? I will be on leave soon. Just send this as an info.

[Steve]

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])

[Kirk_Masden]

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?

[Kirk_Masden]

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.

[Steve]

Kirk_Masden:

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?

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

Kirk_Masden:

I think you have confirmed for me that it was necessary to put my RandBetween virtual column in a different but originally unrelated table.

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

[Kirk_Masden]

Thanks again!!

[Taufan]

Thank you Steve Coile
Thank you Kirk_Masden

Thank you for making my life easier in random sort things.

[Marc_Engel]

Hi, I am working on an app to be used in a heritage situation, with lots of items from a deceased person’s household. To avoid strife and conflict, sometimes the solution of using a lottery would be better.
So, I am looking for a way to generate a random number and then fix it (make it unchangeable or
Read only). Would this RANDBETWEEN expression be the way to reach that goal?
Each of the heirs would have to be able to view an item, press a button (only one time) to generate this number and receive the outcome on screen (maybe I could send a summary of choices made by e-mail after a certain time??? I think that would be a workflow, but I am not sure)

[Marc_Dillon]

Marc_Engel:

So, I am looking for a way to generate a random number and then fix it (make it unchangeable or
Read only). Would this RANDBETWEEN expression be the way to reach that goal?

Yep. Use it in Initial Value, and set Editable_if = FALSE.

help.appsheet.com

App Formulas and Initial Values

How you can set app formulas and initial values to dynamically assign values.

help.appsheet.com

Editable_If Column Constraint

Conditionally allow the user to edit a column value.

[Marc_Engel]

I have asked this question several months ago and have not followed it up, sorry. But now there is need, again, to make this happen. I hope this explanation will help to clarify.
Tha app is being used in an inheritance case. There are 3 heirs in diiferent parts of the world that have to log in and take part in a lottery about items.
The solution I am thinking of:

• 1. I would need to catch the useremail in the same process before I ensure that the random number is processed and made read only immediately after.
     Can I define 3 personalised columns, one for each heir for the 3 random numbers to be placed in ?
• 2. Users should be allowed to view the Columns in which the ‘lottery’ events take place? (Highest number ‘wins’ the item)
• 3. I would like it best if I could make ONE action to be chosen by / clicked on the user and then the useremail would be used to decide if the column is changed to read only or not.
     Each item therefor has to have 3 columns separated, I think. The heirs will be more ready for a suspension of disbelief when the resulting numbers are kept visible.
• 4. If you can think of a quicker or more easy way to get this done, please tell me.

[Marc_Dillon]

Marc_Engel:

I would need to catch the useremail in the same process before I ensure that the random number is processed and made read only immediately after.
Can I define 3 personalised columns, one for each heir for the 3 random numbers to be placed in ?

I would suggest adding a child Table. Probably with 3 columns: a Ref to the inheritance item in question, the random number value, and a column to hold the value for each specific person (however you wish to set that up, whether just USEREMAIL(), a Ref to a person Table, a simple name selection, etc…).

[Marc_Engel]

I managed to set up a child Table, just like you said, but I ran into a Problem:
I defined a couple of behaviors:

1. Set the value of one column in the table with the Items
2. Copy this value to the Child Table together with the UNIQUEID for that item.
   This table then randomly creates a lottery number, writes the datetime, the useremail etc. That’s all going rather well.
3. The next behavior is to clear the value in the first column, in order for the next heir to give the lottery a try.
   One person, one vote should be the rule. …
   But somehow the Editable_if for the LOT Number that was set to ‘FALSE’ does not seem to work. i.e. I am able to vote again and again and try to raise the number until satisfied. I must be thinking wrong, but I don’t know how to correct this obvious mistake.
   Perhaps by manipulating the behaviour ‘Only if this condition is true’ whereby the ceriterium would be that the user has voted or not…??? But that would mean that one table has to be looking at the UNIQUEID and the useremail in another table/???
   Or maybe nr 3 of my behaviors is unnecessary?? Would appsheet ‘listen’ to the useremail first, before it decides ti act on Action number 1??? In that case it should not matter that there is a same value and the user will only get ONE vote???

[Marc_Dillon]

I can’t understand how you have it set up, please provide screenshots.

---

---

Marc_Engel:

Set the value of one column in the table with the Items

Not sure why you’re doing this? Why would you be changing any value in the item record?

Marc_Engel:

Copy this value to the Child Table

Copy what value?

Marc_Engel:

to the Child Table together with the UNIQUEID for that item.
This table then randomly creates a lottery number, writes the datetime, the useremail etc. That’s all going rather well.

Yes, that sounds good.

Marc_Engel:

The next behavior is to clear the value in the first column, in order for the next heir to give the lottery a try.

What value are you clearing, and why are you clearing it? And how does this clearing allow the next heir to do the lottery? I think they should all be able to do the lottery without anything having to be cleared.

Marc_Engel:

But somehow the Editable_if for the LOT Number that was set to ‘FALSE’ does not seem to work. i.e. I am able to vote again and again and try to raise the number until satisfied. I must be thinking wrong, but I don’t know how to correct this obvious mistake.

Yah, not sure what you’re describing here exactly. I imagine you would need to prevent duplicates of the child records, per Item, one per person.
https://community.appsheet.com/search?q=prevent%20duplicates

Marc_Engel:

Perhaps by manipulating the behaviour ‘Only if this condition is true’ whereby the ceriterium would be that the user has voted or not…???

See duplicate preventing above, probably matches what you’re talking about here.

Marc_Engel:

But that would mean that one table has to be looking at the UNIQUEID and the useremail in another table/???
Or maybe nr 3 of my behaviors is unnecessary?? Would appsheet ‘listen’ to the useremail first, before it decides ti act on Action number 1??? In that case it should not matter that there is a same value and the user will only get ONE vote???

I don’t follow any of that.

[Marc_Engel]

Thanks for your quick response.
I will try to explain if I can
The change to the Item record is a column I added, saying ‘I Want it’ (IKWILHET in Dutch). I thought that was needed.
Screenshot

The next step is to copy this value
Screenshot 2 has to come in a second reply, and next one in a third, because the Interface tells me:“Sorry, new users can only put one embedded media item in a post.”

Because the UNIQUEID is provided now, the other columns take from this referenced column the values I want, for instance the name of the item and its number (the Table will be more ‘readable’ in spreadsheet for me)
I originally thought that clearing to orginal value was needed, so I made the action for it

But I think, judging from your comment: “I think they should all be able to do the lottery without anything having to be cleared.” that emptying is not necessary.
About preventing duplicates, what I see is that only ONE record is created for the one user. Only datetime, ‘vote’ and Lot number’ (LOTNR) are updates, overwritten, so that Ia cannot see which was the first time and number. That way any one of the heirs can kewep trying to reach higher number.
What I thought was, that making the column LOTNR Valid_IF FALSE a second time would be impossible, but in fact it is not.
The last sentences, the ones you are not following, are about the same question, really, namely HOW do I prevent an heir to vote twice and HOW do I enable a different one to cast his own vote (in a different row, of course)

[Marc_Engel]

second screenshot

[Marc_Engel]

Third one, after 8 seconds (“You’re replying a bit too quickly. Please wait a few seconds before trying again.”)

[Marc_Dillon]

Oh I see where my misunderstanding was coming from. You want to make sure to record a response from each person for each item, even if they don’t want an item. That “response” was the “value” you were talking about. Makes sense now, good call.

---

---

So, both of your thumbs-up and thumbs-down Actions should definitely have a condition on them, something like

ISBLANK( SELECT(
  [Related...][any-column-in-child-table] ,
  [person] = USEREMAIL()
) )

This should hide both Actions if there is already a response from a person for that item.

---

---

Also, you shouldn’t be recording the per-person response value in the Item record. Just have each of the Actions (accept/reject) do an “add record to other table”, but with a different values.

[Marc_Engel]

Thanks. This makes sense. I will go to work on it tomorrow!! Hope I get it right, otherwise I may be in touch again.

[Marc_Engel]

I tried it out. Made an action from within the Allitems table that will ‘add a record to other table’, being the related LOTINGs table. It seems to work in so far as placing the item number and name and adding a random number (I chose between 1 and 1000)
But the condition does not work. I presume that’s my own fault. I worked with your suggestion and although the formula seems correct, the result is not what I had hoped for. It is still possible for a user to vote multiple times, because the Action button Thumbs down does not dissappear
Question is: What did I do wrong??

[Marc_Dillon]

[Marc_Engel]

I don’t understand. These brackets around [User ] are meant for columns, aren’t they???. There is no column with that name. I thought this part of the expression was for ‘calling’ the user and useremail that was logged in.
It should tell the app something like: 'Listen who is logged in, see if there is NO row for this item in the Table called LOTING and only then show the action.’

Or something similar… forgive me for my inexperience in writing these codes, I hope you understand what I am trying to say

The Thumbs down (also thumbs up, as soon as I have the correct expression) should NOT appear and instead someone should see the related table below with name and lottery number.

[Marc_Engel]

Solved it!!! Sorry, an old man like me needs time to think. Of course by ‘person’ you meant a field inside the LOTING table. Only problem was, I did not call it Person, but USEREMAIL. Immediately after saving this new condition to my app, the Thumbs Up and Down dissappeared in case there had been a vote.
Thanks very much for the help!

[Marc_Engel]

Just one more question stayed on: does this only permit ONE row to be added?? I did some experiments, turning the user email into the one owned by a different heir, but when I ‘voted’ later on with my own email the original record . Of course, that’s no good.
But … also solved!!
It seems I caused this problem by making the wrong column a ‘key column’ (and keys must be unique), so I guess I overcame this one, as well. Change that and the problem goes away. So, thanks again.