For date filters in the query the easiest way is to set the same filters on list view and then copy the query in the script in the addEncodedQuery().
This works very smoothly and doing it yourself in the code is way more complex because of different timezone ServiceNow uses (like the system store the time in GMT, users timezone could be anything and also systems timezone could be different).
Best solution I could think for your requirement is as follows-
- Create a report with filters looking something like this-
- Create a Scheduled Email of Report record selecting this record. It is very important to make this report run with appropriate user (things like their access and timezone matters). Otherwise you might not see proper results.
To ensure that select required user (mostly a user from the same assignment group in condition) in the Run as field (If it is not on the form add it and select the user required). - Select "Omit if no records" and that will not trigger the report if there are not required filtered by the report.
- Now coming to condition where you want this to be triggere only if there are more than 14 records.
For this check "Conditional" and you will see a "Condition" field where you can write server side script.
In this script you need to check if there are more than 14 tickets active.
Your script should have the same filters as the report (use encoded query as specified above) and look something like this (please update the filters as required)-
var count = new GlideAggregate('incident'); count.addEncodedQuery("assignment_group=8a4dde73c6112278017a6a4baf547aa7^opened_atRELATIVEGE@minute@ago@45^stateIN1,2,3"); count.addAggregate('COUNT'); count.query(); var incidents = 0; if(count.next()) incidents = count.getAggregate('COUNT'); if (incidents > 14) answer = true; else answer = false; - Select the assignment group in the Groups field Scheduled Email of Report form.
-
Last but not the least, schedule it as you need.
The result of this configuretion will be a report triggered only if the could is greater than 14.
-Tanaji
Please mark the response correct/helpful if applicable
For date filters in the query the easiest way is to set the same filters on list view and then copy the query in the script in the addEncodedQuery().
This works very smoothly and doing it yourself in the code is way more complex because of different timezone ServiceNow uses (like the system store the time in GMT, users timezone could be anything and also systems timezone could be different).
Best solution I could think for your requirement is as follows-
- Create a report with filters looking something like this-
- Create a Scheduled Email of Report record selecting this record. It is very important to make this report run with appropriate user (things like their access and timezone matters). Otherwise you might not see proper results.
To ensure that select required user (mostly a user from the same assignment group in condition) in the Run as field (If it is not on the form add it and select the user required). - Select "Omit if no records" and that will not trigger the report if there are not required filtered by the report.
- Now coming to condition where you want this to be triggere only if there are more than 14 records.
For this check "Conditional" and you will see a "Condition" field where you can write server side script.
In this script you need to check if there are more than 14 tickets active.
Your script should have the same filters as the report (use encoded query as specified above) and look something like this (please update the filters as required)-
var count = new GlideAggregate('incident'); count.addEncodedQuery("assignment_group=8a4dde73c6112278017a6a4baf547aa7^opened_atRELATIVEGE@minute@ago@45^stateIN1,2,3"); count.addAggregate('COUNT'); count.query(); var incidents = 0; if(count.next()) incidents = count.getAggregate('COUNT'); if (incidents > 14) answer = true; else answer = false; - Select the assignment group in the Groups field Scheduled Email of Report form.
-
Last but not the least, schedule it as you need.
The result of this configuretion will be a report triggered only if the could is greater than 14.
-Tanaji
Please mark the response correct/helpful if applicable
