Report to include Parent row but only current user

Options
vipa2000
vipa2000 ✭✭✭✭
edited 03/28/22 in Formulas and Functions

I have a report with multiple assignees of a task. I set the report up to show just current user and it looked OK until I realised the Parent row was missing. Using this forum I introduced a 'Helper' column with this code =JOIN(CHILDREN([Assigned To]@row), ", ") it does a great job of pulling all the names in but I cannot say equals to current user when I am trying to set the filter up on the report. Thanks in advance, Paul


Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Options

    HI vipa2000,

    The filter of current user will work with a contact-list property column (Assignees in your case).

    JOIN function does not create a contact-list, but a string. That is why it could not help.

    Currently, there is no formula to create a contact.

    My idea is , in Assignees column, you can populate all the contacts of children rows in the parent row.

    Hope that helps.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

  • vipa2000
    vipa2000 ✭✭✭✭
    Options

    I have a report with multiple assignees of a task. I set the report up to show just current user and it looked OK until I realised the Parent row was missing. Using this forum I introduced a 'Helper' column with this code =JOIN(CHILDREN([Assigned To]@row), ", ") it does a great job of pulling all the names in but I cannot say equals to current user when I am trying to set the filter up on the report. Thanks in advance, Paul

    Hi Gia, how would I populate the parent row? Regards Paul

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    edited 03/28/22
    Options

    Got 2 ways to populate the parent row

    1. At the beginning of the project, you manually key in contacts of all the project's members.
    2. Use helper column and worflows to automate the populating

    First, add a new helper column "JOIN" with formula as below

    =JOIN(CHILDREN(Assignee@row), ",")

    then create an Assign People workflow to add Assignees to the parent row when a new member added to tasks. Remember to disable "Replace existing values..."

    then you can duplicate the above worflow for another member. It means if you got 10 members then you will create 10 worflows.


    That's the solution I came up with. Hope that helps.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

  • vipa2000
    vipa2000 ✭✭✭✭
    Options

    Hi Gia, great thanks. I can follow your thought processes for both solutions. It seems adding the people in manually for each parent maybe the easiest. I looked at solution 2 and tried to find a way to copy the parent helper cell across to the assignee column, but to no avail.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!