I have a button to create a list of the rows of a specific table. But I want to not hardcode the table in the button function, but select one from a select field and have the button formula use the table that I selected. I know how to make a list of all document tables, I know how I can get the table-ID or table URL-into a canvas formula, but I don’t know how to use these in my button formula in such a way that it is seen as a table reference.
Can this work in Coda?
The Voldemort style formula sure is possible — you can do object modification on a button and replace table references from one ID to another. However, you’ll either need to a) replace all the column IDs as well, or b) make sure all tables are duplicated from a single table, hence column IDs are also duplicated and are the same for the same columns across those tables.
There is a nicer way I recently found, and while it does use a little bit of black magic, generally it’s a much nicer UX to set up because you get to code your formulas yourself, CFL only, no reference rewriting.
This is an older impromptu video and I made this nicer since then, but it’s the only one I recorded on this matter so far. Hope this shows the trick. This basically allows to extract actions into selectable steps and then dynamically construct what you want to happen (sure, you don’t have thisRow this way but you can plan of some workaround, e.g. store thisRow into some user-specific global variable somewhere)
P.S. On the “column IDs” thing, actually I think @Xyzor_Max earlier demonstrated that IDs weren’t needed, and one could just use table and column names instead of $$ references (he worked on VBA to CFL converter or something). Never tried this myself and not sure if this holds true, tagging him hence. If that’s true, the deep object modification approach could actually be easier.
Actually, given the speed of Coda, the Swich() or SwitchIf() would work. The one thing that is holding me back from using it is that is it not dynamic and you have to maintain the formula, which is not ideal in a multi editor environment. But it is better than no solution at all.
When you talk about a pack solution, you would input the table ID and return a (working) table reference/object? I certainly would be interested to see that work…
Immediately not, but you can create separate buttons for each table you want to support, and then dynamically through a select, one of the buttons will be pressed. It’s basically a way to turn a SwitchIf into a selection of rows.
Yes, I agree that will work. It just feels so backward that I can’t find a way to inject a table in a variable. Doing it manually, like pasting “[myTable]” in a canvas formula, works. But getting the table name in a canvas formula (or button or whatever) seems to not work.
I guess for the time being inserting a switch allows me to use one canvas select (with all names from the doc explorer table list) and one button with my custom function (including the switch) will work. Since I have a lot of tables the switch is going to take up quite a bit of space, but I can put that in a hidden table formula somewhere.
Thanks a lot for your thoughts on this.
if you wrote your business logic in Visual Basic, my VBA compiler can solve the problem easily.
the “thisTable” and “thisRow” variables are processed using late-binding, so the same code string can be used by buttons in all the tables, and when they run, the references are resolved at run-time to the correct rows. so you store your business logic code in a single place.
but onboarding Visual Basic is not trivial, so DM me if you think this might be useful
Yes, I get this (solution 1), but if you have a new table, you need to make a new button just for this purpose - and if you have more than one of these functions, you get another button for each table - or a a switch in each button for different processes.
So, I kind of like option 2 better, although I don’t particularly like to rely on VB just for this one purpose. If we are going to have to use a pack, I prefer to use a pack to return the table object at run time.
And, if we are going to use a pack anyway, it might as well offer the same option for column objects.
@Xyzor_Max and @Scott_Collier-Weir : what should the return object look like, as far as the format in the pack is concerned - so that it acts like the object-pills we use everyday?
I think what Scott meant was setting values through the API. In the pack, naturally, you could go fully dynamic with which table and which columns you wanted to add values to.
The thing with CFL is that Table and Table are two different things in different contexts Sometimes it’s a reference to an object, e.g. like Table.AddRow(...) and sometimes it’s a list of rows.
Table.AddRow( /* Here, Table is a destination reference, i.e. what to modify */
Column1, Table, /* Here, Table is a value: list of all rows (refs) to set */
Column2, Column2 /* Same here: the former is the destination and the latter is a value (all values of a column) */
I don’t think you can construct a destination reference and feed it to CFL as the first/carried argument of AddRow. When you obtain Table from e.g. a dropdown, it’s just gonna calculate to a list of rows of that table, not a destination reference. So I don’t think you can actually dynamically supply destination refs, only hardcode them in formulas (switchifs or my approach)