- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-07-2025 04:19 AM
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
Please suggest,
Thank you all.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-07-2025 05:23 AM
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.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-08-2025 03:20 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-08-2025 03:28 AM
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.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-08-2025 04:36 AM
Thank you @Ankur Bawiskar ,
could you please please provide the solution, the notification will run weekly once.
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-08-2025 04:48 AM
It seems you marked your own response as correct.
would you mind marking my response as correct if that helped you?
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-08-2025 05:04 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-08-2025 05:13 AM
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.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader