Does anyone know:
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?
You could build this type of dynamic table select support with a pack easily enough. But I don’t think you can do it with Vanilla Coda
The only vanilla Coda way to do it would be to have a large switchIf formula that literally just changes the entire executed formula based off your table selection.
Maybe @Paul_Danyliuk knows some sneaky Voldermort style way to get it done.
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…
Thank you for sharing Paul. This is a nice way of allowing users to setup custom workflows - and I agree it is not to much work to set this up. I will definitely put this in my toolbox for future use.
It does not immediately solve the issue of making the real table available in my formula from a global variable, like a named select, text field or otherwise.
Maybe @Xyzor_Max has another magic trick - I am sure he will respond with one answer or another.
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.
i did have a similar need for a client long ago.
i had one button for each table to be selected from and an empty ‘worker-table’, W.
each button for table X did the following…
so all the business logic is defined in the process-W button.
and all the other buttons are identical except for the table X name
all pure standard CFL, no black magic, no hidden formulas, no questions asked.
alternatively…
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
Hey @Xyzor_Max ,
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?
ok.
if a pack returns a table, it is a sync table.
or the pack could use the API to modify cells in-situ.
hmm…
i can also modify my VBA compiler pack to ingest CFL instead of VBA, and the thisRow and thisTable binding will still be done at runtime, so you wont need VBA.
nice challenge.
will work on that over the weekend.
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)
Here’s the “black magic” solution BTW, and it works with plaintext table/column names. Not that bad honestly:
Hello @Paul_Danyliuk
Wow!
Not that bad honestly:
The understatement of the year.
In combination with the Doc Explorer pack you can build a dynamic report generator - using all existing tables AND future tables without any additional programming.
There will always be some tables you want to exclude, but you can filter those out of the table-selection offered in a select field.
This solution shows once again your deep understanding of the innerworkings of Coda.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.