Department Helper Column Rollup Multi Select

Dan Anzalone

The goal here is to create a helper column that with a formula will grab the department information from each task row, and then roll that up into the parent phase and project rows. However sometimes there is a department in the parent rows that are not in the task rows. So I need to be able to grab information from not only the child rows but also the current row as it rolls up. I also need the helper column information to be multi select.

The following formula does everything I need it to do except grab the parent row information as well.

=JOIN(DISTINCT(COLLECT(DESCENDANTS(Department@row), DESCENDANTS(Department@row), <>"")), CHAR(10))

Using the example data below the current formula will take dpt0, dpt1, and dp2 and put it into a helper column but at the project level the helper column wont include diff and i need it to include diff.

Could someone assist me with this? Thank you.

DKazatsky2

Hi @Dan Anzalone,

This is happening because your formula is only taking descendants and the Project row is not a descendant. You should see this behavior in the Phase rows as well.

Try this instead.

=JOIN(DISTINCT(COLLECT(DESCENDANTS(Department@row), DESCENDANTS(Department@row), <>"")), CHAR(10)) + Department@row

Hope this helps,

Dave

Dan Anzalone

That new formula with the + Department@row did give me the additional value I was looking for but it did "break" CHAR(10) as the values don't seem to be split anymore and the + does add values without checking for them to be distinct from the original set that is being pulled in before the +.