How to create the scheduled job based on two fields

[manjupawadhA]

@Ankur Bawiskar  Hi Hope you doing good

I have one requiremet can you please help to resolved this 

I need to create one notification based on the field.In afe table i have two fields total request and amount spent so A nightly job should run and compare the project total request to amount spend on project if the amount spend is greater than the total request send notification.  Set a flag to indicate that the notification has been sent and capture the date and time that the over budget notification was sent for the project.  Log this information in the Activity log for the active AFE on the project and in the Activity  Log on the Project record.

[Ankur Bawiskar]

@manjupawadhA 

Do this

1) create 2 fields

a) Notification sent - true/false

b) Last Notification sent time - date/time

2) enable auditing on that table

Configuring auditing for a table 

3) run schedule job daily and use this script

Please enhance it as per your requirement.

If you don't want to send email again for the same record then add the query with Notification sent=False

sendEmail();

function sendEmail() {
    var afeGR = new GlideRecord('afe_table');
    afeGR.query();
    while (afeGR.next()) {
        if (afeGR.amount_spent > afeGR.total_request) {
            // Send notification
            var notification = new GlideRecord('sys_email');
            notification.initialize();
            notification.type = 'send-ready';
            notification.recipients = 'recipient@example.com'; // Replace with actual recipient
            notification.subject = 'Over Budget Notification';
            notification.body = 'The project ' + afeGR.project_name + ' has exceeded the budget.';
            notification.insert();

            // Set flag and capture date/time
            afeGR.notification_sent = true;
            afeGR.notification_sent_date = new GlideDateTime();
            afeGR.update();
        }
    }
}

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

[manjupawadhA]

@Ankur Bawiskar  thank you for replying me

I have created the event registry and also created one notification and i tried the scheduled job script but the email is not trigger

(function() {

    // Query the AFE table for records where spend amount > total request

    var afeGr = new GlideRecord('x_your_afe_table'); // Replace with your actual table name

    afeGr.addQuery('amount_spend', '>', 'total_request');

    afeGr.query();

    while (afeGr.next()) {

        // Send notification using event

        gs.eventQueue('x_asfi_afe.afe.amount.exceeded', afeGr, afeGr.number, null); // Send null for the message, since you have it set in the notification

    }

})();

Can you please modify this code

[Ankur Bawiskar]

@manjupawadhA 

did you try with the script I shared?

With that no event, notification required?

You can use GlideEmailOutbound() class for that

In your script is the query getting satisfied?

Also how are you setting the recipient?

send the recipient in 3rd parameter and set Event parm1 contains Recipient=true in notification

(function() {
// Query the AFE table for records where spend amount > total request
var afeGr = new GlideRecord('x_your_afe_table'); // Replace with your actual table name
afeGr.addQuery('amount_spend', '>', 'total_request');
afeGr.query();
gs.info('Row count' + afeGr.getRowCount());
while (afeGr.next()) {
// Send notification using event
gs.eventQueue('x_asfi_afe.afe.amount.exceeded', afeGr, ageGr.<managerField>, null); // Send null for the message, since you have it set in the notification
}
})();

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

[manjupawadhA]

@Ankur Bawiskar 

Yes i have tried that script but the email not triggered 

sendEmail();

function sendEmail() {

    var afeGR = new GlideRecord('afe_table');

    afeGR.query();

    while (afeGR.next()) {

        if (afeGR.amount_spent > afeGR.total_request) {

            // Initialize email notification using GlideEmailOutbound

            var email = new GlideEmailOutbound();

            // Construct the email subject with project number and title

            var subject = 'Project ' + afeGR.project_number + ' ' + afeGR.project_title + ' is overbudget';

            email.setSubject(subject);

            // Construct the email body with project details

            var emailBody = 'Hello,\n\n';

            emailBody += 'Project number ' + afeGR.project_number + ' (' + afeGR.afe_number + '), ' + afeGR.project_title + ' has exceeded the Total Request of ' + afeGR.total_request + '.\n';

            emailBody += 'Current Amount Spent for this project is ' + afeGR.amount_spent + '.\n\n';

            emailBody += 'Contact the Fixed Asset team for guidance to bring your project back within budget.\n\n';

            emailBody += 'Thank you for your prompt attention to this matter.\n\n';

            email.setBody(emailBody);

            // Add recipients: AFE Requestor, AFE Division Executive, and Business Project Manager

            email.addRecipient(afeGR.requestor.email); // Assuming requestor is a reference field with an email

            email.addRecipient(afeGR.division_executive.email); // Assuming division executive is a reference field with an email

            email.addRecipient(afeGR.project_manager.email); // Assuming project manager is a reference field with an email

            // Send the email

            email.send();

            // Set flag and capture date/time

            afeGR.notification_sent = true;

            afeGR.notification_sent_date = new GlideDateTime();

            afeGR.update();

        }

    }

}

OOB email is there ,That why i am created the event registry