Send a Notification Based on field updating

varma2
Mega Sage

Hi All,

 

Need to trigger a notification in a Pm_project table. we have filed called Percentage complete  in Pm_project  table and we have form section which is Status report under this they will create record in project_status table,

My trigger condition IS. if below condition met notification should trigger. 

 

Status Report not submitted in the last 7 days
Percent Complete not updated in the last 7 days

varma2_0-1744024679054.png

varma2_1-1744024731931.png

Please suggest,

Thank you all. 

2 ACCEPTED SOLUTIONS

Ankur Bawiskar
Tera Patron
Tera Patron

@varma2 

is that table "pm_project" audited?

If yes then you can use advanced notification condition and use script, I assume your notification is on pm_project table

1) query sys_audit table and see when was the last update to Percent complete

2) in the same advanced notification condition script, query status report table for this Project and see if any record was created or not in last 7 days

based on that set answer=true to send email

something like this but please enhance

 var projectId = current.sys_id;
 var sevenDaysAgo = new GlideDateTime();
 sevenDaysAgo.addDaysUTC(-7); // Get the date 7 days ago

 // Check if "Percent Complete" was updated in the last 7 days
 var percentCompleteUpdated = false;
 var auditGR = new GlideRecord('sys_audit');
 auditGR.addQuery('tablename', 'pm_project'); // Table name
 auditGR.addQuery('fieldname', 'percent_complete'); // Field name
 auditGR.addQuery('documentkey', projectId); // Project sys_id
 auditGR.addQuery('sys_created_on', '>=', sevenDaysAgo); // Changes within last 7 days
 auditGR.query();
 if (auditGR.hasNext()) {
     percentCompleteUpdated = true; // Found an update for "Percent Complete"
 }

 // Check if a status report was submitted in the last 7 days
 var statusReportSubmitted = false;
 var statusReportGR = new GlideRecord('project_status');
 statusReportGR.addQuery('project', projectId); // Filter by current project
 statusReportGR.addQuery('sys_created_on', '>=', sevenDaysAgo); // Submitted within last 7 days
 statusReportGR.query();
 if (statusReportGR.hasNext()) {
     statusReportSubmitted = true; // Found a status report submission
 }

 // Trigger notification if both conditions are not met
 if (!percentCompleteUpdated && !statusReportSubmitted) {
     answer = true;
 } else {
     answer = false;
 }

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

View solution in original post

Thanku @Ankur Bawiskar ,

 

Can we send weekly once the notification using schedule job with below script.

 

var projectId = current.sys_id;
 var sevenDaysAgo = new GlideDateTime();
 sevenDaysAgo.addDaysUTC(-7); // Get the date 7 days ago

 // Check if "Percent Complete" was updated in the last 7 days
 var percentCompleteUpdated = false;
 var auditGR = new GlideRecord('sys_audit');
 auditGR.addQuery('tablename', 'pm_project'); // Table name
 auditGR.addQuery('fieldname', 'percent_complete'); // Field name
 auditGR.addQuery('documentkey', projectId); // Project sys_id
 auditGR.addQuery('sys_created_on', '>=', sevenDaysAgo); // Changes within last 7 days
 auditGR.query();
 if (auditGR.hasNext()) {
     percentCompleteUpdated = true; // Found an update for "Percent Complete"
 }

 // Check if a status report was submitted in the last 7 days
 var statusReportSubmitted = false;
 var statusReportGR = new GlideRecord('project_status');
 statusReportGR.addQuery('project', projectId); // Filter by current project
 statusReportGR.addQuery('sys_created_on', '>=', sevenDaysAgo); // Submitted within last 7 days
 statusReportGR.query();
 if (statusReportGR.hasNext()) {
     statusReportSubmitted = true; // Found a status report submission
 }

 // Trigger notification if both conditions are not met
 if (!percentCompleteUpdated && !statusReportSubmitted) {
     answer = true;
 } else {
     answer = false;
 }

 

Please suggest.

 

Thank you

View solution in original post

10 REPLIES 10

@varma2 

Did I answer your original question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

The discussion can continue on answered thread as well.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

Thank you @Ankur Bawiskar ,

 

could you please please provide the solution, the notification will run weekly once. 

 

Thank you

@varma2 

It seems you marked your own response as correct.

would you mind marking my response as correct if that helped you?

 

AnkurBawiskar_0-1744112887159.png

 

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

@Ankur Bawiskar  Yes, Accepted your solution.

 

Please Suggest.

 

Thank you.

@varma2 

yes it's possible

you can use same script and enhance it further to iterate for all records

Create event and notification and trigger that event from script

var projectRec = new GlideRecord('pm_project');
projectRec.query();
while (projectRec.next()) {

    var projectId = projectRec.sys_id;
    var sevenDaysAgo = new GlideDateTime();
    sevenDaysAgo.addDaysUTC(-7); // Get the date 7 days ago

    // Check if "Percent Complete" was updated in the last 7 days
    var percentCompleteUpdated = false;
    var auditGR = new GlideRecord('sys_audit');
    auditGR.addQuery('tablename', 'pm_project'); // Table name
    auditGR.addQuery('fieldname', 'percent_complete'); // Field name
    auditGR.addQuery('documentkey', projectId); // Project sys_id
    auditGR.addQuery('sys_created_on', '>=', sevenDaysAgo); // Changes within last 7 days
    auditGR.query();
    if (auditGR.hasNext()) {
        percentCompleteUpdated = true; // Found an update for "Percent Complete"
    }

    // Check if a status report was submitted in the last 7 days
    var statusReportSubmitted = false;
    var statusReportGR = new GlideRecord('project_status');
    statusReportGR.addQuery('project', projectId); // Filter by current project
    statusReportGR.addQuery('sys_created_on', '>=', sevenDaysAgo); // Submitted within last 7 days
    statusReportGR.query();
    if (statusReportGR.hasNext()) {
        statusReportSubmitted = true; // Found a status report submission
    }

    // Trigger notification if both conditions are not met
    if (!percentCompleteUpdated && !statusReportSubmitted) {
        gs.eventQueue('eventName', projectRec, projectRec.assigned_to.email.toString());
    }
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader