Report with Multiselect Contact Field
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
Answers
-
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))
-
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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives