Would like to create a filter that hides completed tasks BUT only when parent row is checked

Hi there,

Ultimately I would prefer that tasks that are completed are archived, however in lieu of functionality, I am looking to create a filter view where tasks marked as "done" are hidden.

The problem is right now the filter hides ALL tasks marked done and I would prefer it hide ONLY the parent and its subtasks.  

Please let me know if this is possible, thank you. 

Tags:

Best Answer

  • ALTaylor
    ALTaylor ✭✭
    Answer ✓

    I don't know if I can close my own discussion question but please note this is the response I received from Smartsheet:


    Thanks for contacting Smartsheet Support. I understand that you wanted to build a filter for completed rows. I can certainly help you with this issue today.


    In reviewing the question, we regrettably do not have a way to create a filter to hide the completed rows on the sheet. However, you can use an automation to move completed rows to another sheet. You can setup the conditions so that when a row of data is completed, it automatically moves to the source sheet you point it to. I have linked an article below on this.


    Automatically Move or Copy Rows Between Sheets

Answers

  • ALTaylor
    ALTaylor ✭✭
    Answer ✓

    I don't know if I can close my own discussion question but please note this is the response I received from Smartsheet:


    Thanks for contacting Smartsheet Support. I understand that you wanted to build a filter for completed rows. I can certainly help you with this issue today.


    In reviewing the question, we regrettably do not have a way to create a filter to hide the completed rows on the sheet. However, you can use an automation to move completed rows to another sheet. You can setup the conditions so that when a row of data is completed, it automatically moves to the source sheet you point it to. I have linked an article below on this.


    Automatically Move or Copy Rows Between Sheets

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭

    Hi @ALTaylor

    To make sure I understand your requirement, you only want to hide the parent and sub-tasks when all are done? So if for example you have a parent, and 3 childen, and 2 child tasks are complete they must all remain? Only when all 3 tasks are complete, then the 4 rows must be hidden?

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    edited 11/02/21

    Not at all elegant and will require that the cell formulas are carefully updated: The helper columns "Hide All" and "Is Complete" can be hidden.


    Column formula would be preferred but I don't have the expertise yet for that, maybe someone like @Paul Newcome can assist

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To use column formulas, I would go with something like this...


    Text/Number column - "Helper":

    =IF(COUNT(CHILDREN([Task Name]@row)) = 0, PARENT([Task Name]@row), [Task Name]@row)


    Text/Number column - "Group Complete":

    IF(COUNTIFS(Helper:Helper, @cell = Helper@row) = COUNTIFS(Helper:Helper, @cell = Helper@row, [% Complete]:[% Complete], @cell = 1), 1)


    .


    Basically we first pull the parent task name into a column and then the second formula is saying to check the box if the count of all rows containing the same text in the helper column is the same as the count of all rows containing the same text in the helper column that are 100% complete (meaning the entire group of parent and child rows is complete).


    So no boxes will be checked until all child rows and the parent row are all marked as 100%. Then they all get checked at the same time.


    From there you can filter out the rows that are checked, pull a report based on the check, or run your copy/move row (not advised) automation based on the checkbox.