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?