Select a random row of a table

[Daniel_Stieber]

I will share some hacks I used in the LIP2-Engine. Some of them are basics, some are advanced, but I will try to explain it as easy as possible or approach it in multiple steps.

LIP2-HACK #1: Select a random row of a table

TL;DR: This post approaches the topic in multiple steps. You can also jump to -> Final solution if you want to skip the blabla.

Final result of example 4

One of the biggest improvements in the LIP2 (besides the controller) is the way how the map is filled with objects.

In the first game, I made a table with 99 rows listing the available objects multiple times and just randomly used one of it on the map using right(random(),2). The problem: There was no way to control, how often an object is really placed on the map. So e.g. there was no guarantee there even is an exit

I wanted to exactly define how often an object should be placed on the map, and thatfor I needed a way to select a row randomly from table, that has not been selected before. Here is how I made it work:

Example 1: Show a random fruit
In our example, we want to get a random fruit of our fruits table.

1. Create a table called Fruits with a column “fruit” and a column “order”
2. Fill in fruit names in your column fruit and fill in the formula =random(false) in your column order
3. On your canvas, create a new formula with =Fruits.sort(false,order).fruit.last()

Screenshot

You now get a random fruit out of your table. That is cool, but is it cool enough? What, if you want another random fruit?

[Daniel_Stieber]

Example 2: Select a random fruit
In our second example, we use a button to select a new random fruit.

To do that, we need a second table that will store our selection.

1. Create a second table called “Selection” with a text column “selected”
2. Create a button and give it a name and label (e.g. selectFruit, “Select random fruit”)
3. Choose “modify rows” and select your “Selection” table
4. Use “all rows” and fill your column “fruit” with =Fruits.sort(false,order).fruit.last()

Your button should work now, but you will notice that you still get only the same fruit when click on it.
To change that:

1. Go to your Fruit table and change the formula in “order” from =random(false)to -> =random()

This time, your order will be updated, everytime something happens on the board (e.g. a button click ;))

To get your selected random fruit on the canvas or in a formula you can create a formula with
=Selection.selected.first()

The canvas

The button

Tipps:

• You can also use “add row” instead of “modify rows” in your button, to create a “history” of selected fruits.

[Daniel_Stieber]

Example 3: Select every fruit randomly and only once
In the third example we wan’t to select only fruits, that have not been selected so far.

There are two ways:
A. Delete the row that you have currently selected
B. Just mark the row as “already selected” to keep your data

I prefer way B and here is how it works:

1. Add a number-column “eaten” to your Fruits table using the default value 0
2. Change the value formula in your selection button to: =Fruits.filter(eaten=0).sort(false, order).fruit.last() to just use “uneaten” fruits.
3. Create a new button and give it a name and label (e.g. eatFruit, “Eat selected fruit”)
4. Choose “modify rows” and select the Fruits table.
5. Choose “Custom filter” and use the filter**: fruit=Fruits.filter(eaten=0).sort(false, order).fruit.last()
6. Choose your column “eaten” and set the value to 1
7. Create a new button (e.g. selectAndEatRandomFruit, “Select and eat random fruit”)
8. Choose “push buttons” and select our two buttons “selectFruit” and “eatFruit”

Now, every time you click the “Select and eat random fruit” button, the first button will bring the current random fruit into our selection and the selcond button will make it unavailable for the next selection.

The whole section

The select button

The eat button

The select and eat button

Tipps:

• With the current way, every fruit name can just appear once and has to be unique. You can easily change this by adding a column “Row properties -> row id” and select rows not by the fruitname, but by the id in the formulas.

• Make a “reset” button, so you can fill your fruits stack with one click. Just use “modify rows” - “all rows” - “eaten = 1”

**If you wonder, why we did not use “fruit=Selection.select.first()” in the “eat” button:
When you push multiple buttons with one button you can imagine it like this: Everything freezes until all buttons are processed.
That means:

• Our fruit order stays the same until all buttons are pushed (random will not change) so we can sort by order again and get the same result for both buttons.
• When the eat button wants to eat a fruit, there will be no or wrong data in our selection table and not the current selected fruit. So we can not reference to it.

[Daniel_Stieber]

Example 4: The final solution: 1 Table 1 Button
This is not how I used it in LIP2, because with the game board and the object list, I needed two tables anyway, but in many cases you only need one like here. The principle is quit the same as in the examples before, but here we use a timestamp to mark eaten rows and to create an order and see which is the current one.

I created it with vegetables this time:

