Please check out the document below. Near the bottom, I show two Formulas, that act differently but shouldn’t. And pose questions near the bottom of the doc too. Any insight?
Summary: A formula displays zero results when I prefix with CurrentValue, but displays correct count of results when I leave out CurrentValue. I seem to understand that Coda believes this is expected behavior? Unless they just meant to explain how to get around the unexpected behavior. I was told Nesting Filter/FormulaMap was causing a conflict to CurrentValue, and that WithName was not sufficient to correct that conflict, and that using CurrentValue was slowing down my formula anyway so I shouldn’t use it in the first place.
My comments regarding replys I’ve received about this formula:
“This doesn’t work because the Filter() argument is inside a FormulaMap() formula that also uses CurrentValue that corrupts that result.” *
… but thats what withName is for? And doesn’t it always grab the most recently established CurrentValue as a rule?*
“Also, CurrentValue is implied inside of the filter formula so it’s best to just remove that add’l CurrentValue and continue without it since it’s also probably slowing down that Filter() formula on top of getting mixed up with the FormulaMap() formula’s CurrentValue” *
… same thing here… I thought withname solved this problem and that it would always grab the most recently declared CurrentValue. Also as an aside, note the mention of CurrentValue slowing down the formula.*
Definitely weird. I simplified a bit by removing some extraneous withName()s, but the issue still stands.
t_Users.Filter(
CurrentValue.Notifications.Contains(@Notify Assigned New)
AND
Subordinates.Count() > 0
).FormulaMap(
t_Tasks.Filter(
CurrentValue.[Assigned To].Contains(
Subordinates
)
).FormulaMap(
"** " + CurrentValue.Task +
" is now LATE."
).BulletedList()
)
I agree that based on how CurrentValue is supposed to function, your formula should work as is. CurrentValue should grab the most recently established context, which in this case is a row in t_Tasks.
I assume you want to take Subordinates from the CurrentValue of the outer FormulaMap. But given how context overlapping works, you should not have it there: you’re inside a Filter so CurrentValue.Subordinates should NOT resolve to a value from a current row from t_Users. It could be some new undocumented behavior (some implicit WithName’ing), but I really doubt it, otherwise I’d know. Most likely there is a bug.
There are actually bugs around aliasing CurrentValue that I reported on Intercom, and while this here is a different thing, the cause could be common for both.
TL;DR: The red-circled piece above should not work, the formula should’ve given you an error there. It should work if replaced with CurrentUser.Subordinates though.
Thanks for the replies/insights @Nick_HE and @Paul_Danyliuk. Its nice to confirm that I’m not just misunderstanding, and that it is in fact not working as expected.
You’re right @Paul_Danyliuk , So either removing CurrentValue from Assigned To or Adding CurrentUser to Subordinates makes the formula work when Subordinates of a list of more than 1 (this old formula works if Subordinates is just a list of one user).
It is interesting that if I add a Subordinates column to my t_Tasks table, the formula does not break. It keeps its reference to the Subordinates of the t_User table. Which I think feeds into my “Thought” section at the bottom of my shared doc in my original post. Instead of using thisRow or CurrentValue, we should be selecting the desired row/column from the Formula Helper list that shows up. It seems that formulas aren’t interpreted anew, but save their initial choices regarding specific columns that have been selected. This is why using CurrentValue can slow down the formula. If the Formula Helper choice you made already links that Subordinate column in the Formula to the t_User table, why write out CurrentValue, as it then has to work through CurrentValue then .Subordinates, versus just already knowing which Subordinates it is referencing because somehow in the code behind they have permanently linked it to the t_User table through the Formula Helper selection you made.
I’m starting to wrap my mind around the CurrentValue makes it slower idea by thinking the following:
It is more performant to write “=Amount” than “=CurrentValue.Amount” … even though both thisRow and CurrentValue have the column “Amount” (and your heart tells you to be specific, help the formula processer out, help future yourself out to avoid future conflicts) … but you shouldn’t be specific, you should use the Formula Helper dropdown that appears as you are typing to select the correct reference. Because otherwise the formula sees [CurrentValue].[Amount] instead of [CurrentValue.Amount] (which you just see as [Amount] and it has to process both instead of it together as one.
Which means… we shouldn’t do CurrentValue.Subordinates… since when we wrote out Subordinates we already referenced [CurrentValue.Subordinates] and splitting it into [CurrentValue].[Subordinates] is unhelpful (purely because of the way formulas are implemented).
Which is all to say using and not using CurrentValue should both work equally (just one slower than the other) and here is an example where it works sometimes one way and not the other, and that should be looked at as a bug.
I have two large tables A and B. B has a column lookup with one value A Reference. A has a formula column B.Filter(CurrentValue.A Reference = thisRow) to show all B columns that reference it. When measuring performance, it kept being the slowest column to calculate. I just changed it to B.Filter(A Reference = thisRow) and it is no longer the bottleneck. So removing CurrentValue does indeed improve performance.
This is very surprising! I’m curious if this is something the Coda team plans to optimize soon. It’s nice to be able to be specific, and it’s strange that it’s not all the same under the hood. Otherwise I may have to go back through some formulas
I also wonder if this is a performance regression introduced with the (incredibly powerful and useful) WithName functionality…
