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??
Here’s the problem one:
What you’re looking for is:
Split those three text columns into lists with
or more generally
to catch situations where there’s no space after a comma (common human error)
Combine those three with
ListCombine() into a flat list of all items from all three columns (three lists):
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.
Finally, here you have to
ListCombine() the lists from the (potentially) multiple rows returned by the filter. You probably also want to remove duplicates:
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
ListCombine() would just collapse it without including anything at that spot.
So now you have to additionally filter for blanks here:
An extra tip: checking on just
Month() will get you in trouble when your doc lives longer than a year 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.
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?
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”.
.First() on it returns the first cell — the list of lists:
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])
.[List of Lists].First()
This should be a yet another reminder why you have to
.Filter().First() when you’re expecting just one result.
Thanks for the help! Your explanation makes complete sense.
As always, your educational in-depth explanations are a gift to this community.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.