Calendar App - Multi-Select Field with Filter

Hello all,


I'm looking for a way to be able to utilize a filter on multi-select so that it incorporates the relevant datapoints. However when we do the grouping, it effectively creates grouping by groups and effectively miscategorizes the information.

Example: We are using the calendar app for a launch calendar that we need to filter by a Geo Field where we have a multi-select filter with all countries listed. If I have two rows that have three multi-select values (US, CA, UK), and create a filter where GEO has any of US, neither row will be displayed.


Is there any suggestion on how I can fix the above?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@Robert Campbell

    Would you be able to post screen captures of what you're describing? The Calendar configuration, the full Calendar display, the filter you're creating, and the resulting Calendar display when you apply that filter (but please block out any sensitive data!)

    Since you're using a Multi-Select column, the Calendar will read each cell's possible combinations as unique scenarios. This means that if you Group by this column, one Group will be "CA", one group will be "US & CA", etc. However, you should still be able to apply a filter that sees if the cellhas any of这些values, as you mentioned.

    This should then displayany rowwhere "CA" is selected, whether that's on its own or with other values.

    See my example with slightly different values:

    Screen Shot 2021-05-25 at 12.16.34 PM.png

    Seethe Calendar App Help Articlefor more information.

    If this isn't what you're seeing, it would be helpful to know more about your set-up. Thank you!

    Genevieve

  • Sure! Here is my sandbox data set to define the problem:

    fig1.PNG

    Here is my calendar:

    fig2.PNG

    And here are my filters:

    fig3.PNG

    My expectation is with Multi-select, I shuold be able to select UK and have the pivot on Launch GEO also account for those where UK exists in the larger grouping - however, this is not happening, and it's removing those where UK is part of a larger group.

    Any ideas?

  • image.png

    Here is the result of applying that filter. As you'll see, it excluded those bottom four who have UK as part of their grouping.

  • Somehow, it fixed itself! We are good.


    Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@Robert Campbell

    I wasn't able to replicate what you were seeing so I'm glad to hear that! Thanks for letting me know.

    If this happens again, please reach out to Support as this isn't expected behaviour.

    Cheers,

    Genevieve

  • @Robert Campbell@Genevieve P.-

    I am having this same issue with one of my multi-selects but not the other. Help!


    Working field: Region - Multi select with 6 options

    I can create a filter looking for one of these options and it provides back ALL the rows that contain it

    Smartsheet_Region working MS.png

    It will return an item that has both AMER and WW CMCL AMER:

    Smartsheet_Region working AMER w multiple.png


    Broken field: Cohort - Multi select with all permutations in the filter dropdown.

    Smartsheet_cohort不工作string.png

    When I input DevOps in the filter...it does NOT pick up all the others that have more than one.

    Smartsheet_cohort NOT working ONLY DevOps Selected.png


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@J Chalk

    I'm unable to replicate what you're seeing, my multi-select columns and filters work as expected.

    Is it possible that the filter works differently than you want it to? Keep in mind it's an AND statement between the two columns, so Email Type will need to have one of the values selected AND Cohorts will have to have "DevOps" as well. Were you looking to see an OR statement instead?

    Cheers,

    Genevieve

