Beast Mode - Parent of Parent type join
I have a data set of project items at multiple levels of a project hierarchy: project > task > sub task. I'm trying to build a sort of rollup card that shows the items based on parent link relationships. i.e.:
- Project Parent = Self
- Task Parent = Project
- Sub Task Parent = Task
I'd like to be able to have a column that shows the top level project ID for a sub task but I don't actually have the project associated to a sub task in my data set. I only have the sub tasks parent task. Is it possible to use beast mode or another solution to get something like:
CASE WHEN `type`='subtask' THEN `subtask_parent.task_parent`
Where `subtask_parent.task_parent` is the subtask's parent's parent (the project)?
Just looking for options.