Unrolling Lookup/Filter Columns: Multilevel Filtering?

1

Hey all-

I have a table of projects (called Engagements) which each have actions associated with them. These actions are stored on a separate table (Actions) so I can keep values associated with them (tags, etc).

Previously, I was displaying four columns: Completed, My Pending, Pending by Team 1, Pending by Team 2. I built each of these to look up from the main Actions table separately. As we use this across our team, it slowed down because we have thousands of actions and hundreds of engagements (this is a lot of O(n) lookups and filtering on the same table).

My solution was to unroll this lookup so that there is a hidden column [All Actions] that the other columns could filter from. This was accomplished with: =Actions.lookup([Engagement ID], thisRow). This seems to be working—I have a list of all actions that the formulas can lookup.

However, the filtering further down the line is not working. Here is “My Pending Actions”:

=[All Actions (backend)].filter(Complete=false AND Owner="Chris").sort(true, [Date Taken]).BulletedList()

This is unfortunately returning a full blank. When I re-type it, Coda rejects the references to fields in [All Actions] and I’m not able to filter on the qualities in the actions. In short, I need to filter a column array based on the fields of the items in the array. Has anyone else done this multilevel filtering/dereferencing?

1 Like
2

have you tried to use the filter part twice rather then having the and in there?

=[All Actions (backend)].filter(Complete=false).filter(Owner=“Chris”).sort(true, [Date Taken]).BulletedList()

3

@Philipp_Alexander_Asbrand-Eickhoff this is a good idea but the issue is that the “Complete” and “Owner” fields are inherent to the objects in the list of actions and are not recognized by the formula.

Can you filter by a property of the objects being filtered? In other words, can you dereference in the formula language?

4

could you post a picture of the tables? Much easier to understand everything like this