How can I optimize my Script Include to insert or update a row in a table?

[gjz1]

I have a Script Include that has code to insert or update a custom table with values from 3 SN tables when the record is either updated or inserted.  My custom table has over 50 columns and it's become messy because I repeat the same statements at least twice for each of the SN tables.  I would like to call a function that copies the fields from one table to another and only have to type (or read) them once.  I did try putting them into a function but it didn't work, the data was not inserted or updated.  Can anyone give me some suggestions on how to solve this problem?

A code sample:

updateCustomTable: function(/*GlideRecord*/project) {

var sysId = project.getValue('sys_id');

// check to see if a row already exists

var custom = new GlideRecord('u_customtable');

custom.addQuery('u_project', sysId);

custom.query();

if(custom.getRowCount() > 0) {

    while(custom.next()) {

        custom.setValue('field1', project.getvalue('field1');

        custom.setValue('field2', project.getValue('field2');

        etc. for 50 more fields

        custom.update();

        }

}

else {

    custom.initialize();

    custom.setValue('field1', project.getvalue('field1');

    custom.setValue('field2', project.getValue('field2');

    etc. for 50 more fields

    custom.insert();

}

},

[Brent Sutton]

Hi gjz,

You could simplify the code by using the following:

updateCustomTable: function(/*GlideRecord*/project) {

	var sysId = project.getUniqueValue();

	// check to see if a row already exists
	var custom = new GlideRecord('u_customtable');

	if(custom.get('u_project', sysId)) {
		//should be able to loop through the data rather than set individual fields. Hard to know without seeing the data.
		custom.setValue("field1", project.getValue("field1"));
		custom.setValue("field2", project.getValue("field2"));
		//etc. for 50 more fields
		custom.update();
	}
	else {
		custom.initialize();
		//should be able to loop through the data rather than set individual fields. Hard to know without seeing the data.
		custom.setValue('field1', project.getValue('field1'));
		custom.setValue('field2', project.getValue('field2'));
		//etc. for 50 more fields
		custom.insert();
	}
},

Let me know if this helped,

Brent

[sachin_namjoshi]

You can do below to optimize script include.

- Create JSON field on your table to store name, value pairs of field values.

- Update your script include function to loop through this JSON field and insert rows in custom tables.

You don't have to write custom.setValue() for all 50 or more fields.

Please follow below for JSON.

https://docs.servicenow.com/bundle/jakarta-application-development/page/app-store/dev_portal/API_reference/JSON/concept/c_JSONAPI.html

https://docs.servicenow.com/bundle/kingston-platform-administration/page/administer/field-administration/reference/name-value-pair-scripting.html

https://community.servicenow.com/community?id=community_question&sys_id=9e6f681ddb1e5f402b6dfb651f961926

Regards,

Sachin

[Mark Stanger]

I haven't tested this (and I'm not sure if I'm completely understanding what you're after) but how about something like this?  If each of the tables uses a different field than 'u_project' as the connecting field, you could also pass the field name in as a second parameter to your 'updateCustomTable' function and then reference that variable in your 'addQuery' line.  As long as the field names you're setting from are consistent that could work for anything you wanted to throw at it.

updateCustomTable: function(/*GlideRecord*/project) {
    var sysId = project.getValue('sys_id');
    // Check to see if a row already exists
    var custom = new GlideRecord('u_customtable');
    custom.addQuery('u_project', sysId);
    custom.query();
    if (custom.getRowCount() > 0) {
        while(custom.next()) {
            custom = this._populateGR(custom, project);
            custom.update();
        }
    }

    else {
        custom.initialize();
        custom = this._populateGR(custom);
        custom.insert();
    }
},

_populateGR: function(/*GlideRecord*/custom, /*GlideRecord*/project) {
        custom.setValue('field1', project.getvalue('field1'));
        custom.setValue('field2', project.getValue('field2'));
        // etc. for 50 more fields
        return custom;
},

[Mark Stanger]

Just checking on on this one.  Has this question been answered or is there more information I can help you with?  If it's been answered, please mark the answer above as the correct one so people know this has been taken care of.  Thanks!