How to reference the attribute of the result in a formula?

1

Hi, we are using a formula such as:

=[List of Sprints].Filter([Start Date]<=Today() AND Today()<=[End Date] AND [Type of sprint] =[Bi-weekly])

and we’ve given it a name “CurrentSprint”.

That formula is returning an element in a table and we are trying to figure out how to access an attributes of that element.

We’ve tried with:

=CurrentSprint.First().[Start Date]
and
=CurrentSprint.[Start Date]

But that doesn’t work. Any ideas about how to make that work?

Thanks!

1 Like
2

It appears named formulas are actually Controls, not row references in the table. You can see this by looking at the error message when writing =RowID(CurrentSprint) in the canvas. As of now the only way I can think of to access the row attributes is referencing the table again (which eliminates the benefits of named formulas in my opinion):

=[List of Sprints].lookup([Task Column],ToText(CurrentSprint)).[Start Date]

1 Like
3

Hey @Al_Chen, thank you for this message.

I agree with you that doing this is not straightforward yet and I think the team at Coda knows it.

@evan helped me figure out how to manipulate the data in the way I needed it.

When naming the formula as a Control, I end the formula with a .Frist() method like this:

=[List of Sprints].Filter([Start Date]<= StartDateOfSprint AND StartDateOfSprint <=[End Date] AND [Type of sprint] =[Bi-weekly]).First()

This makes it possible to access the attributes directly in the canvas like this:

=CurrentSprint.Value.[Start Date]

Thanks again!

2 Likes
4

Interesting, I didn’t know using First() opens up the attributes of the row for you when using named formulas. One question I have for you is what happens if the filter returns more than one task? By chaining First() at the end of the formula, that means you only get the first task from the filtered list.

1 Like
5

Great question.

I haven’t attempted to access the attributes within a list of elements returned by a Formula/Control. It would be great to learn to do it.

Does anyone at Coda have an answer about how to do this?

6

@Raul_San_N.H @Al_Chen Great question, and definitely acknowledge this isn’t as straightforward as we’d like it to be!

The main issue here is that we can’t currently “dereference” an array like you can an single reference in the canvas. An example is entering the formula =@Sprint.[Sprint Start] in the canvas. Given Sprint returns an explicit reference to a row in the Sprints Table, we can dereference that and project values like the Start, End, Owner, etc.

The result of Raul’s formula actually returns an array of references/Sprints (even though that array has one value in this particular case). So trying to do the projection does not resolve. Entering .First() then resolves to a single reference, which we can then project from.

The choice here is either 1. make it clear that Raul’s formula returned an array, not a single reference, or 2. allow for dereferencing an array. Have floated this to our cofounder - will let you know once we have a resolution!

2 Likes