Hello there! I have a table with one unique column and one non-unique. I want to print a list of all dirrefent values in second column with numbers of items match these values. I think here in my screenshot second CurrentValue should be white as first, and should refer to FormulaMap context, not to CountIf. How can I do this?
2 Likes
There are couple of ways you could get desired result
-
you could put a column next to Type - lets say count and have following formula
thisTable.Filter(Type=thisRow.Type).Count()- and then have following formula on canvas[Table 1].FormulaMap(Type + ":" + Count).Unique().BulletedList()to get you the list in the format you want. -
you could create another table called
Typeand then have count calculated there and then use canvas formula to go againstTypetable to get list in the format you want.
hereās a document for you to play with
1 Like
But this method will not automatically extract all types from original table. It requires to know the list of types first.
Additional column method is slightly, you know, inelegant ) But ok, Iāll use it, thanks.
How about this evil bit of formula? It doesnāt need extra columns or tables, and handles new types being added. 
3 Likes
I added a description of how that initially scary-looking formula works. Itās pretty easy, actually.
2 Likes
Dear @Nick_Milner,
Thanks for the detailed explanation, that adds to much value when learning a new skill 
1 Like
@Nick_Milner has best evil bits! 
Very useful. 
Thanks.
Although you solution is pretty cool and clever, I think it works due to some magic or bug in Coda
Look: there is green CurrentValue in:
[Table 1].Type.Unique().FormulaMap([Table 1].Filter(Type=CurrentValue).count())
So it shouldnāt work, because Coda shows that it takes CurrentValue from Filter context, not from FormulaMap as it should be! Correct me if I am wrong.
1 Like
@Denis_Peshekhonov - in another table method, you would iterate over all records of types - thus you dont need to really know all the types upfront - as you start adding more types, they should get added to Type tables for you.
@Nick_Milner Thanks for taking time to work through this. while this works for now, I am almost certain that it is working due to bug in our code
in this formula - [Table 1].Type.Unique().FormulaMap([Table 1].Filter(Type=CurrentValue) - CurrentValue ideally should refer to record on Table1 but due to bug, it ends up pointing to outer loop of Table1.Type.Unique list - which obviously works in our favor for this scenario. When we fix the bug on our end and once we do, above formula will break.
you could see that if for formula [Table1].filter(CurrentValue) would give you records back and thats the correct behavior. (where current value refers to filter loop)
p.s - if you are relying on bug to work, this formula would look little less intimidating [Table 1].Type.Unique().FormulaMap(CurrentValue + ":"+ [Table 1].Filter(Type=CurrentValue).Count()).BulletedList() - but i would advice against it
Please, could you allow us to select which CurrentValue context to choose? Colors are pretty clear, just add several CurrentValues to list 
4 Likes
request noted - good that you caught the bug
Ha, I knew thereād be a simpler way of doing it and that Iād be doing it wrong.
I donāt think there should be anything wrong with your formula (even though, currently, there is):
[Table 1].Type.Unique().FormulaMap(CurrentValue + ":"+ [Table 1].Filter(Type=CurrentValue).Count()).BulletedList()
Both uses of CurrentValue are references to the FormulaMap instance rather than the Filter instance (even though the second use is inside a filter). Iād say this was ok.
The problem comes in because CurrentValue does double-duty to reference filter instances as well so you get a clash, but is CurrentValue really required in filters at all? For example:
[Table 1].Type.Filter(CurrentValue="Fruit")
ā¦is functionally the same asā¦
[Table 1].Filter(Type="Fruit").Type
ā¦which has no need for CurrentValue. It also āfeels betterā because follows the relational algebra rule āDo your SELECTs before your PROJECTs before your JOINsā (and might therefore be faster to execute as a result). If you donāt need CurrentValue in filters then the current implementation is fine and your lovely neat (and obvious now that I think about it!) code is also fine. Of course you then have a problem with all the code out there that does use CurrentValue in filters - maybe you could make a FilterValue instead?
Either way, however you fix the issue, some people are going to end up with broken docs - but thatās what betas are for. 
/edit - P.S. I still consider myself a noob at this. Iāve only been using Coda for a few weeks so I still have a lot to learn. This is a really good community.
3 Likes
Thanks @Nick_Milner - appreciate your thoughts and inputs to community - folks like you make this ācommunityā a good community - so looking forward to your continued contributions here.
regarding removing Currentvalue out of filter clause - while removing it could work when table is involved but filter can be applied to List as well -for e.g List(10,20,30).filter(currentValue> 15) to get all values above 15 from the list. so for now, we need to keep using CurrentValue to refer to the filter context.
again, we are aware of needs to refer to outerContext and would appreciate use cases /suggestions/feedback community provides us on this. so please keep them coming!
3 Likes
So thereās really no way to get Outer CurrentValues yet?
Hello @cnr,
I have been using the following method when I need to get around this, it is similar the answers above but I think gives a bit more flexibility:
Now, the two lists can truly be anything that you want to calculate or iterate through, and having seperate variables assigned to them and their counts helps simplify the final formula but is not necessary.
Then using a combination of mod and floor, iterate through a multiplied sequence of each listās count to at any point use Nth against starting lists to access what would essentially have been the outer and inner currentValues had you instead used a 2 layer deep formula map on two lists.
@Krunal_Sheth, do you see any problem with this?
Thanks!
1 Like
Desperately need the possibility to access outer CurrentValues in scenarios like Sequence(...).FormulaMap(...Filter(...Nth(CurrentValue))) where I need the CurrentValue to point to the integer in the sequence.
Right now Iām forced to find insane workarounds that break the docās UI and look like this:
This feels like Iām plain raping Coda now 
1 Like
This has been heavily discussed at Coda. The main thing weāve struggled with is a good syntax for this. What would you picture?
Here are a couple options:
- Multiple levels of
currentValue:currentValueis the inner scope,currentValue1is the next level of scope, thencurrentValue2is the next level and so on - Aliasing: E.g.
Filter([Tasks] as t, t.Value = thisRow.Value) - ForEach(): E.g.
Foreach(t in Tasks.Filter(....), t.Value)ā note that this would produce a list, so may need a set of aggregator / reducer functions
Other ideas? Any of those sound right?
4 Likes
Option 1: Multiple Levels of CurrentValue
The term āCurrentValueā is useful, especially for users from non-coding backgrounds. It reduces abstraction. But a layer of abstraction nonetheless remains, in that the user will have to keep straight which CurrentValue reference goes with which list. Not a huge deal, but still.
Option 2: Aliasing
I like how this syntax pairs the source list with the current value reference right there in the formula ā makes it easy to see which current value reference goes with which list. However, single-letter aliases increase abstraction and can get confusing, especially for non-coders. Custom text string aliases are less confusing, but then the user would be responsible for that ā coders would probably prefer custom naming, while non-coders would probably prefer that the naming just be automated with something simple.
Why not both? 
Filter([Tasks] as CurrentValue3, CurrentValue3.Value = thisRow.Value)
$.02.
2 Likes
For me Option1 is the most straightforward. Colors can also help to match the right level of CurrentValue with the right list.
3 Likes
@shishir Given how youāre currently doing it elsewhere in coda, e.g. when thereās column name ambiguity, and given the apparent need for backwards compatibility ā I think it would make the most sense to just have multiple CurrentValue options in the suggestions list, and distinguish them by color and type, and specify in the variable description where it comes from. It would look something like this:
CurrentValue1, CurrentValue2 would be confusing. Itās not clear that 1, 2 etc refer to nesting. This would make more sense to keep it for the scenario when an outer function needs to pass multiple values down the FormulaMap, e.g. a full join of two lists.
I wouldnāt introduce new syntax just for this. I really donāt see a problem piggybacking on existing system (unless thereās a technical one). As I said, you already have disambiguation mechanism in your formula editor. Actually, the screenshots above are not fake
Another option would be to give these variable completely distinct, verbose names e.g. EvaluatedRow in filter, ThisObject or Iterator in FormulaMap or something. But Iād actually stay with CurrentValue.
9 Likes