Formula fails in one column, but works across two

Suggestion Box Bugs
1

Example document linked here.

Problem : doing a FormulaMap(currentValue.Property) is failing within one column, but working when split across multiple columns.

The columns of interest are these in the Bundles table:

  • Debug: currentValue
  • Debug: Indirect Lookup
  • Debug: Direct Lookup

Debug: currentValue

This column successfully yields that appropriate row from the Discounts table.

list(
Discounts.Filter(thisRow.[Rack One-time] >= CurrentValue.Start).Sort(true, Discounts.Start).Last()
).FormulaMap(CurrentValue)

Debug: Indirect Lookup

In this column I’m referencing the Debug: currentValue column and getting the Base property. This shows that the element in the Debug: currentValue column is, in fact, a reference to a Discounts table row.

thisRow.[Debug: currentValue].Base

Debug: Direct Lookup

But when I try to combine those two steps into one formula, I’m given an error:

list(
Discounts.Filter(thisRow.[Rack One-time] >= CurrentValue.Start).Sort(true, Discounts.Start).Last()
).FormulaMap(currentValue.Base )
2

I think this formula is overcomplicating things a bit and that’s causing the issue. Give this one a try instead…

Discounts.Filter(thisRow.[Rack One-time] >= CurrentValue.Start).Sort(true, Discounts.Start).Last().Base

Discounts at the beginning is already your list, so there’s no need to use the List() formula there. After you filter this, the resulting data is also a list, so you’re still good.

Then you’re sorting, then taking the last value. The value returned is a row value, so you can use dot notation to return any column you need at this point.

3

@BenLee this was a simplified example to show the issue.

In my actual use-case I was using the currentValue several times in an arithmetic calculation. I was trying to avoid repeating the Filter/Sort/Last every time and didn’t want to have to create an additional column.

That said, am I misinterpreting how the Coda syntax works?

Since I can store the value in a separate column then reference the object’s property from that column, as far as I can tell in my limited knowledge, it suggests that there’s a bug in the syntax parser.

4

I’m not sure what the final goal is, but we can break this down in a different way to see if anything jumps out at you for your setup.

Filtering a table will result in a list, so adding list to it can make a list of lists. Instead of a list of values, you have a list of embedded lists that have values. So reaching the column value isn’t straight forward.

(one, two, three) which is 3 values verses ([one, two], [three]) which is two lists and one of those lists contains two values.

I’m not sure what the last part of your formula is for, the FormulaMap(CurrentValue.Column). It looks like you’re down to what you want to be a single value at this point, so there should be no need for a FormulaMap().

If you can say what you need form that formula, I can see if there is something going on.

list( Discounts.Filter(thisRow.[Rack One-time] >= CurrentValue.Start).Sort(true, Discounts.Start).Last() ).FormulaMap(CurrentValue)

But it looks like you’re wanting to filter the Discounts table to a number of rows. Then sort those rows by a specific column. Then take the last row in that list. Then return the column value of that row.

5

Hi @BenLee - I’ve updated the document to be more clear. It now includes the actual calculation I was trying to run, and the Exception Occurred error I was receiving.

Let me know if this doesn’t clear up why I think this is a bug.

6

I really appreciate that example doc, it shows the issue really well.

I’ve added it to our internal quality tracker.