Outer CurrentValue access

[GJ_Roelofs]

While aliasing and naming would work better from a formal / coding perspective, I do agree with @Paul_Danyliuk that the coloring is already a convention for competing variables so why not re-use it?

[Dalmo_Mendonca]

@Zsolt_Bako

For me Option1 is the most straightforward. Colors can also help to match the right level of CurrentValue with the right list.

Second this.

Coloring is great to identify the scope, event though it’s currently broken with nested functions. But I assume that would be fixed.

Just to throw in another idea, in addition to coloring, you could think of appending the formula name to it, e.g. ....FormulaMap(...Filter(currentValueFilter = currentValueFormulaMap))

[Ryan_Martens]

I also agree this is the best solution. However, one way that it might lead to frustration is in copy-pasting… ideally the level of [currentvalue] would be kept when copy-pasting formulae.

[Paul_Danyliuk]

Copy-pasting doesn’t work reliably anyways. I think it’d be a fair tradeoff.

BTW for the record, here’s a workaround solution I documented elsewhere. Past due it belongs in this topic:

What to do when CurrentValue is overloaded

That’s a know thing: There’s a workaround with storing the outer CurrentValue in a cell somewhere (e.g. same row dedicated column, or a helper table). E.g. here’s the way I’m iterating over Value Drivers, then each of their Metric Uplifts, then for each of those I insert six rows with a Sequence().FormulaMap(). Note how I store _Current Value Driver and _Current Metric Uplift into a cell before using it in a nested FormulaMap: [image] [image] (Fun fact: in retrospective, in my case none o…

[Johg_Ananda]

I want to bump this. Just came up for me. I am trying to emulate the output of a Pie Chart, which does this type of thing as part of its default function … I want to be able to get an array of all the ‘pie percents’.

+1 to Option 1 @shishir

[Stefan_Singer]

I too am struggling with the need of being able to do FormulaMap(SomeOtherThing.filter(OUTER_CURRENT_VALUE_NEEDED_HERE))

[Paul_Danyliuk]

P.S. Re copy-pasting. I think internally in Coda references in formulas are copy-pasted by their full $$[] descriptors, not just the names. And it’s just the names when you paste it outside of Coda. My point is, correct CurrentValues may actually stay preserved when copying formulas within Coda.

More about the $$[] operator:

A somewhat of a documentation of hidden formulas [Added mechanics of nested buttons]

Fiddling with your research doc I found out about the $$ operator used to create references. E.g. $$[cell:grid-ahmvaWqCgY:c-ni32haMaa_:i-pHC2juX1xr:false:false:Value] [image] [image] Syntax goes [<type>:<tableId>:<columnId>:<rowId>:<bool>:<bool>:<displayName>]. Some params can be omitted depending on type. Not sure what the bools are for either.

Types are (click to see)

"canvas"; "cell"; "column"; "controlGrid"; "document"; "grid";…

[Stefan_Singer]

Sorry if I’m hijacking this thread, but maybe there’s a way here I haven’t thought of

Where I’ve hardcoded “Linnéa”, I’d like to use the CurrentValue from the FormulaMap. Is there a way?

Edit: It wasn’t clear in the screenshot, but there can be multiple assignments (and members working) on any given day

[Ed_Liveikis]

My 2 cents on this topic, while having multiple CurrentValue's in this list and highlighting the matching scope with coloring/bold is a nice UX consistent with the current webapp, I believe that an explicit solution is required - certainly at the server API level.

So why not just focus on that first, and roll it out to the UI level as well as a first pass?

So something like :CurrentValue or some other “escaping” character (like ... for file systems) to refer to the outer scope(s), as mentioned earlier as a possible solution.

I don’t see how to get around the need for explicit syntax - the other solutions are just syntactic/visual sugar to layer on top of this.

[Paul_Danyliuk]

Can you please share the doc here or with just me actinarium@gmail.com? I think there’s a way around this.

E.g. if you pull in all assignments for a user, you can rewrite your FormulaMap as:

Members.FormulaMap(List(
  CurrentValue.Name,
  CurrentValue.Assignments.Filter(Date = thisRow.Date).Load.Sum()
))

[Stefan_Singer]

I think you might be on to something, we just have to filter by task start and end date instead. I think…

Look at Workload Lab section:

[Paul_Danyliuk]

I’ll look a bit later — going to be super busy rn.

[Stefan_Singer]

Not sure what’s happening here, why can’t I access the Assignments columns after the Filter() call?

thisRow.Members.FormulaMap(
  List(
    CurrentValue.Name,
    CurrentValue.Assignments.Filter(
      AND(
        Start<=thisRow.Date,
        End>=thisRow.Date
      )
    )
  )
)

edit: It’s as if Filter() returns Members.Assignments.[Project task]…

[Ander]

@Stefan_Singer

It’s unclear to me exactly what you’re after. But here’s one approach that might give you some ideas.

[Stefan_Singer]

Hey, @Ander, thanks for giving this a shot! Interesting with how you use Sequence and Nth, I did not know one could construct loops like that. Your approach is close to what I want, I want the sum of load per assignment, per user. As in: Stefan: 125%, Pontus: 50% etc. You’re close but I don’t get how to modify your column 8 to give that result.

[Stefan_Singer]

Using Sequence and Nth, I managed to solve it

Sequence(1, thisRow.Members.Count()).FormulaMap(
  List(thisRow.Members.Nth(CurrentValue),
  thisRow.Members.Nth(CurrentValue).Assignments.Filter(
      AND(Start<=thisRow.Date,End>=thisRow.Date)
    ).Load.Sum()
  )
) 

Thanks @Ander for giving me a way to the solution!

[Stefan_Singer]

Eh, hey @Ander, you’ve modified your document afterwards, right? Since I can now see what I was going for in it?

[Ander]

@Stefan_Singer

Yes, I cleaned it up for future reference. Nice work solving that. I learned a new technique, thanks!

[Stefan_Singer]

But was the solution (The bulleted list in Member Load) there when you first shared it? Did I miss it? Shame on me in that case!

[Ander]

No no, it came after.