Concatenate/Roll Up Text in Children Tasks

cvarela

I want to rollup all of the updates made in the children tasks into the parent row. Is there a formula that can do that? Here is a screenshot of a parent row with some children tasks. The column with the updates is called 'Updates'. Can anyone help me create an IF statement that accomplishes this?

Georgie

Hi @cvarela,

To concatenate the values from the children rows and do this only where a row is a parent row, you could add a new column and create a column formula similar to: =IF([IsParent]@row = 1, JOIN(CHILDREN(Updates@row), ", "))

Check out the following links for more information on the functions used within the formula:

• IF Function
• JOIN Function
• CHILDREN Function
• Reference children, parents, and ancestors with hierarchy functions

Is that what you’re looking for?

Georgie

 

cvarela

Hi @Georgie, thank you. It is what I'm looking for, but the next question would be, how can we get it to not add "." for every child with a blank update? Here is what it looks like. I tested adding an update in each cell and the periods disappeared in favor of the text.

Georgie

Hi @cvarela,

We can remove the blank child row values by adding in the COLLECT Function, telling it to only collect children row values where the cell isn’t blank. So, where your original formula is:

• =IF([IsParent]@row = 1, JOIN(CHILDREN(Updates@row), ", "))

Your final formula would be:

• =IF(IsParent@row = 1, JOIN(COLLECT(CHILDREN(Updates@row), CHILDREN(Updates@row), <>""), ", "))

The <> operator means “not equal to” , so <>”” means “not equal to blank” - more information on operators you can use in Smartsheet formulas can be found here: Create and edit formulas in Smartsheet.

Hope that helps!

Georgie