Outer CurrentValue access

#1

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

There are couple of ways you could get desired result

  1. 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.

  2. you could create another table called Type and then have count calculated there and then use canvas formula to go against Type table to get list in the format you want.

here’s a document for you to play with

1 Like
#3

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.

#4

How about this evil bit of formula? It doesn’t need extra columns or tables, and handles new types being added. :slight_smile:

2 Likes
#5

I added a description of how that initially scary-looking formula works. It’s pretty easy, actually. :slight_smile:

1 Like
#6

Dear @Nick_Milner,

Thanks for the detailed explanation, that adds to much value when learning a new skill :clap:

1 Like
#7

@Nick_Milner has best evil bits! :laughing:

Very useful. :muscle: Thanks.

#8

Although you solution is pretty cool and clever, I think it works due to some magic or bug in Coda :slight_smile:

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.

#9

@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 :slight_smile:

#10

Please, could you allow us to select which CurrentValue context to choose? Colors are pretty clear, just add several CurrentValues to list :innocent:

#11

request noted - good that you caught the bug :slight_smile:

#12

Ha, I knew there’d be a simpler way of doing it and that I’d be doing it wrong. :slight_smile:

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. :slight_smile: 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. :wink:

/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. :slight_smile:

3 Likes
#13

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