Lists Within Lists (Inconsistent Behavior)

1

Hi!

In a column formula, I was referencing lists of lists and filtering results using .first() this worked as I expected. But when referencing from another table or a “stand alone” formula it behaves differently.

Will someone explain what I am missing? I’m inclined to believe it’s the formatting of the list when referencing from the non-table origin??

3

Here’s the problem one:

image
image834×324 30.2 KB

What you’re looking for is:

  1. Split those three text columns into lists with

    Fruit.Split(", ")

    or more generally

    Fruit.Split(",").FormulaMap(CurrentValue.Trim())

    to catch situations where there’s no space after a comma (common human error)

  2. Combine those three with ListCombine() into a flat list of all items from all three columns (three lists):

    image
    image800×270 21.9 KB

    I’m not a big fan of doing .IfBlank("") here. I understand it “hides” the [] empty list brackets but it also ruins the data type consistency: the whole column is lists but suddenly you have a blank value here. It’s better to keep an empty list there. In the next step you’ll understand why.

  3. Finally, here you have to ListCombine() the lists from the (potentially) multiple rows returned by the filter. You probably also want to remove duplicates:

    image
    image840×184 17.8 KB

  4. Now about that blank problem; when ListCombine()ing those into a flat list, those appear as blank items on the list. If you kept it an empty list instead of a .IfBlank(""), ListCombine() would just collapse it without including anything at that spot.

    image
    image1133×507 32.7 KB

    So now you have to additionally filter for blanks here:

    image
    image837×211 19 KB

An extra tip: checking on just Month() will get you in trouble when your doc lives longer than a year :slight_smile: because May 2022 and May 2023 will return the same .Month(). To make things correct and also more performant,

  • ensure that the date on Monthly Report that is a month is always the 1st of that month,
  • make a separate column on Grocery List with a formula Date.DateTimeTruncate("month"). It will calculate to the 1st of that month.
  • simplify the filter to just check on equality of those. If you drop all functions within a filter and just match on equality, Coda will be able to create an index and match faster.
3 Likes
4

P.S. Oh, found the original issue in the doc history. Are you referring to why this formula is not the same as First on the table’s row?

image
image974×476 43.4 KB

Well because in the table, thisRow.[List of Lists].First() takes the value in the List of Lists and returns the first item of it — the first list of groceries.

And in the formula, this part here returns the column — a list of cell values. Even if it’s filtered to just one row for that specific date, this here is a list of cells — so a list of “list of lists”.

image
image961×490 44.2 KB

Doing .First() on it returns the first cell — the list of lists:

image
image961×490 38.1 KB

You need to further .First() it to get the first item of that list in there,
Alternatively write your formula as:

[Monthly Report].filter(Name=[date 1]).First().[List of Lists].First()

This should be a yet another reminder why you have to .Filter().First() when you’re expecting just one result.

5

Paul!

Thanks for the help! Your explanation makes complete sense.

As always, your educational in-depth explanations are a gift to this community. :grinning:

1 Like
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.