AddQuery is not working on Business Rule

[Avee678]

Hello Everyone,
I have a requirement to update the start_date field on the cmdb_software_product_model table using the start_date from version records (). These version records are created under the related list of the sam_custom_product_lifecycle table.
Whenever a version record is created, the corresponding record's start_date should be updated with the version's start_date.
To achieve this, I wrote an "After Insert" Business Rule on the sam_custom_product_lifecycle table. In the script, I'm using GlideRecord to query .  I’m using the product field to query due to the relationship between the two tables.
The Product field from both tables are referring to same product table. The product from cmdb_software_product_model  and product form sam_custom_product_lifecycle are matching in the logs. but still not going to IF loop.
I've exhausted all approaches I could think of, but I'm unable to resolve this. I'm attaching my script below—please suggest possible solutions or improvements. Your help would be greatly appreciated!

( function executeRule ( current , previous /*null when async*/ )
var lifecycleGR = new GlideRecord ( ' sam_sw_product_lifecycle ' ) ;
lifecycleGR.addQuery ( ' norm_product ' , current.norm_product ) ; lifecycleGR.query ( ) ;
if ( lifecycleGR.next ( ) ) { gs.info ( ' SPM - SCL : ' + lifecycleGR.sys_id ) ;
gs.info ( ' SPM - SCL : 1+ lifecycleGR.norm_product ) ;
var plSysID = lifecycleGR.sys_id ;
var softwareModelGR = new GlideRecord ( ' cmdb_software_product_model ' ) ;
softwareModelGR.addQuery ( ' product' , lifecycleGR.norm_product) ;
softwareModelGR.query ( ) ;
if ( softwareModelGR.next ( ) ) {
gs.info ( ' Found related Software model record : ' + softwareModelGR.sys_id ) ;
softwareModelGR.u_start_of_life_date = current.start_date ;
softwareModeIGR.update ( ) ;
gs.info ( ' Update End date for Software model record : ' + softwareModelGR.sys_id ) ;
else{
gs.info ( ' No related Software model record found for product : ' + current.norm_product ) ;
}
}
} ) ( current , previous )

 

[Nilesh Pol]

@Avee678 

find some findings in your script:

• Correct Table Name: Ensure the correct table names. You've used sam_sw_product_lifecycle but mentioned sam_custom_product_lifecycle in your description. Double-check that the correct table name is used.

• Syntax Error: There’s a syntax error in your code (else without a closing parenthesis and missing curly braces in the script).

• Add current.start_date Correctly: Use current.start_date to update the related cmdb_software_product_model record.

• Check if Product Field is Correct: Make sure that current.norm_product and lifecycleGR.norm_product are valid and return the expected results when queried.

use updated below updated script:

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

// Query the sam_sw_product_lifecycle table based on the 'norm_product' field
var lifecycleGR = new GlideRecord('sam_sw_product_lifecycle');
lifecycleGR.addQuery('norm_product', current.norm_product); // Ensure 'norm_product' is the correct field
lifecycleGR.query();

if (lifecycleGR.next()) {
gs.info('SPM - SCL : ' + lifecycleGR.sys_id); // Log the sys_id of the lifecycle record
gs.info('SPM - SCL Product : ' + lifecycleGR.norm_product);

// Fetch the related Software Product Model record using 'norm_product'
var softwareModelGR = new GlideRecord('cmdb_software_product_model');
softwareModelGR.addQuery('product', lifecycleGR.norm_product); // Assuming 'product' is the field on 'cmdb_software_product_model'
softwareModelGR.query();

if (softwareModelGR.next()) {
gs.info('Found related Software model record: ' + softwareModelGR.sys_id); // Log the found record's sys_id

// Update the 'u_start_of_life_date' field with the value from the version record (current.start_date)
softwareModelGR.u_start_of_life_date = current.start_date;

// Ensure the update happens correctly
var updateResult = softwareModelGR.update();
if (updateResult) {
gs.info('Updated Start Date for Software model record: ' + softwareModelGR.sys_id);
} else {
gs.info('Failed to update Software model record: ' + softwareModelGR.sys_id);
}
} else {
gs.info('No related Software model record found for product: ' + current.norm_product);
}
} else {
gs.info('No related lifecycle record found for product: ' + current.norm_product);
}

})(current, previous);

[Avee678]

Hi Nilesh,

  Thanks for the response

• Correct Table Name: The relation between the cmdb_software_product_model  and the related list is sam_sw_product_lifecycle but records are creating with sam_custom_product_lifecycle. That's why I glided this table

• Syntax Error: I missed syntax while writing here but I used correct syntax in the script
  Add current.start_date Correctly: I used currect value on BR but I used dummy values here

• Check if Product Field is Correct:  product field is currect

[Nilesh Pol]

@Avee678 thanks for clarification.

Please use following final script:

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

// Log the current norm_product to ensure it's set correctly
gs.info('current.norm_product: ' + current.norm_product);

// Query the sam_sw_product_lifecycle table based on the 'norm_product' field
var lifecycleGR = new GlideRecord('sam_sw_product_lifecycle');
lifecycleGR.addQuery('norm_product', current.norm_product);
lifecycleGR.query();

if (lifecycleGR.next()) {
gs.info('SPM - SCL : ' + lifecycleGR.sys_id); // Log the sys_id of the lifecycle record
gs.info('SPM - SCL Product : ' + lifecycleGR.norm_product);

// Fetch the related Software Product Model record using 'norm_product'
var softwareModelGR = new GlideRecord('cmdb_software_product_model');
gs.info('Looking for product: ' + lifecycleGR.norm_product);
softwareModelGR.addQuery('product', lifecycleGR.norm_product);
softwareModelGR.query();

if (softwareModelGR.next()) {
gs.info('Found related Software model record: ' + softwareModelGR.sys_id); // Log the found record's sys_id

// Update the 'u_start_of_life_date' field with the value from the version record (current.start_date)
softwareModelGR.u_start_of_life_date = current.start_date;

// Ensure the update happens correctly
var updateResult = softwareModelGR.update();
if (updateResult) {
gs.info('Successfully updated Start Date for Software model record: ' + softwareModelGR.sys_id);
} else {
gs.info('Failed to update Software model record: ' + softwareModelGR.sys_id);
}
} else {
gs.info('No related Software model record found for product: ' + current.norm_product);
}
} else {
gs.info('No related lifecycle record found for product: ' + current.norm_product);
}

})(current, previous);

[Avee678]

In the query of 'softwareModelGR.addQuery('product', lifecycleGR.norm_product);' 
Both values ae matching but not going into IF loop and both fields are referring to same table
Is anything I need to check here?