Filtering by lists

Here’s a little problem that’s had me muddled the last few days. I would keep trying at it myself, but my partner is going to give birth this week and I’m really trying to get some last bits of our business coda doc working.

We have a fairly involved project management system.
Projects have workflows which are really templates for groups of tasks (called stages) which have their own sets of tasks.
Stages are booked to people - and the tasks belonging to those stages are all assigned to the booking.
A booking is assigned to a person.

I’ve made a dummy doc showing just tasks and bookings. But it contains the “stages / tasks” hierarchy.

There’s a few design decisions that mean there are some quirks that may confuse. The button to make a booking is on all rows - but pressing a button on a task in a stage with more than one task will bring all those tasks into the booking. This is by design and is necessary!

This all works - and in the real doc, it shows all the tasks in the booking great thanks to nested tables in display view.

However, one last thing I’m trying to achieve is give each staff member a “list of tasks” they have to complete rather than just bookings in the calendar. Its nice for them to see whats coming up - and also potentially helps them re-schedule themselves.

So this needs to be done in the task table. Try as I might, I cannot make a formula that can look up to see if there is a booking which contains the task ID (as part of a list in the column [tasks in stage]) and then match the person assigned to the booking to the task in the task table.

Ouch - that makes sense to me but is convoluted right?

Have a look at the following…

The task assigned to column in task table is where I want it to show which tasks have been assigned to a user through a booking. (The booking is where the assignment takes place!)
At the moment I can only get a formula working using the task ID columns - so only showing the task of the button I originally used to make the booking.

So for instance - you can see TID6 is associated with booking 11 which is assigned to me. However, by making that booking, I want the tasks table to also recognise that I’m associated with TID4 and TID5 as well. (The other tasks in the stage!). Getting the list together of those tasks was straight forward. However, distilling the data down for me at least hasn’t worked.

Any thoughts? Its late - I’m probably not expressing myself well - but its the best I can do right now.

Cheers, Brendan.