Alert if a specific date is entered N times.

I am creating a "Covid Check-in' form for my office, where employees will use a form to check in with the date they are checking in.

I would like to send an alert to the office manager if more than a set number of entries are made for any specific date. I.E. if more than 10 people check in on 09/01/20 then an alert can be sent to the office manager's email.

i'm pretty new with Smartsheet, but familiar with coding and databases, so I was wondering if there is a built-in way to do this?

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    Use a cell to count the number of alerts using COUNTIF function. Then use a workflow to send out the email.

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Could anyone provide more tips on this - on how to do what Ramzi described?

  • Hello @Rosie Hallett

    I was able to create a solution that may work. To do this, you will want to add a "Helper" column, I used a checkbox, and used the following formula: =IF(COUNTIF([Check-In]:[Check-In], >=[Check-In]@row) > 10, 1, 0)

    This checks that if the date on the row shows more than 10 times within a Date column, it will check the checkbox. Please see my example below where I demonstrate 10 of the same Dates vs 9 of the same Dates.

    10 Dates:

    9 Dates:

    As you can see, when there are 10 entries of 19/09/20, it will flag the Checkbox. From here, you could create an Alert that will send when a Checkbox has been checked. With this, here are a few things to note when doing this:

    Let me know if you have any questions!

    Regards

    Sean

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sean Morgan Thanks for the "shout-out". One thing to note... The solution in the linked thread is primarily for date overlap across a range of dates (start and end for each row) and not necessarily for having multiple entries of the same date. While the COUNTIFS being used is good for including additional range/criteria sets, I don't think the date based portion of that solution would be a good fit.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!