Creating a list *until* a condition is met

[utkucha]

So I am working on a doc that helps me visualise and setup my level design for a game I am working on.
I have different tables and I can emulate if my settings are ok with the basic game economy I am creating.
I have many columns that are automatic but a couple of them are a bit tricky - at least for me right now - to figure out and I am doing them manually.

The part I am struggling is this:
I have a current coins column that tells me how much coins the player has after having played this much levels. What I’l like to achieve is to automatically spend all this money until I can’t buy any more item (of course each item on the item table has a price). An item can be bought once so after a new level has been completed and the player earned some coins, they should not buy the same items again but new ones, until they reach have bought everything.

In the end I’d like to see how fast a player can buy everything in the game.

Do you have any clue on how to achieve that?
Don’t see anything that could work with Filter() or FormulaMap()…

[Irfan_Khan]

Just spitballing ideas here.

I’m taking this as you have 100 coins and you want to buy the max items for an item that costs 9 coins. You could have a “max possible” column that is floor((current coins)/price,1). That tell you the max number of that item that a user can purchase.

In this example (current coins/price) = 100/9 = 11.111. Floor (aka rounddown) would yield 11.

Edit: ok I see you want max 1 purchase per item. I guess I’m not seeing the true calculations you want to see? Transaction logic would just be If(current coins>item price,allow purchase,don’t allow)

[utkucha]

just to make it more clear maybe here is a screenshot of a part of this big table (also connected to all the other tables describing the game entities)
each line is the player playing one level. You can see the amount of coins they earn during the level. The current coins column is increasing until the player buys one new zone (this is done manually for now)
On the right there are the items from the shop the player can buy because they are unlocked (based on XP). This list has new items added each time they are unlocked. Oh, and of course each item has a different price
What I wanted is to pick items from this column and display them in a new column so I can simulate the player trying to buy everything with the coins still in their pocket.
In the example here the player has actually enough to buy 4 or 5 of these items, so the other one would be bought on the next level…

I was thinking some kind of formula map that gets each item and add in a list and breaks (Is there a way to break a loop as in a for each statement?) whenever the sum of the prices would be higher than the current coins

[Agile_Dynamics]

the simplest UNTIL solution is to use a FormulaMap() and have an SwitchId() inside that loop that will execute the required code if the UNTIL condition is false, and will do NOTHING otherwise.

so if i have a table, T, with prices column, P, and i want to do some action, XXX, until the sum of prices exceeds N then i could write