You would need to use a Move Row or Copy Row automation.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":474,"urlcode":"data-shuttle","name":"Data Shuttle"}]},{"discussionID":106742,"type":"question","name":"Conditional Formatting","excerpt":"Hi, I need to apply conditional formatting based on another row in the sheet. Higher it should be highlighted in red, equal to or below highlighted in green. It would be easy to just use the target figures in the conditional formatting rules but I need to be able to change it frequently without changing all the formatting…","categoryID":321,"dateInserted":"2023-06-21T22:41:30+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T15:15:27+00:00","insertUserID":160222,"insertUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":160222,"lastUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3374836617,"url":"https:\/\/community.smartsheet.com\/discussion\/106742\/conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106742\/conditional-formatting","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":106742,"commentID":381752,"name":"Re: Conditional Formatting","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381752#Comment_381752","dateInserted":"2023-06-22T15:15:27+00:00","insertUserID":160222,"insertUser":{"userID":160222,"name":"WillH","url":"https:\/\/community.smartsheet.com\/profile\/WillH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T15:13:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ARIIND3W8G3C\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T15:15:35+00:00","dateAnswered":"2023-06-22T03:52:17+00:00","acceptedAnswers":[{"commentID":381675,"body":"

@isabonita<\/a> is on the right track. This is kind of an involved answer, but once you figure out the logic, it isn't that bad.<\/p>

You cannot reference specific cells in the conditional format in a manner that would make this easy. You cannot, for instance, say IF this specific cell is greater than that cell, apply this format. You can do it for the same row, but not always referencing the same cell. We get around this by inserting a \"helper\" column for each condition we want to check. The checkbox format works great. In the conditional format rule, you can simply say IF THIS cell is checked, apply the format to THAT cell. <\/p>

To set the checkbox to checked\/unchecked, we use this column formula: =IF(PDP@row > PDP#, 1, 0)<\/p>

But what is PDP#? This is another issue we have to work around. You cannot reference the first cell of a column in a column formula, you can only reference cells in the same row. To work around this, we add fields into the sheet summary. [PDP]# references the PDP field in the sheet summary. In order to make updating these values easier, we set the value of this field equal to the first cell in the PDP column using =PDP1<\/p>

After creating all of the helper columns, setting up the column formulas for each to determine which ones are checked, creating all of the necessary fields in the sheet summary, and setting those equal to the first cells in each of the respective columns within the sheet, we can set up the conditional formatting.<\/p>

Check out the image below to get an idea of how to setup the formats. Unfortunately, we have one more workaround to setup at this stage. Since the formatting will apply to every row in the sheet, it will apply to the first row as well and all of those cells will be green. To get around this, add the first format pictured below as the first one in your list. For the background color, you must select \"white\" in order to avoid the highlight. If you set it to \"no background\", you will get an error because Smartsheet does not see \"no background\" as a format.<\/p>

After everything is setup and running, you should be able to update the cells in your target row and things will work themselves out automatically.<\/p>


<\/p>


<\/p>

\n
\n \n \"Condition.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

I hope this helps, and I really hope it makes sense.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]},{"discussionID":106711,"type":"question","name":"Is there a glitch in the alert automation when trying to add the double curly brackets?","excerpt":"When creating an alert based on a row being added or changed, I can set the alert to go out hourly, daily, weekly. However, when I add the fields with the double curly brackets, it will not allow me to use the hourly, daily, weekly feature. Is this a glitch in Smartsheet? Has anyone else noticed this?","categoryID":321,"dateInserted":"2023-06-21T17:08:36+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T16:21:48+00:00","insertUserID":158955,"insertUser":{"userID":158955,"name":"Andrea_Thompson","url":"https:\/\/community.smartsheet.com\/profile\/Andrea_Thompson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AmEthyIFMno!3R8u_8p4Dvo!yYe8n_1IW84","dateLastActive":"2023-06-22T16:22:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":49,"score":null,"hot":3374821224,"url":"https:\/\/community.smartsheet.com\/discussion\/106711\/is-there-a-glitch-in-the-alert-automation-when-trying-to-add-the-double-curly-brackets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106711\/is-there-a-glitch-in-the-alert-automation-when-trying-to-add-the-double-curly-brackets","format":"Rich","lastPost":{"discussionID":106711,"commentID":381771,"name":"Re: Is there a glitch in the alert automation when trying to add the double curly brackets?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381771#Comment_381771","dateInserted":"2023-06-22T16:21:48+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-22T19:34:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T17:28:51+00:00","dateAnswered":"2023-06-21T19:34:19+00:00","acceptedAnswers":[{"commentID":381597,"body":"

Sorry. I misspoke. You are correct that it is not a time based trigger, but it is a time based sending of the alert. If you have 10 rows that changed within that 1 hour period, then it will send all 10 of those rows within the same alert. Since it is pulling 10 rows, it does not know which row to pull the {{Placeholder}} from.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=341&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":5398,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Using Smartsheet