Last updated on August 24, 2023.
There are situations where you want to limit NetSuite Case Alert emails to business hours. While that is not possible with native Case Alert options, it can be done using cleverly crafted search formulas. In this article, I describe how to limit a saved search to show results only within specific times of the day and on specific days of the week in order to control exactly when Case Alerts fire. Once understood, this technique could potentially be applied to other scenarios.
Context
The Case Alerts feature in NetSuite (Setup -> Support -> Case Alerts) is a useful tool that can be used instead of saved search email alerts to notify users of support cases that fall within certain criteria that is defined within a saved search. A common use case where a Case Alert would be used rather than a saved search email alert would be for support cases that are unassigned for a specific duration. Saved search email alerts can be triggered on create or update actions, or through a fixed schedule such as once daily. However, they are limited in being able to trigger in real time once a case hits a certain amount of hours of being unassigned, which is something that Case Alerts can handle.
Although the Case Alerts feature was introduced in NetSuite Release 2017.1 and has been used by many customers since then due to its flexibility, it is (still) in beta which might explain why some features, like the one I sought, are missing.
Recently, I was tasked with setting up Case Alerts for specific types of cases to be sent to users if the cases were unassigned for a certain duration. The tricky part was that my task involved making the alerts to send only Monday through Friday between specific time ranges of the day, while including any unassigned cases that might have been created during off hours, such as nights and weekends. The end users did not want to receive alerts for these particular cases during the off hours that fell within the criteria until the start of the next business day.
Since you cannot set an email schedule for these alerts on the Case Alert records themselves, I knew I had to find a solution within the saved search that would drive the alerts, and more specifically, within the criteria of the saved search. As mentioned, the key constraint was that when a case matched the criteria of being unassigned for a certain duration during off hours, we did not want the alert to be sent out until the start of the next business day.
Solution
Create a new Case Saved Search (Lists -> Search -> Saved Searches -> New -> Case). In the criteria of the search set the filters as illustrated below. SuiteAnswers ID 99915 “Setting up a Case Alert on Support Cases” walks you through the basics of how to create a saved search for Case Alerts.
Now, you must add a few Formula (Numeric) fields to predefine the time ranges you want the results to show in the search, which will be the time ranges that the Case Alerts will be sending alerts out. In these examples, I am choosing the time ranges of 10am to 5pm, Monday through Friday.
- Formula (Numeric)
- Formula: {now} – TRUNC({now})
- Greater than or Equal to 0.416667
- Formula (Numeric)
- Formula: {now} – TRUNC({now})
- Less than or Equal to 0.708333
- Formula (Numeric)
- Formula: CASE WHEN TO_CHAR({now},’DY’)=’SUN’ OR TO_CHAR({now},’DY’)=’SAT’ THEN 0 ELSE 1 END
- Equal to 1
After you add these Formula Fields, the criteria should look like the below example:
By using these formulas, you can tell the search to only show results if the current time is between 10am and 5pm, Monday through Friday.
TRUNC({now}) truncates the current timestamp to 12am. Subtracting the difference between {now} and TRUNC({now}) with a Formula (Numeric) field will translate into a decimal number which represents the fraction of the current day that has already gone by. For example, if it is 2pm right now, we are .58333 complete with the current day.
In the first formula, .416667 translates to 10am since 10 hours divided by the 24 hours in a day equals .416667. Similarly, .708333 in the second formula translates to 5pm because 5pm is 17 hours after the start of day (12am). Thus, 17/24 gives us 0.708333.
The third formula is pretty straight forward and is telling the search to only show results if the current day is not Saturday or Sunday.
For the Results of the search, you can add whatever fields you would find useful. See SuiteAnswer ID 99916 “The Standard Email Alert Email Template” for information on how to customize what’s in the body of the Case Alert email.
Now you can save the saved search, and add it to your new Case Alert. For more information on setting up Case Alerts, refer to the official documentation.
Conclusion
NetSuite does not have a notion of business hours. Nevertheless, with some clever formulas, we can mimic that behavior and control the delivery window of Case Alerts. This is exciting! This opens the door to interesting use cases. Using the approach I described in this article, a user can make saved searches show results during specific time ranges of the day which in turn causes Case Alert emails to be sent only during those time ranges. For the use case described in this article where Case Alerts were to be sent for cases unassigned for a certain duration, this approach ensured that even if the cases were created during the off hours and would have matched the criteria of being unassigned for the predefined duration, the alerts for them were not sent out until the next business day during business hours.
NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, learn how you can become an author/collaborator here.
Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!
This is a great article to have bookmarked Chloë!
I like the use of the TO_CHAR format mask ‘DY’ (which returns the day of week abbreviation “MON”, “TUE”, etc) instead of using the format mask ‘D’, which returns 1-7, but is dependent on the account’s “First Day of Week” setting.
I typically will use these two criteria for alerts during business hours (both Formula(Numeric), and equal to 1):
CASE WHEN ({now}-TRUNC({now}))*24 BETWEEN 9 AND 17 THEN 1 END
DECODE(TO_CHAR({now},’DY’),’SAT’,0,’SUN’,0,1)
One thing to consider if you have recipients in multiple time zones is that, IIRC, {now} and {today} are in relation to the time zone of the owner of the saved search.