Trigger single email notification on condition

[farci]

Hi Team,

I am trying to trigger single emails notification based on condition. My record counts in the table are dynamic. However I know the way to count them.

Below is the scenario how I want the system to behave

Suppose if I have 15 records in the table and they all belong to same supplier and reporting/service month. and Once the status of few records changes to 'Ready for Validation' I want to send a single notification and once all 15 record status changes to 'Ready for Validation'. then I want to trigger another notification.

The problem with my below script is, it sends out email notification for all the records instead of single notification. Basically i want two notifications one when the record count is less than 15 and one when record count is equal to 15.

This is a after Business rule.

(function executeRule(current, previous /*null when async*/) {

(function(){

var gr = new GlideAggregate('x_isgi_isg_governa_pm_sl_s');

gr.addEncodedQuery('sp_name=Supplier^serv_monONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()');

gr.addAggregate('COUNT','sp_name');

gr.setGroup(false);

gr.query();

while(gr.next()){

var gr1 = gr.getAggregate('COUNT','sp_name');

gs.addInfoMessage(gr1 + 'GlideAgrregate Count');

var gr2 = new GlideRecord('x_isgi_isg_governa_pm_sl_s');

gr2.addEncodedQuery('sp_name=Supplier^serv_monONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()^s_wf=6^sys_updated_onRELATIVEGE@hour@ago@1');

gr2.query();

if(gr2.getRowCount() > 0 && gr2.getRowCount() < gr1){

gs.eventQueue("x_isgi_isg_governa.Par_Submit _ISG_ACN",current);

gs.addInfoMessage(gr2.getRowCount() + " getrow count");

}

else if (gr1 == gr2.getRowCount()){

gs.addInfoMessage('full');

gs.eventQueue("x_isgi_isg_governa.Full_Submit _ISG_ACN",current);

}

}

})();

})(current, previous);

Awaiting your quick response.

Regards,

Narmi

[Gaurav Bajaj]

Hi,

I am afraid you need to set flag in the table itself in a new column.( say u_flag)

Suppose if I have 15 records in the table and they all belong to same supplier and reporting/service month. and Once the status of few records changes to 'Ready for Validation' I want to send a single notification and once all 15 record status changes to 'Ready for Validation'. then I want to trigger another notification.

My Concern is when exactly you want to send the first notification? We need to define the limit for few records.

The way i have suggested will work for the 1st record and then at last when all 15 record's status has been changed.

