Create Automation based on Multi-Select Drop Down

Kate123
Kate123
edited 01/10/22 in Smartsheet Basics

Hello

I have a form where people can select what events they want to attend based on multi-select drop down option. Some of these events have a maximum capacity. I have set up formulas to count how many people sign up to each event. There are a couple of other things I want to do based on when maximum capacity is reached:

  • Set up an automation to alert me when 20 people have signed up to each event so I can change the thank you message on the form
  • Set up an automation to place any new registrations to a waitlist column

Any help with this would be appreciated!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/10/22
    • Set up an automation to alert me when 20 people have signed up to each event so I can change the thank you message on the form

    I might be inclined to set up a Metrics sheet to collect this info. One row for each event, use your count formula to populate the number of people signed up, and add automation rule to email you when the number is greater than 19.

    • Set up an automation to place any new registrations to a waitlist column

    In your main sheet, add a waitlist column and a total registrant column. In the total registrant column, use a COUNTIF to collect the total number or rows where the event name equals the event for this row: =COUNTIF(Event:Event, =Event@row), and make this a column formula.

    Create an Change Cell Value automation rule that is triggered when new rows are added. Add a condition block to the rule that if total registrant column is greater than or equal to 20, change the cell value in the waitlist column to whatever you want (you could use a checkbox, or just make it a "1" in the waitlist column, etc.) (You could also use the waitlist column to send an alert to the registrant that they're waitlisted.)

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thanks Jeff, how does this work if multiple options were selected in the one row? So on the registration form a person can select up to 4 options.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    In that case, it probably makes more sense to move the total registrant count to the metrics sheet. List each Event in a column, and in a Total column, use:

    =COUNTIF({Main Sheet Event Column}, CONTAINS(Event@row, @cell))

    In the metrics sheet, also create a Waitlisted column:

    =IF(Total@row >20, "Yes", "No")

    The tough part would be actually indicating which event the user registered for that is waitlisted... 🤔

    In the Main sheet Waitlist column, try this out - I'm not making any guarantees though, I've never done this before!

    =JOIN(COLLECT({Metrics Sheet Event Column}, {Metrics Sheet Waitlisted Column}, "Yes", {Metrics Sheet Event Column}, CONTAINS(Event@row, @cell)), ", ")

    This SHOULD collect any events from the Metrics sheet event column which are waitlisted and also appear in the multi-select Event field for this row, and list them separated by commas, like

    Training Event, Toga Party, Copy Machine Usage Seminar

    You could then use automation triggered when a row is added that looks for any value in the Waitlisted column and sends an alert to the registrant and/or the admin that the listed events are Waitlisted.

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • thank you so much!!