Join Collect w/ CHILDREN

Alex Hackford

Hi,

I am trying to pull task descriptions w/ a Join(Collect) formula but only for Children of phases w/in a project plan. Here's the formula I'm currently using...

=JOIN(COLLECT({Project Plan - EOL Kronodoc Range 1}, {Project Plan - EOL Kronodoc Range 2}, @cell < 1, {Project Plan - EOL Kronodoc Range 3}, @cell = [Phase #]@row, {Project Plan - EOL Kronodoc Range 4}, >=TODAY(7)), ", ")

This formula works as I'd like it to on the Progress to Date column - returning task descriptions for all tasks on the project plan...BUT it seems to only be returning task descriptions for parent cells in the Next Steps column. I tested this by adding a new task and out-denting it as far as possible and it was returned as expected.

I'm happy to add a CHILDREN designator in the formula but I'm not sure if that's the right path or if there's something else preventing child task descriptions from showing in this one column. Any help is greatly appreciated.

Project Plan - EOL Kronodoc Range 1 - Task description

Project Plan - EOL Kronodoc Range 2 - % complete

Project Plan - EOL Kronodoc Range 3 - Phase #

Project Plan - EOL Kronodoc Range 4 - Task End Date

Paul Newcome

Are they both the same exact formula?

Alex Hackford

Hey Paul - No, they are different formulas.

On the Progress to date column I'm using this formula:

=JOIN(COLLECT({Project Plan - EOL SiteFM4 Range 1}, {Project Plan - EOL SiteFM4 Range 2}, @cell = 1, {Project Plan - EOL SiteFM4 Range 3}, @cell = [Phase #]@row), CHAR(10))

This returns parents and children and I'd love for it to just return children. The ranges are the same as above just referencing a different project plan.

For the Next steps column I'm using this formula:

=JOIN(COLLECT({Project Plan - EOL SiteFM4 Range 1}, {Project Plan - EOL SiteFM4 Range 2}, @cell < 1, {Project Plan - EOL SiteFM4 Range 3}, @cell = [Phase #]@row, {Project Plan - EOL SiteFM4 Range 4}, >=TODAY(7)), ", ")

And it is only returning parent row descriptions.

Paul Newcome

The ranges are the same in both of those formulas with the exception of the additional range/criteria set for the dates in the second formula. I also notice that in the first you are looking for 100% complete and in the second you are looking for less than 100% complete.

Alex Hackford

Yes, that's correct.

The Progress to Date column needs to return task descriptions for completed tasks w/in each phase.

The Next Steps column needs to return task descriptions for open tasks w/in each phase that are due in the next 7 days.

As I'm writing this I think an easy solve would be to use the Item Type column we have to return only descriptions for rows designated as a "Task", which would exclude parent rows. I'll go try that and see if it works.

Alex Hackford

Adding the task criteria seems to have worked well on the Progress to Date column, but I'm still not getting the right descriptions returned on the Next Steps column.

Here's the task I'm missing as it appears on the project plan:

The row in yellow has a delivery date of next Tuesday and is less than 100% complete, is in phase 1, and is designated a task but is still not being returned with this forumula:

=JOIN(COLLECT({Project Plan - EOL Connectiv Range 5}, {Project Plan - EOL Connectiv Range 2}, "Task", {Project Plan - EOL Connectiv Range 4}, @cell < 1, {Project Plan - EOL Connectiv Range 3}, @cell = [Phase #]@row, {Project Plan - EOL Connectiv Range 1}, >=TODAY(7)), CHAR(10))

Again, the ranges are as follows:

Project Plan - EOL Connectiv Range 5 - Task description

Project Plan - EOL Connectiv Range 2 - Item Type (task, milestone, etc.)

Project Plan - EOL Connectiv Range 4 - % complete

Project Plan - EOL Connectiv Range 3 - Phase #

Project Plan - EOL Connectiv Range 1 - Task end date

Maybe I'm missing something in the formula?

Alex Hackford

Okay, looks like I misunderstood the logic of the TODAY(7) criteria. I changed the formula to read:

=JOIN(COLLECT({Project Plan - EOL Connectiv Range 5}, {Project Plan - EOL Connectiv Range 2}, "Task", {Project Plan - EOL Connectiv Range 4}, @cell < 1, {Project Plan - EOL Connectiv Range 3}, @cell = [Phase #]@row, {Project Plan - EOL Connectiv Range 1}, <=TODAY(7)), CHAR(10))

And it is now returning what I need.

Paul Newcome

Excellent! I'm glad you were able to get it sorted!