Automation Notifications if 10+ rows change

Options
LConnor
LConnor ✭✭
edited 07/31/23 in Smartsheet Basics

Hi All!

Need some help with a specific Automation notification when a specific criteria is met.

The automation I would be after would look something like this:

Everyday 100+ lines of data are added to the sheet, this tracks a specific item. I am wanting an automation that triggers an email when 10+ of the same item for the same request is set to a specific status. (Every product even for the same request has a row created).

So for example: John Smith requires 12 lamps, once the status gets set to "transported", I would like an email to be sent to me notifying me of this. But Jane Doe only requires 6 lamps (under the 10 threshold), I do not want a notification sent to me.

Here is a mock up of what data roughly looks like currently in the sheet, each order will have an individual order number for identification.

In my mind, I am wondering if I can base the automation off of the individual order number to trigger to email notification, I am just unsure how, as the ID number is only created at the time of entering into the sheet, whereas from my limited capability with automation triggers I think I need to know this information in advance and set the ID number trigger up within the flow criteria, which I believe would defeat the purpose as a person would be required to constantly manage that trigger.

Open to all suggestions!

Thanks so much!

Answers

  • Itai
    Itai ✭✭✭✭✭✭
    Options

    Hey @LConnor

    My first thought would be to create a seperate sheet with all the options available and formulas that will COUNTIF the data from the main sheet, this way the automation will be on the Analysis sheet rather then the main one.

    I am following this post, intersting to see what people will come up with.

    Itai Perez

    Reporting and Project Manager

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Razetto
    Razetto ✭✭✭✭✭✭
    Options

    @LConnor What about adding columns, one for John (i.e. Count John Smith) and one for Jane, to checkmark whenever the criteria is met, like =IF(AND(Status@row = "transported", Product@row = "Lamp", name@row = "john S"), 1, 0). Create another sheet to track the count that will trigger the notification, =IF(COUNTIF([Count John Smith]:[Count John Smith], 1) > 10, "Ready", ""). I'm assuming that after being notified the status will be changed so you're not seeing the same entries.