How to insert multiple records in a table through scheduled job

Munna1 · ‎02-01-2024

Hello everyone,

I am trying to insert multiple records in the u_association table based on the Attribute value, with this below scheduled job script. But only one record is inserting for each attribute. How can we insert multiple records in this table. I am trying with this script, but it is not inserting multiple records. Is anything is missing here can anyone help me on this please.

var grs = new GlideRecord("cmdb_ci_auto_data");

grs.addEncodedQuery("u_application_idISNOTEMPTY");

//grs.setLimit(1);

grs.query();

while (grs.next()) {

//gs.print(grs.getAggregate("count")+"@--"+grs.getValue("u_application_id") );

var app_id = grs.u_application_id;

var app_sysid = grs.sys_id;

// calling Rest API

var contact_sub_type = '';

var email = '';

var user_sysid = '';

var smeflag = 0;

var roflag = 0;

var adlflag = 0;

var counter = 0;

var r = new sn_ws.RESTMessageV2('APPDATA', 'GET_APP_TRACK');

r.setStringParameterNoEscape('app_id', app_id);

var response = r.execute();

var responseBody = response.getBody();

var httpStatus = response.getStatusCode();

var obj = JSON.parse(responseBody);

if (httpStatus == 200) {

for (var i = 0; i < obj.length; i++) {

// gs.info(obj[i].Name);

contact_sub_type = obj[i].Name;

email = obj[i].Email;

var gruser = new GlideRecord('sys_user');

gruser.addQuery('email', email);

gruser.query();

if (gruser.next()) {

user_sysid = gruser.sys_id;

}

if (attribute == 'RGE')

{

smeflag = 1;

counter = counter + 1;

var grassoc_check = new GlideRecord('u_associations');

grassoc_check.addQuery('u_association_record', app_sysid);

grassoc_check.addQuery('u_attribute', 'RGE');

grassoc_check.addQuery('operational_status', 1);

grassoc_check.query();

if (grassoc_check.next()) {

grassoc_check.u_user_association = user_sysid;

grassoc_check.setWorkflow(false);

grassoc_check.update();

} else {

var grassoc = new GlideRecord('u_associations');

grassoc.initialize();

grassoc.name = "Application Contacts";

grassoc.u_association_record = app_sysid;

grassoc.u_user_association = user_sysid;

grassoc.operational_status = '1';

grassoc.sys_class_name = 'u_associations';

grassoc.insert();

}

} else if (attribute == 'incident') {

roflag = 1;

counter = counter + 1;

var grassoc_check1 = new GlideRecord('u_associations');

grassoc_check1.addQuery('u_association_record', app_sysid);

grassoc_check1.addQuery('u_attribute', 'incident');

grassoc_check1.addQuery('operational_status', 1);

grassoc_check1.query();

if (grassoc_check1.next()) {

grassoc_check1.u_user_association = user_sysid;

grassoc_check1.setWorkflow(false);

grassoc_check1.update();

} else {

var grassoc1 = new GlideRecord('u_associations');

grassoc1.initialize();

grassoc1.name = "Application Contacts";

grassoc1.u_association_record = app_sysid;

grassoc1.u_user_association = user_sysid;

grassoc1.operational_status = '1';

grassoc1.sys_class_name = 'u_associations';

grassoc1.insert();

}

} else if (attribute == 'string') {

adlflag = 1;

counter = counter + 1;

var grassoc_check2 = new GlideRecord('u_associations');

grassoc_check2.addQuery('u_association_record', app_sysid);

grassoc_check2.addQuery('u_attribute', 'string');

grassoc_check2.addQuery('operational_status', 1);

grassoc_check2.query();

if (grassoc_check2.next()) {

grassoc_check2.u_user_association = user_sysid;

grassoc_check2.setWorkflow(false);

grassoc_check2.update();

} else {

var grassoc2 = new GlideRecord('u_associations');

grassoc2.initialize();

grassoc2.name = "Application Contacts";

grassoc2.u_association_record = app_sysid;

grassoc2.u_user_association = user_sysid;

grassoc2.operational_status = '1';

grassoc2.sys_class_name = 'u_associations';

grassoc2.insert();

}

var grs1 = new GlideRecord('u_associations');

grs1.addQuery('u_association_record', app_sysid);

grs1.addQuery('operational_status', 1);

grs1.query();

var association_record_count = grs1.getRowCount();

if (counter < association_record_count) {

if (smeflag == 0) {

var grSetRetired = new GlideRecord('u_associations');

grSetRetired.addQuery('u_association_record', app_sysid);

grSetRetired.addQuery('u_attribute', 'RGE');

grSetRetired.query();

if (grSetRetired.next()) {

grSetRetired.operational_status = 6;

grSetRetired.setWorkflow(false);

grSetRetired.update();

}

if (roflag == 0) {

var grSetRetired1 = new GlideRecord('u_associations');

grSetRetired1.addQuery('u_association_record', app_sysid);

grSetRetired1.addQuery('u_attribute', 'incident');

grSetRetired1.query();

if (grSetRetired1.next()) {

grSetRetired1.operational_status = 6;

grSetRetired1.setWorkflow(false);

grSetRetired1.update();

}

if (adlflag == 0) {

var grSetRetired2 = new GlideRecord('u_associations');

grSetRetired2.addQuery('u_association_record', app_sysid);

grSetRetired2.addQuery('u_attribute', 'string');

grSetRetired2.query();

if (grSetRetired2.next()) {

grSetRetired2.operational_status = 6;

grSetRetired2.setWorkflow(false);

grSetRetired2.update();

}

I will appreciate the explanation with the Script.

Thanks In Advance.

Mark Manders · ‎02-01-2024

Am I missing the definition of 'attribute' in your script, or did I read over it? Could it be from the 'obj[i]' JSON? What is the exact issue? Is it only creating one record? That could be due to the check on existing records ('grassoc_check.query(); if grassoc_check.next()) .... It will update the record found instead of creating a new one.

I made some assumptions, but maybe this will help you along:

var grs = new GlideRecord("cmdb_ci_auto_data");
grs.addEncodedQuery("u_application_idISNOTEMPTY");
grs.query();
while (grs.next()) {
    var app_id = grs.u_application_id;
    var app_sysid = grs.sys_id;

    var r = new sn_ws.RESTMessageV2('APPDATA', 'GET_APP_TRACK');
    r.setStringParameterNoEscape('app_id', app_id);
    var response = r.execute();
    var responseBody = response.getBody();
    var httpStatus = response.getStatusCode();

    if (httpStatus == 200) {
        var obj = JSON.parse(responseBody);

        for (var i = 0; i < obj.length; i++) {
            var attribute = obj[i].Attribute; // Ensure you have an Attribute in your JSON
            var email = obj[i].Email;
            var user_sysid = getUserSysId(email);
            if (user_sysid) {
                updateAssociation(app_sysid, user_sysid, attribute);
            }
        }
        // Additional logic for setting operational status to 6 can go here
    }
}

function getUserSysId(email) {
    var gruser = new GlideRecord('sys_user');
    gruser.addQuery('email', email);
    gruser.query();
    if (gruser.next()) {
        return gruser.sys_id.toString();
    }
    return null;
}

function updateAssociation(appSysId, userSysId, attribute) {
    var grassoc = new GlideRecord('u_associations');
    grassoc.initialize();
    grassoc.name = "Application Contacts";
    grassoc.u_association_record = appSysId;
    grassoc.u_user_association = userSysId;
    grassoc.u_attribute = attribute; // Assuming this is how you determine RGE, incident, string, etc.
    grassoc.operational_status = '1';
    grassoc.sys_class_name = 'u_associations';
    grassoc.insert();
}

Please mark any helpful or correct solutions as such. That helps others find their solutions.
Mark

Munna1 · ‎02-01-2024

Hi Mark,

Thanks for your response,

But The above script is not working,

Here Records need to insert in the association table based on attribute value

Eg: If Attribute is ->RGE have 5 records

incidents have 3 records and

String have 4 records.

But this script showing only first record in each attribute. We need all records need to display in the association table.

Thanks