That is a tricky one. We need a whole section dedicated to debugging and helping with this!
For me it’s understanding how to use formulas for something I can describe in words but am unsure how to actualize in the Formula logic. For instance, if there’s a value in a cell, then do X. I found the documentation on the Formulas a little dense (I’m not an engineer) so turning to Community and Help chat was integral to my success.
Hope that helps!
“Named Parameters”. See this thread.
Added @Ian_Nicholson - even I forget about that one now and then!
Instead of getting a copy from another users doc,
That you can copy content from a “view only” doc and place it in a doc you are the owner of.
Automatically you will be able to see all formulas used!
Important: if the content you copy has “related tables”, “controls” and "formulas"over more pages, it’s important to take them with you. (see doc map)
Oh! Not hard to find, but very counterintuitive.
Say, I want to get a cell value from the first row of a table.
Following the top-down logic, I’d first select the table, then the row, then the cell, i.e. write the formula like this:
[Table name].First().[Column name]
But this won’t work! The correct order of selection is: table -> column -> row:
[Table name].[Column name].First()
This feels wrong, because it feels like I’m making Coda first pull all the values for the column for possibly hundreds of rows, to discard everything but the first one. I assume Coda devs were smart enough and optimized this so that Coda wouldn’t actually pull the whole column, but still it feels counterintuitive.
Interesting that if you separately calculate a row (e.g. in a named formula or a single value lookup cell), then you can select a cell from that row just fine:
P.S. The counterintuitiveness shines when the cell is actually a list of values, and you need to only select the first value from that list.
[Users].First().[Tasks].First() — invalid
[Users].[Tasks].First().First() — way to go
I had a hard time discovering that when I want to use a drop-down type select control’s value I need to use .value
This also gets me every time. Both should be valid IMO.
Just stumbled on this shared doc. Love it!
A simple calculation that took way too much time and work (Calculating a dynamic age based on a birthday/date field):
If(thisRow.Birthday.IsNotBlank(),Today().Year()-thisRow.Birthday.Year() - If(Date(Today().Year(),thisRow.Birthday.Month(),thisRow.Birthday.Day())>Today(),1,0) ,"")
I feel like this could be in the “hard to discover” category and may be worthy of it’s own formula? I’m surprised more users don’t need an age calculation!
One more thing that was hard for me to discover:
The fact that I could use any type of display (i.e. charts, cards, nested Detail views) within a Detail view, not just a table:
Props to @Francesco_Pistillo for the trick:
Also one more thing. Super confusing.
The fact that the list of options here:
depends on which columns are visible when this view is in Table mode!
That’s right, not the columns that’s visible right now, but only when you switch to Table mode, make the required ones visible, and then switch back to Detail
Learned this from @Krunal_Sheth through Coda support.
@mallika I took me a while to discover that when needing just 1 section (but not all the other sections) from a community template … since there is no way to Shift / ctrl + click and then delete, the most efficient way to delete multiple sections is to add the unwanted sections to a folder and then delete the folder containing all the unwanted sections
Oh, and this fact too:
When you reference the master table in the formula, it always works on a full set, whether or not a filter is applied.
When you pull the same data from a view, it will only use the filtered subset.
Sorting setting doesn’t matter in either case, you need to explicitly
.Sort() in your formulas:
And this is sometimes very useful also in terms of efficiency, so relying on [Views] (if possible) it helps to improve the overall “speed”.
IDK, I prefer to rely on master tables only — more predictable that way. I like to explicitly write in my formulas what exactly I need from the full data set and how.
One exception is that if it’s a formula that is only used once and nearby the view in vicinity — e.g. a header saying Hello
User(), today you have
[Your tasks view].Count() tasks. I.e., when it’s apparent that this formula describes the view beneath it.
UPD: When I meant “rely on master tables only”, I meant in the context of working with the full data set directly and not segmented via lookups. If e.g. I have a table of users and a table of tasks, I’ll first pull in each users’ tasks into a lookup column of a Users table, then refer by
@Some user.Tasks. I won’t write it like
Tasks.Filter(User = @Some user) in that case. TL;DR: If I can connect the data, I’ll do that first.
@Paul_Danyliuk, I had the same feeling: somehow it’s like not really be sure of the underlying data.
I discovered it really helps a lot (i.e. better UI performances) when many rows - and many columns - are involved.
Often it’s just a matter of proper View naming.
Think about the overall historical
Exchange Rates (master table) and
Current Rates (the View with current valid ones) anytime you need to have exchange computations.
Looking up a single field with few rows dramatically increase computation times.
Anyway: didn’t want to drag the attention away from this post
@Federico_Stefanato Never tested it actually, but I think that if we were after micro-optimizations, we wouldn’t be using Coda but write it in C++/Java/etc
I mean, with tools like Coda, I believe good data design and sensible conventions are more important than performance.
But yeah, there are always exceptions, that’s why I always say use your best judgement.
However, in your case I’d say you could approach this differently and have both better design and performance. I’d keep the historical
Exchange Rates table, but instead of the view I’d make a
Currencies master table that looks up the most recent exchange rate for each currency into a lookup column. Then reference the
Currencies table when I need to get the most recent rate — e.g. with a direct row lookup like
I’d love to keep this discussion open
I’ll write you in private (in order not to deviate this post)
@Paul_Danyliuk - this is fixed now. now you would see all allowable columns in the dropdown. - the fix would be deployed in a day or two. Thanks for reporting the issue.