What does it mean when a formula returns empty brackets, like this?
[ ]
I’m running into when attempting to use a lookup formula, like in this contrived example in the column titled “doesn’t work.” This formula attempts to do a lookup based on a column that is a select list.
Here’s the doc:
I since figured out another way to make the “does work” column by using a different formula, but I’d like to understand where I went wrong.
Thanks,
Wallace
I think your problem is that the column “doesn’t work” is set to Text, where it should be Select List, isn’t it?
Hey @Wallace_White you got very close! What you want to be aware of is object type. When you are doing your lookup you are looking up Name, a text value as indicated by the T, with a thisrow an array. An array is a structured list of items; in this case it is the current row, and the items are the different columns in it.
So what we want to do is lookup this row’s name field, in the Employees table Name column like this:
lookup(Employees,Name,thisRow.Employee.Name
Actually, I think the formula has to be like this:
lookup(Employees,Name,thisRow.Employee.Name).Department
What I would like to know for myself: how do I mark text as code?
Good question!
There is the code block option in the toolbar so you can highlight text then click the </> icon. But the “markdown” way to add it quickly is just a backtick before and after. For a block of code, you can do three backticks and a new line at the beginning and at the end.
@Wallace_White you can use =employee.department instead of lookup.
Thanks, everyone.
First, @Alexey_Demin’s approach does work and is easier than lookup(). It’s actually what I had already done in the Current Department (does work) column. I just am trying to understand how to use lookup() properly.
So is [ ] what Coda shows whenever a formula returns an array that can’t be displayed in that cell? Or is it an empty array? Or do the brackets mean something else? I couldn’t find anything about them in Coda’s help.
As for the lookup formula, @Johg_Ananda’s approach as amended by @joost_mineur works, though I’m confused as to why. The documentation for the lookup formula shows that you can pass thisRow as a match value parameter. I thought that Coda always uses the Display Column if you don’t specify a particular member of thisRow.
But I’m also confused what thisRow.Employee.Name means in this context. thisRow is referring to the Events table where this formula is located, right? So the first part of the expression (thisRow.Employee) I understand, but there is no Name entity anywhere in the Events table, so why do I refer to it as if it is a member of thisRow.Employee? Is it because the Employee column is a lookup column that selects from Employee.Name values? So, if I changed the data source for the Employee column to something else, is it correct that I would have to change this formula to refer to the new source?
Here’s a duplicate of my original Coda page with an additional column called Current Department (revised lookup) that uses @joost_mineur’s formula:
Thanks again,
Wallace
Hi @Wallace_White,
Great questions here and actually something that comes up during talks on how to make some of these things more understandable.
The empty brackets, [] are just what you mentioned, an empty array.
In the first formula you posted, there was sort of an error in that nothing would be returned, but the formula was written properly and would return a list if the data it was trying to referenced was reachable. So it’s been difficult to figure out when Coda should show an error and when Coda should show that it’s a validly written formula, but not returning results.
For the other questions around thisRow and what are these things actually referencing, there are a few visual indicators that can help.
In your “Events” table and the “Employee” column, it is a lookup to your “Employees” table. What you are choosing in the dropdown there is actually the full row of the employee and not just the name. So that Events.Employee column, in the first row for “Jill” is actually the full dataset of “Name:Jill, Department: R&D”.
So if you create a new column and just use dot notation, you don’t need a lookup at all to find the department. You can simply reference the “Employee” column which is really a row from the “Employees” table, and then reference the “Name” column from that table.
Employee.Name
This will give you the Name and Employee.Department will give you department.
The visual indicator of what is a full row reference is the blue capsule around the text. If you hover over that, you’ll see the info from the original table.
For formulas, Lookup() is really just a form of Filter() so I have always used Filter() when I need to find results. Either one works, so go with what you’re comfortable with.
In your “Employees” table, add a new column and try something like this…
Events.Filter(Employee = thisRow).Count
Based on your current example, you should see a “2” listed in Jill’s row. It’s easier to see what “thisRow” actually is when using it from your original table.
I’m happy to explain more if you have more questions. I think trying these things might help to visualize what’s going on a little more. And yes, it is a sort of new way of working with data, so it takes a little bit to get into it. Once it clicks though, it’s difficult to go back to anything else.
Great. Thanks, Ben. Now I get it!
The empty brackets make sense for an empty array. I just couldn’t figure out where to look or search in the documentation to explain that. Besides there, maybe a hover tooltip could explain it, or a note in the formula builder whenever [ ] appears.
I made another page in this doc to, as you suggested, show an event count:
In its Events 3 table, it also uses a filter() in a new column. Good to know that lookup() and filter() can both be used for this sort of thing.
Thanks again,
Wallace
I was experiencing this same empty array issue as well - and YES the column type was Text.
Switching it back to Select List solved the issue.
I had a “Notes” column w/a button to allow adding multiple notes into a ‘Task Notes’ table (sorted descending to show the latest notes first).
One of the users mistakenly deleted the column formula when attempting to add herself a note into the column.
I wish there was a way to lock the table design / configuration while still allowing users to edit their entries.
Both Coda and Notion suffer from that. They allow to create a fully customizable workspace, but none of them has the tools to keep the design locked and the system still operational.
