How to solve relationship with circular reference?

[tcpr]

Hello all!

I am struggling to get a solution for the following problem. Any help will be hugely appreciated!

I have two tables to work with:

Table 1: contains a list of items. One column is the item unique ID, and the other columns are information about the item.

Table 2: contains basically two columns: Item ID (for reference) and Item ID child.

So, if I filter Table 2 by an ID, I get all its children as a result.

For example, let's say the items are: "Projects", "Tasks," and "Subtasks".

ID #1 is a project. It has #2, #3, and #4 as children. #2 and #3 are tasks, and #4 is a subtask.

Task #2 has subtask #5 as a child.

My goal is to be able to summarize tasks and subtasks by projects and their statuses.

I think I have to do something like: Get one Table 1[ID] >>> Table 2 [ID reference].

Then get all Table 2 [child ID] >>> Table 1 [ID]

Again, get all Table 1 [ID], check its child, and return another list of Table 1 [ID]s until there are no more children left.

How can I achieve that?

[dufoq3]

Hi @tcpr, provide sample data as table and expected result based on sample data.

[tcpr]

Table 1:

Table 2:

One expected result:

(this is as far as I where I was able to get by duplicating the tables)

On the list, one project is selected, and on the pizza, all tasks and subtasks directly linked to this project are filtered.
However, I would also like to retrieve all subtasks linked to this project (including the one directly linked and any others indirectly linked through the 30 tasks). Furthermore, if I select the "tasks" on this pizza, I would like to be able to filter all its subtasks on a different pizza or drill through this one.