Unable to reference a list

1

Can I somehow use a Select List as a value for new rows without creating a new table for it?
I thought it would be possible since I’m not creating new options with this formula, it’s just for new row, therefore it’s not circular in my mind. It doesn’t actually change the value of the Status list. Or does it?

image
image1309×890 73.4 KB

1 Like
2

Hi Fran,

Interesting example!

It looks as if Coda has a very broad view of circular reference. Yes, the formula does not use the content of the cell in the column in the calculation, but the formula does refer to the current column.

It seems that any reference to the current column will trigger the error message. But using the meaning rather than the syntax of the formula to determine whether a formula is circular is a WHOLE lot more difficult, if even possible.

Regards
Rambling Pete

2 Likes
3

So the simplest solution for this problem would probably be an extra option, just like the “Allow multiple selections” toggle option, that says something like: “Use random item from this list”

OR

The Coda Program could run the formula in the background while you are typing it, and check whether or not the formula changes the actual value of the formula.

  • It does change the value = Circular Reference
  • It does NOT change the value = NOT a Circular Reference

I know very little about coding, so I don’t know if that’s a viable option… but hopefully you get the idea.

1 Like
4

It would work if you re-use the list of selectable options in your value for new rows formula instead of trying to reference directly the column :blush: (which indeed lead to circular reference)

List(
     "Not Started", 
     "On Hold", 
     "In Progress",  
     "Completed"
   ).RandomItem(false)

This means though that if you wish to add a new status in that field, you would need to modify selectable options and the formula …

3 Likes
5

regarding circular references.

they are not allowed because the coda engine automatically recalculates any formulas impacted by any changrs you make to any of your data.

to do that it must know WHICH formulas are dependent (directly and indirectly) on the changed datum and in which ORDER to do the computations to correctly propogate the change.

it does that using a complex data structure called the “dependency graph” which it maintains under the hood. it keeps track of which columns, controls and canvas formulas are referenced by each formula

a circular reference would cause an infinite loop in the dependency graph, so its not allowed.

in spreadsheets, the definition of a circular reference is narrow, the single cell. that is because each cell has its own formula. it does not concern the engine wheather or not its the same (relatively) as other cells’s formulas.

but in coda, each COLUMN has its own formuls and ALL the cells in that column share it. so the definition of a circular reference seems much broader in coda than you may be used to from spreadsheets.

excel can “bypass” the circular reference warning by nefarious means, but coda does not.

note that initial value formulas for new rows are treated seperately so i have been able to exploit that to do once-of computations upon row insertion that would otherwise fall afoul of the circular reference rule.

max

3 Likes
6

Nice! That’s it, thank you!

I must be honest though… Since I first stumbled onto this obstacle, I’ve already created a new table and hid it on a different page and forgot about it. :person_shrugging:t2:

But I might remember to use this in the future :+1:t2:

1 Like
7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.