(function executeRule(current, previous /*null when async*/) {


(function(){


var gr = new GlideAggregate('x_isgi_isg_governa_pm_sl_s');
gr.addEncodedQuery('sp_name=Supplier^serv_monONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()');
gr.addAggregate('COUNT','sp_name');
gr.setGroup(false);
gr.query();


while(gr.next()){
var gr1 = gr.getAggregate('COUNT','sp_name');



var gr2 = new GlideRecord('x_isgi_isg_governa_pm_sl_s');
gr2.addEncodedQuery('sp_name=Supplier^serv_monONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()^s_wf=6^sys_updated_onRELATIVEGE@hour@ago@1');
gr2.query();


if(current.u_flag==false){
if (gr1 < gr2.getRowCount()){
ifif (gr1 == gr2.getRowCount()){ (gr1 == gr2.getRowCount()){
gs.eventQueue("x_isgi_isg_governa.Par_Submit _ISG_ACN",current);
current.u_flag=true;
current.setWorkflow(false);
current.update();
}
}

if (gr1 == gr2.getRowCount()){
gs.addInfoMessage('full');
gs.eventQueue("x_isgi_isg_governa.Full_Submit _ISG_ACN",current);
}
}

})();

[farci]

Hi Gaurav,

The script is not working.

In terms of when exactly the first notification should go? Suppose if a user submits 5 records today then one notification should go saying partially submitted and if again the next day he submits 6 records again a partial submission notification should go and on the third day when he submits the remaining records (4 records) full submission notification should go.

There is no schedule as to when a user will submit the records therefore, I cannot utilize schedule jobs/scripts to run the notification.

Below is the modified scripts per your feedback.

(function executeRule(current, previous /*null when async*/) {

(function(){

var gr = new GlideAggregate('x_isgi_isg_governa_pm_sl_s');

gr.addEncodedQuery('sp_name=Supplier^serv_monONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()');

gr.addAggregate('COUNT','sp_name');

gr.setGroup(false);

gr.query();

while(gr.next()){

var gr1 = gr.getAggregate('COUNT','sp_name');

var gr2 = new GlideRecord('x_isgi_isg_governa_pm_sl_s');

gr2.addEncodedQuery('sp_name=Supplier^serv_monONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()^s_wf=6^sys_updated_onRELATIVEGE@hour@ago@1');

gr2.query();

if(current.u_flag==false){

if(gr2.getRowCount() > 0 && gr2.getRowCount() < gr1 ){

gs.eventQueue("x_isgi_isg_governa.Par_Submit_ISG_ACN",current);

current.u_flag=true;

current.setWorkflow(false);

current.update();

}

}

if (gr1 == gr2.getRowCount()){

gs.addInfoMessage('full');

gs.eventQueue("x_isgi_isg_governa.Full_Submit_ISG_ACN",current);

}

}

})();

})(current, previous);

Regards,

Narmi

[Fabian Kunzke]

Hello,

I would like to question your approach to the use case. The scenario you are indicating suggests, that a user will always open a fix amount of tickets (e.g. 15). If this ever changes, you will have to change your code once again. A much cleaner approach would be to have a parent record, in which it is defined of how many childs are expected (e.g. 15). There a cycle of the "partcial submitted" notifications can be defined, too (e.g. every day 16:00).

This way you can use the oob notification function by defining one, which is send when the definied time is met and some childs have been added, and another one, which is send after the maximum amount of defined childs is added. This way you won't need to change your scripting depending on the number of maximium childs opened by a customer.

Greetings

Fabian

[farci]

Hi Fabian,

Thank you for your response. There is no scope of defining parent and child now. The table and contents are already setup now. In terms of counting the number of records, I am using glide aggregate wherein, I get to know how many records are there for each supplier for different service months. Ideally the record counts is dynamic which is achieved by glide aggregate.

The next piece of coding is to find out among these records how many of them are having a status as 'Ready for Validation' which happens only when a user updates the records. The user can do this by using excel import or by opening a record in a form view and updating it.

I have a UI Action button both in list and form view, which says 'Submit for Validation' and once a user hits the button the record status changes to 'Ready for Validation'.

Along with this condition I am specifying a time frame that those records that are updated before 1 hours should be counted and if the count of records is less the the glide aggregate count for then records then it has to trigger partial submission notification and if it matches then full submission notification.

However the notification is triggered for all the records instead of two single notification.

Regards

Narmi

[Fabian Kunzke · Accepted solution]

Hello Narmi,

Well, you could still create a parent table and add a new relation between the both. This way you won't need to modify the original table. I doubt, that this will be a simpler solution, but a cleaner one for sure.

But let's focus on what you are trying to archieve. Essentially your issue lies within the way your solution is handled: A business rule triggers on every record. Therefore every single record will trigger a notification (or let's say a notification action). This behaiviour will persist for business rules, since they trigger per record, not per table.

You could solve the issue by setting the business rules up in a way that they trigger a notification action, not the notification itself. This action (e.g. an explicit call of a script) is then handled by a script, which does the calculation/aggregation on the scope of the table. This means, that the calculation of the notificaiton would be done outside the record scope.

The other option would be to mark a record as soon as the initial notification has been send. This way the scope can be kept on the record level.

Again, i think your current way is neither ideal nor future proof. I would suggest to either set up a parent child relatioinship or to calculate the criterias outside of the record scope.

Greetings

Fabian