I tried a new thing but couldn’t make it work, so I made a Coda Doc for help if someone has time to look at it.
1 Like
Hi @Gabriel 
!
Thank you very much for sharing a sample doc 
!
So, here’s the SwitchIf() formula I think you were looking for (for your button SET ABC) :
SwitchIf(
thisRow.[ABC (LookUp Text)] = "",
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = "A").First(),
thisRow.[ABC (LookUp Text)] = A,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = "B").First(),
thisRow.[ABC (LookUp Text)] = B,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = "C").First(),
thisRow.[ABC (LookUp Text)] = C,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = "A").First()
)
It is different from the formula you used to update the value of your field 123 with your button [SET 123] because you used a lookup field and what lookup fields do is create a bridge between tables at a row level…
So, a lookup field can’t return any other type of datas… Only row(s) …
And this is what this SwitchIf() does …
In Coda, a table is a list of rows and each very specific values you put in the various fields of a table is somewhat stored, behind the scene, within the Reference of a row (which is the value in the field you set as the Display column) more than often abbreviated as simply a row (generally speaking) .
The Reference of a row is then the value allowing you to access all the other values in your various fields …
When you created your lookup field in your table HELP you gave to that table the ability to link a specific row in HELP to another specific row in ABC (as it’s a single-select lookup).
So, for the SwitchIf() to work in this case, you need to compare a row to another row in the conditions you set up :
E.g.:
[ ... ]
thisRow.[ABC (LookUp Text)] = A,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = "B").First(),
[ ... ]
This part of the SwitchIf() kind of says :
If the value in [ABC (LookUp Text)] for thisRow is equal to the row A in the table ABC, then :
Take the table ABC and in the whole list of rows of that table look for (Filter()) a row where the specific value (CurrentValue) in the field [ABC (LookUp Text)] is equal to the text value B.
As Filter() returns a list of rows when used on a table, it will return here a list of one row (as there’s only one row with the text value B).
To isolate the actual row from the list and “force” Filter() to return the appropriate row (and not a list of one row) we can add .First() at the end .
All the other pairs of condition/result do the same thing as this bit of formula (except for the blank ("") one)
I could have @Ref directly the rows in the SwitchIf()…
Or apparently let Coda guess the result I’m looking for as this seems to work too :
SwitchIf(
thisRow.[ABC (LookUp Text)] = "",
"A",
thisRow.[ABC (LookUp Text)] = A,
"B",
thisRow.[ABC (LookUp Text)] = B,
"C",
thisRow.[ABC (LookUp Text)] = C,
"A"
)
But I’m personally not a big of letting Coda guessing what I mean 
…
I hope this is not too confusing and it will help you a little 
…
Don’t hesitate if something is not clear or if you have questions !
Hello @Pch, and a huge thank you for such a detailed (and a bit scary I admit! 
) answer!
I’m going to have to dive into all this information and analyze/assimilate it!
And then I’ll let you know where I stand
1 Like
I’ll admit that I might have a gone a bit too far… Sorry …
But I can tell you as a TL;DR that your SwitchIf() didn’t work simply because you tried to compare some apples with bananas …
E.g.: thisRow.[XYZ (LookUp Select List)] = "Z"
thisRow.[XYZ (LookUp Select List)]is a row"Z"is a text value
from this SwitchIf()
1 Like
What confused me was that since Coda offered to create a table from the Select List I was creating, I thought it might still work.
But I knew it would make things more complicated!
What was also surprising was that it didn’t display an error message, but it still displayed the first item of the SwitchIf…
This surprised me as well to be honest 
…
1 Like
I continue my exploration, and try to understand…
I copied and pasted
SwitchIf(
thisRow.[ABC (LookUp Text)] = “”,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “A”).First(),
thisRow.[ABC (LookUp Text)] = A,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “B”).First(),
thisRow.[ABC (LookUp Text)] = B,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “C”).First(),
thisRow.[ABC (LookUp Text)] = C,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “A”).First()
)
but it doesn’t work. The button is greyed. “Unknown Reference. We could not fint any Table, View, or Control with the name A anywhere in the doc”
Then I modified with
SwitchIf(
thisRow.[ABC (LookUp Text)] = “”,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “A”).First(),
thisRow.[ABC (LookUp Text)] = “A”,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “B”).First(),
thisRow.[ABC (LookUp Text)] = “B”,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “C”).First(),
thisRow.[ABC (LookUp Text)] = “C” ,
ABC.Filter(CurrentValue.[ABC (LookUp Text)] = “A”).First()
)
As for my own test, it loops between “A” and “blank”.
Then i copied and paste
SwitchIf(
thisRow.[ABC (LookUp Text)] = “”,
“A”,
thisRow.[ABC (LookUp Text)] = A,
“B”,
thisRow.[ABC (LookUp Text)] = B,
“C”,
thisRow.[ABC (LookUp Text)] = C,
“A”
)
And that works!
I have two questions, because I’d like to understand the logic,
What represent “current value” ?? I can’t figure it out (but I must admit that it took me a long time to get used to thisRow and that it’s been a very short time since I understood it ! 
)
And for:
thisRow.[ABC (LookUp Text)] = A,
“B”,
How can it “guess” that (A=) (A with the “row chip”) is from the other table (usually it seems to me that you have to indicate the name of the table first, isn’t it?)
I apologize for these questions, @Pch !
Yeah - this is pretty deep stuff. I often wonder if a custom Pack could encapsulate all this logic and perhaps hide a lot of the complexity by simply leveraging the Autocomplete interface.
1 Like
Hey Gabriel,
Your problem is that you are trying to fill a lookup column with plain text, while you have defined the lookup column to contain items from another table (which makes them into objects).
Your formula should like this:
You get these objects by typing @A and then hit the tab key. Do this for all the letters. I used switch because it doesn’t make sense to keep on typing the formula over and over. So, what this formula does:
look what is in ‘field ABC (lookup text)’
if it has object A, fill it with object B, if it has object B, fill with object C, etc.
there is a fallback (last line): if all the above fails, wilt it with object A
You can use switchif(), but you have to do a lot more typing.
You can work with characters like “A”, “B”… but then your receiving field needs to be of type T (Text), not lookup.
Working with objects generally is the better way to go, because you can change an object (for example, if in your source tabel object A is changed to A1, you don’t have to go back into your button formulas, every object in your tables, but also in the formula’s, is changed to A1.
Your set123 button works because you are putting the values into a select column, which selects from whatever you allow to be selected (you entered the list 1,2,3). This is generally not the way to go, because if your select list changes, you have to edit the button formula
To see how to fix it you can copy my doc with SET ABC and SET XYZ buttons that work, one with switch() and one with switchif():
At the end of my document is an alternative example to show some other possibilities
1 Like
No need to apologize @Gabriel … We’re all here to learn, help and/or get help and share stuff generally speaking .
Well, it’s simply because you copied/pasted the SwitchIf() formula from here into Coda …
Coda does a lot of the work for us when we write a formula: it suggests pertinent values to use in a formula which can be easily selected using ⇥ Tab which makes writing formulas faster…
If Coda can suggest things, it can also guess and retrieve …
But here, because you copied/pasted this “text” formula, if Coda could retrieve the 99% of the formula, it simply didn’t know how to interpret what appeared as a simple A … (which could have been anything… a row, a text value…etc ). Hence, the error .
All you had to do was to correct the formula (which Coda automatically points out) :
Note that, this can just happen … When you copy a formula from here and paste it into your formula editor, it might not work directly and need some adjustments/corrections .
Remember I said earlier that a table is a list of rows and that each specific value you enter in the various fields of the table is somewhat stored within the Reference of a row ?
Well, CurrentValue is linked to this … At least to the fact that if you ignore the display/visual data representation a table is, all a table actually is, is a list of rows…
You could somewhat represent a table like this :
TABLE
├ ROW 1
│ ├ Column 1 : Value for ROW 1 in Column 1
│ ├ Column 2 : Value for ROW 1 in Column 2
│ └ Column 3 : Value for ROW 1 in Column 3
├ ROW 2
│ ├ Column 1 : Value for ROW 2 in Column 1
│ ├ Column 2 : Value for ROW 2 in Column 2
│ └ Column 3 : Value for ROW 2 in Column 3
├ ...
│
└ ROW Nth
├ Column 1 : Value for ROW Nth in Column 1
├ Column 2 : Value for ROW Nth in Column 2
└ Column 3 : Value for ROW Nth in Column 3
In the list of rows a table is, CurrentValue represent each specific row in the table …
And if you go a bit further: For each specific row of a table (i.e.: CurrentValue), each very specific value in any column of your table is represented as CurrentValue.[Column Whatever]
E.g.: (See the sample down below )
Table.Filter([Column 2]="A")
Note that [Column 2] here is in fact CurrentValue.[Column 2] .
What happens in this formula is :
We take the list of rows the table Table is …
… and we ask Filter() to keep only the row(s) where in the whole list of values it has within the [Column 2] the actual CurrentValue is equal to "A"
This returns a list of 2 rows as I have only 2 rows where the (Current) Value is "A".
In other words, in a very simplified fashion, if you have a list of items, each very specific item in the list is represented as a CurrentValue .
This probably grossly cover what CurrentValue is !
(Sorry, this is really not easy to explain )
@Paul_Danyliuk actually covers all this (CurrentValue) somewhere in his Formula Fundamentals live stream (starting ~2:19:00) he did some time ago .
And if you go further, he also talks about list of rows and objects (~3:16:00) .
Thank you for this ressource, i’ll look into it as soon as i get a little extra time, soon hopefully!
That’s so great, thanks.
Your message is very encouraging.
I haven’t studied enough the difference between SwitchIf() and Switch(), so I’ll do that first. Indeed the formula becomes simpler.
I didn’t know you could type @A, at least I knew you could use @ in the canvas, but not like this.
Thanks for pointing out that it is more appropriate to work with objects, and for the embedded example document…
wow, thank you so much for such a detailed aswer.
I’ll dive into this, but I already thank you in the meantime…!..
1 Like