Contact List Rollup

I have a multi-select contact list column for assigning tasks to team members. I want the contact(s) assigned in the child row(s) to roll up to a master set of contacts for the parent row(s). However, when I try =JOIN(CHILDREN(),", "), I end up with a list that has blank items separated by columns and duplicated names if the same person is assigned more to more than one child task. It doesn't maintain the contact list format, so the parent rows don't show properly in the dashboard task summary reports we use to show the various user's tasks across multiple sheets/projects.

Is there a way to write a parent row formula that will select items from the drop down? Or is there a way to assign contacts from a child row to a parent row with an automation that I'm missing?

Find more posts tagged with

Contacts

parent row

Accepted answers

Paul Newcome

The formula issue will require a DISTINCT and COLLECT function.

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), @cell <> "")), ", ")

To get them as usable contacts though so that you can use features like filter by user, you will need to follow one of the options outlined in the thread below:

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column

All comments

Ipshita

Hello @cat_competent are you trying to send automated update requests for each task assigned to the contact list in that particular row? Because if you are, it's fairly easy to do so and I'll tell you how to set it up, but I would like to ask as to why do you want to roll up contacts from child rows to the parent row?

Thanks,

Ipshita

cat_competent

Hi @Ipshita,

Sending the update requests to the child row contacts is already set up in my automations and works fine, I realize I shouldn't have included that as it's not the crux of my problem. I've modified my original question.

The real problem I'm hitting is that we want to see the contacts in the parent rows so that the parent rows show up in the reports that are set up to show only tasks assigned to the current user. Each user has multiple sheets (one per project) with tasks, then they have a summary report that shows all their tasks across all projects. It is grouped by sheet name, but the parent rows won't show unless we manually set the users on them, and doing this has become tedious as task assignments change unexpectedly. I can set filters in the sheet that include parent rows, but the reports that service our dashboards don't work this way. I've some areas where we set the parent row contact and use a formula to assign it to the children that works fine, but the problem comes in in the parent rows that have different users assigned to each task in the child rows, and some child rows have 2 users assigned.

Paul Newcome

The formula issue will require a DISTINCT and COLLECT function.

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), @cell <> "")), ", ")

To get them as usable contacts though so that you can use features like filter by user, you will need to follow one of the options outlined in the thread below:

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column

cat_competent

@Paul Newcome Thank you! Unfortunately, my employer hasn't paid for any of those advanced options in the linked post, but this helps me understand the underlying structure and why it's not working the way I want it to. Now to dig around into what DataMesh/Bridge/Data Shuttle can do to see if I can justify the cost. :)

I also have other areas where the DISTINCT formula will be useful, so thank you for introducing it.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.