Showing a random value from 3 columns

[Korbib]

Hello there,

I have 3 columns :

• Motto
• Guilty pleasure
• Shortcoming

I would like to display randomly in a Hint component only 1 quote from one of this 3 columns and avoid empty cells.
And every time the user hit the component, it displays an other quote randomly.

Thanx for your help

[Nicolas_Joseph]

Hi @Korbib

Assuming you have a Users table to enable User Profile based computed columns, here’s a functional approach.

In your Quotes table

Expected result

Step by Step

1. For each column containing quotes (i.e. Motto, Guilty pleasure and Shortcoming), create a Joined List of all values:
   

   

2. Store all values from these 3 columns in a single one thanks to a Template column, just like this:
   

   

3. Convert this Template column into an array, using a Split Text:
   

   

In your Users table

Expected result

Step by Step

1. Get the All 3 colmuns in array column from Quotes using a Single Value:
   

   

2. Add a Previous quote column (Text) to store the displayed quote:
   

   

3. Get the list of all quotes available (for the next hit on the button), let’s call it All 3 columns without previous quote. It’s a Remove Element column:
   

   

4. Count the number of available values using a Rollup based on the column we’v just created (Count of values in array without previous quote):
   

   

5. Add a Date & Time column in order to store the exact moment the user hits the button. Our random will be based on that later on (Date and Time for Random):
   

   

6. Create a Random Number column. I named it Random 0→1 to make it different from the next one we’ll add just after that:
   

   

7. With a Math column, we define the index of the quote we’ll display next (Random on Count). The formula is the following: FLOOR(random*count).
   

   

8. Another Math column to help us slicing the array - Random on Count + 1:
   

   

9. Now we can extract the next quote thanks to all previously created columns. It’s New quote, a Slice Array column:
   

   

10. Lastly, we need the data as a text instead of an array to make it useable in a component. New quote in text, a Joined List column will be useful for this purpose:
    

    

In your Layout

Expected result

• The main point here is the Button setup, so here it is:
  
  

When the User will hit the button, it’s going to change the Date & Time for Random column. The value will be used to define a value between 0 and the total number of quotes - 1 (we don’t want to reach the same quote again).
Because we have replace the Previous quote with the one we displayed, you can be confident on the fact it will not pop for this new draw.
And it deals with empty cells, as you can see in the Quotes table: I intendty not filled all cells for this exact reason: as you can see, everything works fine

Is this what you were looking for?

[ThinhDinh]

So in your database, you have a list of quotes for Motto, Guilty pleasure, and Shortcoming, and the full space you’re consider is all those quotes, avoiding empty cells?

[Darren_Murphy]

@Nicolas_Joseph

• Store all values from these 3 columns in a single one thanks to a Template column, just like this:

• Convert this Template column into an array, using a Split Text:

You can combine these two steps into a single step by using a Make Array column.

[Korbib]

Ho wow !
This is more than I expected, thank you @Nicolas_Joseph

@ThinhDinh

This is my actual table :

and the goal :

It doesn’t matter if the same quote is showing several times.

[Korbib]

How can I make this in the same table without the previous displayed part ?

[Nicolas_Joseph]

Does your screenshot is from the Users table @Korbib?

If so, you just have to apply the steps from what is described preivously, moving all firsts steps about Quotes table directly inside the Users one
And of course, win one manipulation using tip provided by @Darren_Murphy!

In the layout, you can also use an action directly in the Hint component. Same logic (action with Set column value)

Where are you stuck, maybe we could help you from there?

[Korbib]

I don’t use the users table, I’m on a free plan.

I don’t want to use the Hint component because I don’t want to have an action button.
I know it’s a bit strange but my goal is to have a hidden feature

[Korbib]

In a previous app, I made it like this

Each component use a random single value for each column.

The problem is when the random hits an empty cell, the component dispear and sometimes there is no component because all 3 single value columns hit an empty cell.

That why I wanted to combine these 3 columns into one without empty cells

[ThinhDinh]

• Use a Make Array column, pointing to the 3 columns. So you have an array of values from 3 columns in each row.
• Use a Lookup column, point to the Make Array column above. Now you have an array of values from all 3 columns, for all rows.
• Add a Single Value column > Random > point to the Lookup column, and use it for your hint component.

[Korbib]

thank you @ThinhDinh

But the make array column can’t be pointed in the lookup column

[ThinhDinh]

Ok so here’s plan B:

• Use a Joined List column, join the Make Array column using a comma as a delimiter (so you don’t have any empty elements here).
• Use another Joined List column, join the Joined List column above so you have a comma-delimited list of everything.
• Use a Split Text column, split the 2nd Joined List column by a comma so you have an array.
• Add a Single Value column > Random > point to the Split Text column, and use it for your hint component.

[Korbib]

Thanx for the reply

That’s strange I can’t see the single value with the split text but if I make a single value with a simple column it works

[ThinhDinh]

I think your random all quotes column is setup incorrectly, as it shows an array in your case.

Here’s an example I just setup.