how to update value from one table(sc_request) to another table(sc_req_item) using background script

[Vinod S Patil]

Hello Everyone

I have requirement to update contact_type field value from one table(sc_request) to another table(sc_req_item) using background script.

Please suggest me the code.

[SP22]

Hello @Vinod S Patil,

Using background script for updating the records is not a best practice.

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0696028
Thanks
SP.

[Akash4 · Accepted solution]

Hi Vinod,

If you are using it for demo purposes, you can also try from Fix Script, very similar to your requirement.

You can use this code, but make sure you create in right scope for its working:

var gr = new GlideRecord('sc_request');

gr.query(); //add filters if necessary
while (gr.next()) {
var contactType = gr.contact_type;

var reqItemGr = new GlideRecord('sc_req_item');

reqItemGr.addQuery('request', gr.sys_id);
reqItemGr.query();
while (reqItemGr.next()) {
reqItemGr.contact_type = contactType;
reqItemGr.update();
}
}

[James-B]

You need to provide more info.

Is this just a one off? 

What is the problem?

What is the purpose? 

[HIROSHI SATOH]

Here’s an example script for this:

// Query the sc_request table to get the contact_type value
var reqGR = new GlideRecord('sc_request');
reqGR.addQuery('sys_id', 'INSERT_SYS_ID_OF_REQUEST');
reqGR.query();

if (reqGR.next()) {
    var contactType = reqGR.getValue('contact_type');
    
    // Query the sc_req_item table to update the contact_type field
    var reqItemGR = new GlideRecord('sc_req_item');
    reqItemGR.addQuery('request', reqGR.sys_id);  // Ensure it matches the related request
    reqItemGR.query();
    
    while (reqItemGR.next()) {
        reqItemGR.contact_type = contactType;
        reqItemGR.update();
        gs.info('Updated sc_req_item record: ' + reqItemGR.sys_id);
    }
}

Explanation:

1. Query the sc_request table: The script fetches the contact_type from a specific request.
2. Query the sc_req_item table: For each item related to the sc_request, it updates the contact_type field.
3. Update the records: After setting the value, the script updates each sc_req_item record.

I think it's best to implement it as a business rule.

To update the contact_type field in the sc_req_item table whenever the contact_type in the sc_request table changes, you can create a Business Rule. The Business Rule will be triggered when the sc_request table is updated, and it will propagate the changes to the related sc_req_item records.

Here’s how you can write the Business Rule script:

1. Create a Business Rule on the sc_request table.
2. Set it to run after update.
3. Add the following script to the Script section.

(function executeRule(current, previous /*null when async*/) {
    
    // Check if contact_type has changed
    if (current.contact_type != previous.contact_type) {
        var reqItemGR = new GlideRecord('sc_req_item');
        reqItemGR.addQuery('request', current.sys_id);  // Find items related to the request
        reqItemGR.query();
        
        while (reqItemGR.next()) {
            reqItemGR.contact_type = current.contact_type;  // Update the contact_type
            reqItemGR.update();
        }
        
        gs.info('Updated contact_type for all related sc_req_item records for request: ' + current.sys_id);
    }
    
})(current, previous);

Explanation:

1. Trigger Condition: The Business Rule runs when the sc_request record is updated.
2. Check for changes: It checks whether the contact_type field has been modified (current.contact_type != previous.contact_type).
3. Query related sc_req_item records: It retrieves all sc_req_item records that are associated with the updated sc_request.
4. Update related records: It updates the contact_type field in the sc_req_item records.
5. Logging: It logs the updates for auditing purposes.