Business rule on change request table and stg change record producer table

[Darlene York]

Hello,

I am trying to create a business rule that would get the opened_at from the change_request table

And carry it over to the appropriate standard change template and populate the opened_at date into the u_last_used date one the template form on the std_change_record_producer table.

This would change every time that template is submitted.  The purpose is to be able to run a report and see when the last time a template was used.

// Triggered when a change request is updated

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

    // Ensure that we have an open change request with a valid 'opened_at' value

    if (current.opened_at && current.template) {

        // Get the template ID that is associated with the change request

        var templateId = current.template;

        // Create a GlideRecord object for the std_change_record_producer table

        var producerGR = new GlideRecord('std_change_record_producer');

        // Query for the specific record that uses the template

        if (producerGR.get(templateId)) {

            // Update the u_last_used field with the opened_at value

            producerGR.u_last_used = current.opened_at;

            // Save the record

            producerGR.update();

        }

    }

})(current, previous);

Thank you for your help

[Robert H · Accepted solution]

Hello @Darlene York ,

The following Business Rule will achieve this:

Note: the Filter Condition is "Standard Change Template version" => "Template" | "is not empty".

Script:

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

	var template = current.std_change_producer_version.std_change_producer.getRefRecord();
	template.u_last_used = gs.nowDateTime();
	template.update();

})(current, previous);

Regards,

Robert

[Robert H · Accepted solution]

Hello @Darlene York ,

The following Business Rule will achieve this:

Note: the Filter Condition is "Standard Change Template version" => "Template" | "is not empty".

Script:

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

	var template = current.std_change_producer_version.std_change_producer.getRefRecord();
	template.u_last_used = gs.nowDateTime();
	template.update();

})(current, previous);

Regards,

Robert

[Darlene York]

Hi Robert,

Thank you so much that worked perfectly!

Have a great weekend!

[Darlene York]

Robert, 

One more thing do you know if there is a way to make this retroactive so hi will update the last used date on all the standard templates?  It works great if I submit a new standard change.  It updates it just the way I want it too.

[Robert H · Accepted solution]

Hello @Darlene York ,

Sure, please use the following Fix Script to update all Standard Change Templates based on the most recently created Change Request for each:

var gaChg = new GlideAggregate('change_request'),
    templateField = 'std_change_producer_version.std_change_producer';
gaChg.addNotNullQuery(templateField);
gaChg.groupBy(templateField);
gaChg.addAggregate('MAX', 'sys_created_on');
gaChg.query();

while (gaChg.next()) {
    new GlideQuery('std_change_record_producer')
        .where('sys_id', gaChg.getValue(templateField))
        .update({
            u_last_used: gaChg.getAggregate('MAX', 'sys_created_on')
        });
}

Regards,

Robert