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?

Find more posts tagged with

Accepted answers

All comments

Ramzi K

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

Rosie Hallett

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

Sean Morgan

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:

You will want to enter this formula into the first date entry of every Form Submission. You can also drag this into every Checkbox cell.
You may want to customize the Alert that you send so that id does not show the Checkbox section within the Alert. See more on this here: https://help.smartsheet.com/articles/2479256-customize-the-content-of-your-alerts-and-requests
If you want to add more conditions, you will want to change the COUNTIF to a COUNTIFS. See this following article for a great formula created by one of your Community Members @Paul Newcome : https://community.smartsheet.com/discussion/comment/260242
When we refer to a Helper Column, this is a Column that is used within other Formulas to trigger or present a certain result E.G Checkbox for using within an Alert.

Let me know if you have any questions!

Regards

Sean

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.