How to trigger the calculated field automatically once condition is met instead of saving the record on the form

[Abhishek Kumar8]

The field “Pen Test Compliance Status” is not updating its value until the dependent records (below) are saved/updated, but it should update when the conditions of the calculation are met.

u_pentest_app_inventory table contains a field called ‘Pen Test Compliance Status’ which is calculated based on a few other values. The other values are:

u_pentest_appl_inventory table – ‘Last Full Test Date’ and ‘Is Testing Required’ fields

u_pentest_deviation table – ‘Pen Test Deviation’ field calculated as Y/N

Is there a way to set it so that “Pen Test Compliance Status” gets calculated and updated on a reoccurring/scheduled basis instead of waiting for a saved record to trigger it?

BR - Before ( insert/update) - u_pentest_appl_inventory : calculate pen test compliance status(compliant/overdue/no testing reqd/never tested) and is testing required ( yes/no) based on the conditions

BR - Before ( insert/update) - u_pentest_deviation :  calculate Pen Test Deviation ( Y/N)

will the scheduled job work here to trigger them automatically in the backend without saving the changes on the form as BR does here?

Any help on this is really appreciated.

Thank you,

Abhishek Kumar

[Community Alums]

you need a onChange client script(s) to achieve this. BR will not work, before and after BRs only trigger as and when Save button is clicked. It sounds like you want to set value of a field when value of other field(s) change, correct?

[Abhishek Kumar8]

Yes, I have written the change() script to achive the functionality. but the script is not working here since the value set on the field (u_pen_test_manual_test_status) is present in another table( i.e pen test invent).

On change script: 

Field name : Pen Test Deviation

Table : Pentest_dev

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
if (isLoading) {
return;
}

//Type appropriate comment here, and begin script below
var pentest_dev = g_form.getValue('u_pen_test_deviations');
var app_dev_id = g_form.getValue('u_pen_test_deviation_application_name');

var gdev = new GlideAjax('ExpComplCheckLastFullTest'); //Name of the Script Include

gdev.addParam('sysparm_name', 'CheckCompliStatus'); //Name of the function in the script include

gdev.addParam('sysparm_appl_id', app_dev_id); //Parameter to pass to the script include

gdev.addParam('sysparm_pen_test_dev', pentest_dev); //Parameter to pass to the script include

gdev.getXML(ExpComplCheckLastFullTestParse);

//Function that gets the response and will return to the client. You can place your alert in this function

function ExpComplCheckLastFullTestParse(response) {

var answer = response.responseXML.documentElement.getAttribute("answer");

if (answer == 'true2') {

g_form.setValue('u_pen_test_manual_test_status', '2'); // complaint
//alert("is testing requiredtestcompliant" + g_form.getValue('u_pen_test_manual_test_status'));
}
if (answer == 'true5') {
g_form.setValue('u_pen_test_manual_test_status', '5'); // overdue
//alert("is testing requiredtestoverdue" + g_form.getValue('u_pen_test_manual_test_status'));
}
if (answer == 'true4') {
g_form.setValue('u_pen_test_manual_test_status', '4'); // never tested
//alert("is testing requiredtestnevertestedft" + g_form.getValue('u_pen_test_manual_test_status'));
}
if (answer == 'true1') {
g_form.setValue('u_pen_test_manual_test_status', '1'); // no tested required

}
if (answer == 'true3') {
g_form.setValue('u_pen_test_manual_test_status', '3'); // deviation

}

}

}

Script includes: 

var ExpComplCheckLastFullTest = Class.create();
ExpComplCheckLastFullTest.prototype = Object.extendsObject(AbstractAjaxProcessor, {
CheckCompliStatus: function() {

var applid = this.getParameter('sysparm_appl_id');
var pentestdeviation = this.getParameter('sysparm_pen_test_dev');
var pen_test_no_record;
var pentestdev = new GlideRecord('u_pentest_app_inventory');
pentestdev.addQuery('u_pen_test_app_name', applid);
pentestdev.query();
if (pentestdev.next()) {
var istestingreqd = pentestdev.u_is_testing_required;
var fulltest = pentestdev.u_pen_test_last_full_test_date;
var start = new GlideDateTime();
if (fulltest != '') {
var end = new GlideDateTime(fulltest);
var duration = GlideDateTime.subtract(end, start);
var totaldate = duration.getNumericValue() / 86400000; // convert milliseconds to days
totaldate = parseInt(totaldate);

}
// record found
pen_test_no_record = false;

} else {
// no record found
pen_test_no_record = true;

}

if ((pentestdeviation == 'Y') && (pen_test_no_record == false)) {
//deviation
return "true3";

}
if (((pentestdeviation == 'N') || (pen_test_no_record == true)) && (totaldate < 365) && (istestingreqd == 'Yes')) {
//complaint
//gs.log("DEBUG: pen test not foundcompliant");
return "true2";

}
if (((pentestdeviation == 'N') || (pen_test_no_record == true)) && (totaldate > 365) && (istestingreqd == 'Yes')) {
// overdue
//gs.log("fulltesttestdate123overdue" + fulltest + "testing" + end);
return "true5";
}
if ((pentestdeviation == 'N') || (pen_test_no_record == true) && (fulltest == '') && (istestingreqd == 'Yes')) {
//gs.log("fulltesttestdate123" + fulltest + "testing" + end);
// never tested
return "true4";
}
if ((pentestdeviation == 'N') || (pen_test_no_record == true) && (istestingreqd == 'No')) {
// no testing required
return "true1";
}

},

type: 'ExpComplCheckLastFullTest'
});

could you please help on this.

Thank you,

Abhishek kumar

[Community Alums]

can you verify two things?

1) put an alert after this line

var answer = response.responseXML.documentElement.getAttribute("answer");

alert(answer); // to confirm which value is being returned

2) Is the Script Include marked as Client callable and Active?

Once above are good then change following lines

pen_test_no_record = 'false'; //put quotes around false

pen_test_no_record = 'true';

also put quotes around false and true in conditions in script include where you return "true1', true2' etc.

[rajneeshbaranwa]

You have to use client script here.

Scheduled script will work only if the data is updated in the table and will be similar to how business rule work except it will work on scheduled basis rather than on database trigger.

I am assuming here that you want Pen Test Compliance Status field to update in the form before saving it to the database.