Hi there, this one’s actually halfway between a “tips and hacks” and an “ask the community”, since I’m eager to know what the black magic wizards and performance nerds out there think about it. You know who you are
Anyway, we all know and love the structure of a database model, but SOMETIMES, all we really need is the equivalent of a “quick and dirty” formula we would type in an Excel cell (outside of the auto-calculated formulas of tables that we also all know and love).
There might be better solutions for that scenario, but what I’ve come up with is the “free form” formula: if you use a Canvas column, you can basically type whatever formula you want for whatever row. And then you can retrieve the result in a “proper” result column in order to use totals and whatever else you’d expect from a classic formula column.
With that approach, you can still use other columns on the same row as long as you use thisRow in your “free form” formula.
Even better: you can actually comment your formula in the same canvas column (how quick and dirty is that? ) and still retrieve the number as long as you use the .toNumber formula:
However, I haven’t tested that approach on big tables yet and I kinda expect it to become sluggish at some point: I recall reading somewhere that any given canvas formula is kind of treated like a table of its own in terms of performance so my guess is that it wouldn’t help…
So, what do you all think about the “free form” formulas using Canvas columns? Let me know in the comments below!
we have been using this technique for some time with our clients.
some of the tables are quite big (1000+ rows) and no degradation in performance resulted.
the same approach also works for setting up different buttons on each row as well as canvas formulas.
we even use canvas-column cells to provide a different ‘menu’ of buttons for each row in a table.
it is an excellent way to overcome the restriction coda imposes of making a formula-column and a button-column always use the same formula for every row.
so well done for figuring this out AND for sharing it with this community.
Wow, thanks for the feedback Max, that’s really good to know!
May I ask what scenario required “free form buttons” and how you implemented them? I quite liked what I saw here and there with the hidden button function but so far I’ve always been a bit reluctant in using it in a “production” context, so that got me curious!
You do not neccessarily need the hidden Button() function to use this technique.
We use it in situations the user selects a particular ‘Type’ from a drop-down list.
For example a CustomerType selection. There is a CustomerType table with details for each of the types they can pick.
One of those details can be a canvas-column that contains a ‘menu’ of buttons that are unique to that CustomerType (eg for invoicing etc).
We do not need in that case to use the hidden Button() functions, the buttons are standard buttons on a canvas.
Thanks again. I had thought of using a similar trick to have “conditional buttons” before thinking about the canvas approach and could only achieve that with the hidden fuction - until now
Thing is, what you showed is not a “free form” formula since it’s the same for the whole column. But if in a blank canvas column, on a given row you use the formula =thisRow.Type.CanvasButtons, it should work.
The drawback is that you would need to type the very same “free form” formula for each and every row… Except I just found a trick : you can use a canvas template! Just add a subpage and copy-paste the =thisRow.Type.CanvasButtons formula. You’ll obviously get an error on that subpage but… If you use it as the “Value for new rows” in your canvas column, it’ll work!
I’m aware of using the ‘Value for new rows’, but that’s not very flexible, for example it doesn’t help much when you decide to change the logic of the buttons. And typing them row by row of course is out of the question.
Just thought Max implied it was possible to achieve something as flexible as with button() using this approach.
I understood from this that the canvas buttons are in the CustomerType table and that they are pulled from the Customer table.
Actually it is flexible, since every new row will get a standalone formula that just happens to be the same as the previous row. If you change the buttons in your Types database, it will change the buttons in your Customers database.
The tricky part is indeed to get the “context” as you mentioned, i.e. use the right customer in your button. In that case, I’d use something that I adapted from a trick by @Paul_Danyliuk (if I remember correctly). You can use a personal control with a relation on your Customers database. Then you only display your canvas button in a layout that you get to by pushing an “expand” button which, before opening that layout, assigns the customer you want to work on to the Control.
OK reading this out loud would probably not make much sense to anyone who doesn’t already use that trick, sorry… That would require some .gifs to explain properly but it’s already past midnight in here so maybe next time!
Ok, I got you now. That means you can not directly show the buttons on the table / card like with button(), but you have to always click a button to get them displayed in the detail view with the correct context. And it’s a bit more tedious to set up.
Anyway, it’s all about trade-offs and it’s good to have more options.
sorry, i should have indicated that my approach is based on using a canvas-template that is placed in the new row.
most of our workflows are controlled by actions; users clicking buttons at each step of the task. those buttons then set control-values and/or modifyRows() to ensure the dialogs show the correct details.
) and still retrieve the number as long as you use the 
