Filter( ) doesn't work on a list, retrieved from a related row

Hello community!

I am having a problem filtering rows, retrieved via linked rows in the same table.

First, some context.

I’m working on a project management system based on the Theory of Constraints. I have a project plan that has connected tasks (predecessor to successor), a critical chain (longest path of tasks) and so called “feeding chains” that are like branches, inflowing into critical chain.
I need feeding chains to bias to the right (late start), therefore I need a deadline for them to stick to.
In order to calculate the deadline for a feeding chain I need a date from the critical chain, that is not affected by the feeding chain, otherwise I get a cyclical reference.

See illustration.

I’ve written a formula running on a feeding chain rightmost task (task E) that:

  1. Looks into a successor of the task (task C),
  2. retrieves it’s predecessors (task B, E),
  3. filters that list to get a predecessor that is NOT on a feeding chain (I’m expecting it to be task B)
  4. retrieves it’s due date

Here’s the screenshot of the formula:

I’m having problems with step 3: filter doesn’t work. I successfully retrieve a list of tasks (B, E), but then I can’t filter it. I tried different operators: “!=”, “Contains()”, “Contains().Not()” etc., but none of them worked. I also tried using “Current value” in Filter( ) formula, it didn’t help.

Do you have any ides on what I’m doing wrong?

I notice Successor (manual) has the stacked icon, so I presume it is a lookup with multiple select enabled. that makes Successor.Predecessor a list of lists. maybe try these if possible: make Successor a lookup with multiple selection turned off and remove List() (as .Predecessor would now already be a list rather than a list of list), or use ListCombine() instead of List()

3 Likes

Could you provide a minimal example in a shared doc with playmode?

Sidenote: I like that illustration, how did you make it? :slight_smile:

1 Like

ListCombine() worked like a charm! It even fixed some other problems I faced trying to find a workaround, which was unexpected :smiley: .
Thank you so much!

My problem was solved (see above), thank you.

I used Miro.com to make the illustration. I’ve made it for my customer, actually, just adapted slightly for this post. It’s a great practice, btw: I draw many illustrations for my customer while brainstorming on the project, and then I use these pictures for the knowledge base I create as a deliverable.

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.