Randomly reorder the elements of a list

[ranorga]

Dear sirs:

I am trying to find is a function to randomly reorder the elements of a list and that when set to an Initial Value it is executed only once. I had tried it with randbetween but it is executed every time I selected other field despite is in Initial Value. Could you help me?

Regards

[Suvrutt_Gurjar]

If you could share your current configuration and expression and details about where you are using it , the community could suggest a possible solution.

For example,  by initial value, do you mean value in the " initial value" of a column's setting?

[ranorga]

Hi @Suvrutt_Gurjar  this is the expression:

ORDERBY(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema]),RANDBETWEEN(1,10))

Yes, "Initial Value" of a column's setting

[Suvrutt_Gurjar]

Thank you, is it a physical (real) or virtual column? Also, as requested, a little surrounding context would help.

[ranorga]

Physical (real) column 

[Suvrutt_Gurjar]

If it is in initial value of a physical column, then it should not change once the value is set during initial creation of the record, unless you have reset on edit set on the column. Please elaborate.

Define app formulas and initial values - AppSheet Help (google.com)

[ranorga]

The following link shows the problem:https://drive.google.com/file/d/1NJHA10xSypHV528KmPbelcDonE43AVHa/view?usp=sharing

This is the formula in Orden Field:

ORDERBY(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema]),RANDBETWEEN(1,COUNT(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema]))))

The field Orden initially did not change but when I placed the following expression in the Puntaje1 field the problem started:

ANY(SELECT(AlternativasPorPregunta[Puntos],AND(IdPregunta=INDEX([Orden],1),[Alternativa]=[_THISROW].[Pregunta1])))

Any idea how to fix?

[Suvrutt_Gurjar]

Thank you for more updates. Is it correct understanding that you have this expression 

ANY(SELECT(AlternativasPorPregunta[Puntos],AND(IdPregunta=INDEX([Orden],1),[Alternativa]=[_THISROW].[Pregunta1]))) 

 in Puntaje1 field but not in the next field rhat you are saying in the video that it is not changing?

[ranorga]

@Suvrutt_Gurjar , can you give me your email to give you access to the app? And you can see the problem better

[Suvrutt_Gurjar]

Hi @ranorga ,

Let us try to see if the problem can be solved in the community discussion itself.

May I request you to try the following

1. Please add a physical column called say [First_Orden] with an initial value expression something like 

INDEX([Orden] , 1)

Then in the field [Puntaje1] , you could try an expression of 

ANY(SELECT(AlternativasPorPregunta[Puntos],AND([IdPregunta]=[First_Orden],[Alternativa]=[_THISROW].[Pregunta1])))

It sounds that you have forgotten the brackets around the field "IdPregunta" in your shared expression?

[ranorga]

Hi @Suvrutt_Gurjar , I tried your suggestion but the issue continues. Also I put brackets in the field "IdPregunta", thanks!

Maybe the solution is use other function to randomly reorder the elements of a list, instead of RANDBETWEEN. Thanks in advance.

[Suvrutt_Gurjar]

Okay, thank you for the update. Yes, you are right. I believe RANDBETWEEN() will recalculate for every occurrence of the expression. We need a "stable" random number for your requirement. By stable, I mean one that will not change once calculated. 

I will think over any possible solution and revert back, if I get one. The other community colleagues may also have some great solutions. 

[Suvrutt_Gurjar]

By the way, may I know what is the key of the table where you are executing the expression. It the key composed of say UNIQUEID()?

[ranorga]

Yes, the key has a expression UNIQUEID(). Thanks!

[Suvrutt_Gurjar]

I will try the following

1. Instead of UNIQUEID(), I will try RANDBETWEEN() for a key of the table. So the key column's initial value expression can be something like RANDBETWEEN(1111111111,9999999999)

2.Then the expression for the [Orden] column can be something like below

ORDERBY(

               SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema]),   

                               COUNT(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema])) -                                                NUMBER(LEFT([Table_Key],1))  

                    )

The part  NUMBER(LEFT([Table_Key],1)) may need adjustment based on how large count the expression   COUNT(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema])) can produce.

Here basically instead of RANDBETWEEN() in expression in column, we use RANDBETWEEN() for computing key column of the table. The key column does not change , so I believe it will give us a "stable" random number.

The we use the leftmost digit of the key by using the expression NUMBER(LEFT([Table_Key],1)) . We use this subexpression in the [Orden] then to compute a random number.

Point to note: The key computed with RANDBETWEEN(1111111111, 9999999999) can have possible 8888888888 variations. So, if the table will contain 10 K rows, there will be a probability of 1 in 88 K for the key to repeat. You could improve the probability by using still higher range for RANDBETWEEN() for computing key.

I have not fully tested the above approach, but I believe it will work. 

[Suvrutt_Gurjar]

You could also have the table key something like

CONCATENATE(RANDBETWEEN(10, 99),"-", UNIQUEID()) 

It will produce a key something like  78-A1C34EA9.

You could use the leftmost 2 digits in your expression, something like NUMBER(LEFT([Key_Column], 2))

Hope this helps. You will need to adjust the RANDBETWEEN() range based on your requirement.

[ranorga]

I am sorry @Suvrutt_Gurjar , but I don't understand how it can help me. Could you explain more?

[Suvrutt_Gurjar]

---

@ranorga wrote:

I am sorry @Suvrutt_Gurjar , but I don't understand how it can help me. Could you explain more?

---

HI @ranorga , my response to your above question is as below

---

@Suvrutt_Gurjar wrote:

Here basically instead of RANDBETWEEN() in expression in column, we use RANDBETWEEN() for computing key column of the table. The key column does not change , so I believe it will give us a "stable" random number.

---

We basically aim to use the key column property that key value does not change once created. The key column is based on random numbers. We then use part of it in your expression. We need to use " part" of key because the entire key is very large.

Hope this explains.

[ranorga]

Thank you very much @Suvrutt_Gurjar  for you help! I really appreciate.

[Steve · Accepted solution]

---

ORDERBY(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema]),RANDBETWEEN(1,COUNT(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema]))))

---

Can be more efficiently written as:

ORDERBY(SELECT(PreguntasPorTema[IdPregunta],[IdTema]=[_THISROW].[IdTema]),RANDBETWEEN(1,9999))

Try changing this:

ANY(SELECT(AlternativasPorPregunta[Puntos],AND(IdPregunta=INDEX([Orden],1),[Alternativa]=[_THISROW].[Pregunta1])))

---

To this:

ANY(SELECT(AlternativasPorPregunta[Puntos],AND(IdPregunta=INDEX([_THISROW].[Orden],1),[Alternativa]=[_THISROW].[Pregunta1])))

Or this:

ANY(SELECT(AlternativasPorPregunta[Puntos],AND(IdPregunta=INDEX([_THISROW_BEFORE].[Orden],1),[Alternativa]=[_THISROW].[Pregunta1])))

I agree that this appears to be a bug.

[ranorga]

Thank you very much @Steve! Your solution works! Finally, I had to create some fields:  Orden1, Orden2, etc. with this expression: INDEX([_THISROW_BEFORE].[Orden],1)

and the expression in Puntaje1 is: ANY(SELECT(AlternativasPorPregunta[Puntos],AND(IdPregunta=[Orden1],[Alternativa]=[_THISROW].[Pregunta1]))).

Regards.

[Steve]

I was unable to reproduce the behavior you're getting. Please take a look at my sample app.

[ranorga]

Thanks, @Steve. But If you add scores to each alternative, as happens in an exam, the problem will appear and the expression with RANDBETWEEN will be recalculated. Try it, please.