1. Create a table vegetables with the columns “id” (row properties -> row id), “name” (text), “order” (=random()) and “selected” (date/time)
2. create a button with “modify rows”, select the vegetables table and the custom filter id=Vegetables.filter(selected="").sort(false,order).id.last() (this time we select by id, to make it possible to have a vegetable more than once)
3. use the values: “selected = now()”
4. you can also add a disabled if: Vegetables.filter(selected="").count()=0
5. create a formula on your canvas =Vegetables.filter(selected!="").Sort(true,selected).name.last()

[Joseph_B]

These are great. I can see a lot of potential for some of these tips and tricks. Thanks for sharing!

[tomavatars]

Hey there @Daniel_Stieber and thanks for the tips.

I struggled with the last part, I didn’t understood why using Filter.(Selected!=1) and it didn’t worked on my side. I replaced it with : [Art of Game Design Lenses].Filter(Selected.IsNotBlank()).Sort(true,Selected).Subcategory.Last()
I also removed Filter(selected="") in the button filter, to be able to continuously pick any 113 lenses without excluding already picked lenses.

And finally made my random Game Design Lens picker!!! Thanks again!

By the way @evan or anyone at Coda, in my gif, I’m in presentation mode, and I find it a bit weird that the canvas content is not centered on the whole screen. Thanks!

[Daniel_Stieber]

This is awesome @tomavatars!

Is there a chance you share this doc with me? I’m super curious about all the lenses and what I can learn from this.

Thanks for mentioning the bug in the code. You are right, that != 1 is totally wrong.
Replaced it with the one I used in the screencast.

Also a question to you and to @Joseph_B:

• Is the way I made this post/tutorial to long/complicated?
• Do you like the step by step approach or would you prefer just a final result with 2-3 formuals and the shared doc (e.g. just example 4 of this post, without the rest)?

[tomavatars]

I just copied the doc and I can share it with you if you want (lenses are taken from Jesse Schell book Art of Game Design, links in the doc) : https://coda.io/d/The-Art-Of-Game-Design_dW83SIx73Lk/Random-Lens_su71m#_lu3Jd

I think I need your mail ?

The tutorial was good, but I often missed some of the formula logic. Like I had to scratch my head a bit to finally find that Sort(true) correspond to ascending. But it’s also Codas’ fault, because the formula sheet is not very clear, especially for noobs like me!

[Daniel_Stieber]

Thanks for feedback. Yeah the formula field with complex formulas is a mess. I copy and paste back and forth with my text editor.

Very nice, I‘ve sent you a request and ordered the book

[Joseph_B]

@Daniel_Stieber

@Daniel_Stieber

Is the way I made this post/tutorial to long/complicated?

Not a problem for me. Sure, it looks a little longer, but if readers take 2 seconds to appreciate the value and navigate to where they need to go, then awesome. Suppose down the road this could be translated to another format (e.g. a blog).

@Daniel_Stieber

Do you like the step by step approach or would you prefer just a final result with 2-3 formuals and the shared doc (e.g. just example 4 of this post, without the rest)?

I liked the step by step method that you did. It allows people to jump in at different points, more easily digest the information, and arguably can be multiple solutions in one (i.e. people with different questions may find this tip helpful since it is broken down in steps). Also, if you only need “step 1”, you may find yourself now interested in diving deeper once step one works.

[Anavi_Dhawan]

Hi @Daniel_Stieber thanks for sharing this. This is very useful for me. I am trying to create a randomised meal planner and was looking for a platform to help me do this easily. Thanks for posting these with such a good explanation!

[Margaret_Robertson]

Does RandomItem() supersede some of the advice here? I always find this thread when I google, but it seems like RandomItem lets me do everything I want to do (so far!) in terms of pulling random elements out of tables.

Wondering if the advice here is from before it was added…

[Daniel_Stieber]

Hey @Margaret_Robertson ,
you are absolutely right, randomItem() was introduced in March last year, so this hack and also some answers in the threat are outdated.

For anyone coming from Google or elsewhere, here is the post when randomItem along with two other handy formulas was announced:

Launched: 3 new formulas to help you get random and/or reversed

In the immortal words of Missy Elliott, sometimes you just need to “Put my thing down, flip it, and reverse it.” We’ve heard that’s been true for your Coda docs, and while some of you have already discovered these, we’re excited to confirm 3 new formulas with you today! [ReverseList.png|1000x562.5] ReverseList() Can you guess what this one does by its name? That’s rightーnow you can automatically structure your outputs as a list in the reverse order. Previously, it took some complex formulas …

[Margaret_Robertson]

That makes sense - thanks for clarifying. It’s a great formula - I’m working on a massive modular text generator project and am moving the whole thing across from a really gnarly spreadsheet. Massively easier in Coda!