just to respond to the code snippets you have shown:
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, ... )
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
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.