Alert someone when $ value is approaching next increment of $50,000.00
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.
Best Answer
-
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!
Answers
-
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!
-
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!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!