Report with Multiselect Contact Field

tovythomas · 06/11/24

I'm playing around with reports as I'm new with it and in our smartsheet we have a contact field for assignments. This field is a multivalue option as there is a manager who on certain items gets assigned as well for visibility.

However, when trying to create a report and filter on the number of rows per assigned person (in the contact field), it won't show the field in question as an option to group by.

I did test breaking it back to a single value field only and it does work.

Is there a limitation on grouping if multiple values ?

I had hoped it would just count those as a separate item in the grouping

For ex:

Person A (by themselves)- 6 tickets

Person B (by themselves) - 8 Tickets

Person A/B (together) - 10 tickets

David Tutwiler · 06/11/24

There is a limitation on multi-select fields and grouping in reports. I don't believe it is available.

Depending on how many people you have in the assignments, you could make Sheet Summaries on the sheet and pull those into a report. Instead of Row Report you would select Summary Report.

The formula for finding this would involve a COUNTIF or COUNTIFS and a CONTAINS formula. Something like:

=COUNTIF([Assigned To]:[Assigned To], CONTAINS("Person A", @cell))

tovythomas · 06/11/24

https://community.smartsheet.com/discussion/comment/440196#Comment_440196

Thanks @David Tutwiler , but how would i grab both people together. For example if John Doe/Jane Doe are both assigned to the same ticket (row), how would you do the combination formula.

I have a formula like this currently

=COUNTIFS([Billing Specialist Assigned]:[Billing Specialist Assigned], "John Doe") which gives me a value of 3 which is the number of rows they are in by themselves (not including multiselect).

If i add a + plus sign and another person ("Jane Doe") with another countifs, it will give me their total value together where they are assigned to a row by themselves. I'm trying to only pull when the 2 are together in that case.

I tried (assuming i did it right) your suggestion like this for even 1 value

=COUNTIF([Billing Specialist Assigned]:[Billing Specialist Assigned], CONTAINS("John Doe", @cell)) but it just gives me a value of 0 not unparseable as i would have thought if i messed up the formula.

David Tutwiler · 06/11/24

You'll need to wrap your Contains with an AND and put both contains formulas as the parameters of the AND function.

Since it is a contact column, you might have to use the email address in the Search For part of the Contains function.

Report with Multiselect Contact Field

Answers

Categories