Alert someone when $ value is approaching next increment of $50,000.00

PeggyLang

I am creating a 'Project Tracker'; 1 location per row with many different columns one of the columns being '$ Spend to Date'.

I need to notify program manager when '$ Spend to Date' is nearing $50,000.00, then again when '$ Spend to Date' is nearing $100,000.00, then again . . . $150,000.00, then again . . . until we reach the cap of $5,000,000.00.

I'm wondering what is the best way to do this.

Toufong Vang

HI, @PeggyLang,

Here's one approach that assumes your threshold is $5,000. In other words, "send an alert when spending is less than $5,000 from the next increment."

This approach uses two column helpers. One column stores the next/nearest increment--Increment. The other column indicates whether or not to send the alert--Alert. 

1) In the Increment helper, use MROUND() to return the nearest multiple of 50,000.

MROUND([$ Spend to Date]@row, 50000)

2) In the Alert column use the formula below to set the alert flag--"where '1' means 'yes, send alert'".

IFERROR(IF((50000 - MOD([$ Spend to Date]@row, 50000)) <= 5000, 1, 0), "")

3) Configure a workflow automation to run at your desired time. Set the criterion to, Where Alert is equal to 1.

4) Use the placeholder {{Increment}} in your message to show the $ increment they are nearing.

Hope this is helpful!

Toufong Vang

HI, @PeggyLang,

Here's one approach that assumes your threshold is $5,000. In other words, "send an alert when spending is less than $5,000 from the next increment."

This approach uses two column helpers. One column stores the next/nearest increment--Increment. The other column indicates whether or not to send the alert--Alert. 

1) In the Increment helper, use MROUND() to return the nearest multiple of 50,000.

MROUND([$ Spend to Date]@row, 50000)

2) In the Alert column use the formula below to set the alert flag--"where '1' means 'yes, send alert'".

IFERROR(IF((50000 - MOD([$ Spend to Date]@row, 50000)) <= 5000, 1, 0), "")

3) Configure a workflow automation to run at your desired time. Set the criterion to, Where Alert is equal to 1.

4) Use the placeholder {{Increment}} in your message to show the $ increment they are nearing.

Hope this is helpful!

PeggyLang

@Toufong Vang

I worked on this prior to receiving your response and I built exactly (minus the 'rounding' function) what you had suggested. Works very nicely.

Thank you!!!