Update existing record with info from other tables

[calebb]

I'm a little fresh with ServiceNow scripting, so I'll explain what I'm trying to do and show the embarrassing script I've written afterwards.

We have three tables:

        1. New

        2. Products

        3. Overview

New contains initial requests, and products are added independently on insert through another business rule or manually at a later time. Overview is supposed to be a summary of info from both of the other tables(so if a client has 20 products of two different types, I could rolle up Product A and B into 10 and 10). I have the following script that is working to insert info from 1 to 3.

This script is after insert to the New table.

(function executeRule(current, previous /*null when async*/) {
       var overview = new GlideRecord('x_etgr_cyclops_cyclops_overview');
               overview.initialize();
               overview.dealer_name = current.dealership;
               overview.retention_period = current.u_retention_period;
               overview.insert();
})(current, previous);

My intention with the script below is to find an Overview record that has a matching dealer_name as the form being submitted on the Products table. Then I want it to check to see if certain product types exist, and if they do, fill in a checkbox on the Overview page.

This script is after insert to the Products table.

(function executeRule(current, previous /*null when async*/) {
       var overview = new GlideRecord('x_etgr_cyclops_cyclops_overview');
       current.addQuery(current.u_request, overview.dealer_name);
       current.query();
               while(overview.next()){
                       if(current.u_product == 'Term Discount'){
                               overview.special_pricing_term = 'True';
                       }
                       if(current.u_product == 'Price Discount'){
                               overview.special_pricing_discount = 'True';
                       }
                       gs.info('outside while loop');
                       overview.update();
               }
})(current, previous);

What it's actually doing is inserted a new Overview record rather than trying to update the one that exists already, but my products (in this case two discounts) aren't checking the box that is on this overview page.

[Pradeep Sharma]

Hello Caleb,

Below is the updated code.

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

       var overview = new GlideRecord('x_etgr_cyclops_cyclops_overview');   
       overview.addQuery('dealer_name', current.u_request);   //Assuming dealer_name is the column on table x_etgr_cyclops_cyclops_overview and u_request on the table where business rule is defined.
       overview.query();   
               while(overview.next()){   
                       if(current.u_product == 'Term Discount'){   
                               overview.special_pricing_term = 'True';   
                       }   
                       if(current.u_product == 'Price Discount'){   
                               overview.special_pricing_discount = 'True';   
                       }   
                       gs.info('outside while loop');   
                       overview.update();   
               }   

})(current, previous);   

[calebb · Accepted solution]

It's a little more complicated now, but just wanted to update with a version that is now working as expected:

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

/*   
The three different if conditions per discount are to account for a couple different scenarios. 
The boolean should be properly set to true upon first insert, but also if a user happens to expire 
a discount by mistake then set it back to in use. It should also be set to false of course when 
the record is inactive.
*/

         var overview = new GlideRecord('x_etgr_cyclops_cyclops_overview');


         overview.addQuery("dealer_name", current.dealer_name.sys_id);
         overview.query();
                   while(overview.next())
{
                             if(current.u_product == 'Term Discount' && current.operation() == "insert" && current.active == true){
                             overview.special_pricing_term = 'true';
                             }
                             if(current.u_product == 'Term Discount' && current.operation() == "update" && current.active == true && previous.active == false){
                             overview.special_pricing_term = 'true';
                             }
                             if(current.u_product == 'Term Discount' && current.operation() == "update" && current.active == false){
                             overview.special_pricing_term = 'false';
                             }
                             if(current.u_product == 'Price Discount' && current.operation() == "insert" && current.active == true){
                             overview.special_pricing_discount = 'true';
                             }
                             if(current.u_product == 'Price Discount' && current.operation() == "update" && current.active == true && previous.active == false){
                             overview.special_pricing_discount = 'true';
                             }
                             if(current.u_product == 'Price Discount' && current.operation() == "update" && current.active == false){
                             overview.special_pricing_discount = 'false';
                             }
                   overview.update();
         }
}
)(current, previous);

[michaeltillman]

this is old but .update() should not be used in BRs