T.P.Formulamap(
    SwitchIf( SUM<N,
        RunActions(
            XXXX,
            SUM.SetControlValue(SUM+CurrentValue)
        )
)

This will loop over ALL the rows in T
But will only sum the price and do the XXXX action while SUM<N

There are more complex ways for when you dont know how many iterations there will be.
see these for two different approaches

WHILE loop with a live formula

My clients use WHILE loops in their Visual Basic and Python logic and its not easy to migrate this into purely FormulaMap() loops, especially when the WHILE CONDITION is complex. So I had to find a way. And I recalled an ancient SPIN-LOCK technique we used in UNIX back in the 80s

WHILE loop implementation

Coda is now officially Turing-complete Here’s an implementation of a while loop that runs as long as the value in the table is not equal to 0. It can be easily modified for any boolean condition by changing the formula in While condition. The hack used here: a button cannot push itself, but it can push another button, which will then push the first button.

max

[utkucha]

Thank you for your reply man!

So I have spent a couple of hours trying to figure out how to do but I am still stuck.
I tried to do with a formula in a column because it will be the most immediate and I’d like the table to react when I change some values like player skill and see how this affect the number of coins they will earn after each level and what they will be able to do with them (i.e. buy items until they run out of money)
In this scenario, well, the RunActions formula is not supported (or is it and I am doing it wrong?)
but I tried to play with formulaMap and switchIf with no luck.
How can I have the sum, within the formula map, of the elements

      [available items]
        .FormulaMap(
          SwitchIf(
           thisRow.[bought items].price.Sum() < [current coins],
            
              ListCombine(
                [Player Progress]
                  .Filter(
                    count =
                      thisRow.count - 1
                  )
                  .[bought items],
                CurrentValue
              )
          )
        )
    
  

well, I have a circular reference, and this is not working
the main issue for me here is to know while looping, what is the sum of the prices of the elements I am already gone through (or added to the list I am creating…)

the SUM property you mention, I can’t figure out how to create it in this formula

I am also tried the button option but the code is not executing and I don’t have any clear feedback telling me why

[Player Progress]
  .FormulaMap(
    WithName(
      CurrentValue, thisRow,
      [available items]
        .FormulaMap(
          SwitchIf(thisRow.[bought items].price.Sum() < [current coins],
            thisRow.[bought items] .ListCombine(
                [Player Progress]
                  .Filter(count =thisRow.count - 1).[bought items],
                CurrentValue
              )
          )
        )
    )
  )

If I can avoid the button option I’d avoid it though.

[Agile_Dynamics]

ok @utkucha, challenge accepted !

you want a column formula, not a button.
tricky but i will see if it can be done.

you are correct, RunActions and SetControlValue are not available in a column formula. that’s because column formulas must be pure functional expressions; ie. no side effects, like setting variables or executing actions.

to use SetControlValue to accumulate a sum, you need to create a /text or /number control widget in your document. and i forgot to mention that above - sorry.

this is a similar problem to one i solved for a client. they had a list of sorted backlog tasks and wanted to select some of them for the next sprint such that the sum of days-effort is less than a given budget.

i will dig that up and modify the model to match your use-case above

max

[Agile_Dynamics]

So here is the old SCRUM PLANNING document.
You enter the Costs and Benefits of the tasks.
You define the total BUDGET available.
And the column formula goes through the Tasks ranked by best ROI UNTIL the budget is spent.
It marks those tasks as INCLUDED in the sprint.

Max

[Agile_Dynamics]

just to respond to the code snippets you have shown:

CIRCULAR REFERENCE
you get a circular reference in SHEETS if your cell has a formula that refers to itself, or it refers to a chain of formulas that refer to itself. so you can easily have a cell formula that uses the value of the cell ABOVE - a common technique to accumulate values or count rows etc - and NOT get a circular reference.

but in CODA, formulas are tied to the entire column and not the individual cells it contains.
so a column-formula that refers to the same column (even if it really refers to the cell above) is seen as a circular reference and not allowed. so we have to find other ways to achieve what we want.

BUTTON CODE NOT EXECUTING
the one syntax error i see straight off is this

     WithName( CurrentValue, thisRow, ... )

using WithName() you are assigning a NAME to the value of an EXPRESSION.
but here you are attempting to assign the name thisRow to the expression CurrentValue.
and since thisRow is a reserved word you cannot assign to it like that.
not sure why you did not get a syntax error - but thats where i think the problem lies.

it is easy to confuse the use of CurrentValue (inside Filter() and FormulaMap()) and the use of thisRow (inside any column-formula) - but they are quite different - they just seem similar.

otherwise the logic might have worked - and you are progressing well with your Coda formula-wrangling.

my own example with the SCRUM Planning, is doing something similar to what you are looking to do.

there is a BUDGET and a list of ITEMS that can be ‘bought’ for that amount, sorted by the ROI (ratio of benefit/cost) - and the table selects the ones that can be got for that amount.

the key trick is using the Accumulated Cost column to compute the ‘total spend’ as you go through the list in descending ROI order. And then to flip the checkbox depending on whether this is above or below the budget.

(i should point out that using Filter() and Sum() in this way is computationally expensive O(n^2),
for every row; it scans all the other rows to find the Filter() matches and then scans all of those to Sum() the cost. so not scalable to thousands of rows, but perfectly ok for a few dozen rows)

although the table is sorted by descending ROI, that does not impact the execution of the formulas. so you could sort the table by some other criteria - and it will still work.

so, i think this example will work for your use-case with a little adjustment. hope it helps.

let us know how it goes.

respect
max

[utkucha]

Hi Max,
Thank you for the explanations.
By the way I actually have a row number, it is the count column (it’s the number of level played here)

Here we can see that at level 5, I have unlocked the shop and have access to the items available for player LVL >= 2. This is more or less the formula for [available items]

In bought items I have this formula

thisRow.[available items]
  .FormulaMap(
    SwitchIf(thisRow.[spent coins] < thisRow.[current coins], CurrentValue)
  )

I guess the spent coins column is update after the loop is done so the switchIf condition is always valid.

So I thought maybe I could use what you are doing with the SCRUM Sprint plan and I have made a second table with the player inventory that would be populated with the formula in each level by reporting the count value, and the item bought and compute the cumulated price of bought items at this level. I had high hopes but realized you can’t use AddRow in column formulas

Another thing I tried was to store in a Withname formula the current list and try to get the cumulated price of the items in this list but I was stucked there because the list is a list so I have access to the things you can do with a list, but no access to my item.price data…

Don’t know now, maybe the button route is something to consider for this use case , and I am afraid I can’t do it in a column formula!

[Agile_Dynamics]

perhaps you are doing too many things in one table.

you may need a seperate table for inventory and another for available items.

also, you will probably need to use a button at some point to automate the creation of the next row in the game, and that button can also save a set of values in tables without these problems caused by column formulas having to be pure functions with no side-effects.

the reason column-formulas are restricted in this way is both philosophical and very practical.

such pure functions, devoid of side-effects, are the real reason spreadsheets have been so successful. non technical users can easly understand the models they build with such formulas. and the lack of side-effects makes it almost impossible to create unexpected bugs and strange behaviors. (the philosophical reason).

on top of that, the system can easily keep track of the web of dependencies amonf formulas. working out which formulas need to be re-run whenever a value is changed. and in which order to recalculate everything to keep the model consistent.

in fact, it also becomes easy to recalculate many formulas at the same time, using multiple threads, since they cannot change other values. (the practical reason).

but there comes a point when we need to set several values and to add rows or delete rows etc.

in spreadsheets, we do this with MACROs. written in a different language and usually needing extra skills and knowledge, often introducing bugs and unexpected behaviours.

in Coda we can do this by using buttons and automations with a FEW extra functions that do have side-effects.

so, trying to develop a complex game with only column-formulas is very tricky indeed. you will need buttons and/or automations at some point.

just my humble opinion
max

[utkucha]

OK I get that.
So, I am not trying to develop a game here but a simulation of the player behavior in the actual game with the number of coins they can get in each level (depending on their skill that I set in a slider) and then how they will use the coins in the shop to see how fast they can buy everything

But I can try to run that simulation on a button indeed. I’ll try to share the result here.
I believe it can be a really good too to quickly iterate over the different parameters of the game.
It already is, but I am doing some of the actions manually (i.e. unlocking stuff, buying items in shop…)
I am more into the challenge of making it all automatic

[utkucha]

Hi,
I am trying to use a table for that.
I think it might work but I am struggling with the button that would create the rows until I have spent enough at this level

Here is the code for the button:

[Player Progress]
  .FormulaMap(
    WithName(
      CurrentValue,
      currentRow,
      [available items]
        .FormulaMap(
          SwitchIf(
            [Item Inventory]
              .Filter( [game count] = currentRow).Last().[cumulated price] <
              currentRow.count,
            [Item Inventory]
              .AddRow(
                [Item Inventory].item,
                CurrentValue,
                [Item Inventory].[game count],
                currentRow.count
              )
          )
        )
    )
  )

However I keep getting the “unable to execute invalid actions” error.

I even tried with a simple formula such as

[Player Progress].Filter(count= 1).test.Concatenate(“hello”)

and same error

any idea why it is not working?