How to insert values and labels in sys_choice table from a custom table.

Raviteja Kunal1 · ‎05-30-2022

I have three fields in a custom table as below.

Whenever a new record is inserted with all the values I need to write a script to insert the same with labels and values in the sys_choice table with elements as Category and SubCategory. The issue I am facing here is I need to do insert 2 times with different element values for one record inserted in the custom table. Any idea how can I achieve this?

I really appreciate any help you can provide.

Hitoshi Ozawa · ‎05-31-2022

Hi Raviteja,

Create a business rule on the custom table that executes on "insert".

Check on "Advanced" and add the following script. I've named fields "fuctionalarea" and added fields to enter table name, language, value, and label to set on the choice.

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

    if (current.category == '' || current.functionalarea == '' || current.subcategory == '') {
        return;
    }
    var fieldList = ['category', 'subcategory', 'functionalarea'];
    var name = current.name;  // 'incident';
    var lang = current.language; // 'en';
    var value = current.value;  // 'test';
    var label = current.label;  // 'test';

    for (var i = 0; i < fieldList.length; i++) {
        insertRecord(name, lang, value, label, fieldList[i]);
    }

    function insertRecord(name, lang, value, label, field) {
        var grChoice = new GlideRecord("sys_choice");
        grChoice.addQuery('name', name);
        grChoice.addQuery('element', field);
        grChoice.addQuery('language', lang);
        grChoice.addQuery('value', value);
        grChoice.query();
        if (!checkGR.hasNext()) {
            var grChoice2 = new GlideRecord("sys_choice");
            grChoice2.initialize();
            grChoice2.setValue('name', name);
            grChoice2.setValue('element', field);
            grChoice2.setValue('language', lang);
            grChoice2.setValue('value', value);
            grChoice2.setValue('label', label);

            grChoice2.insert();
        }
    }


})(current, previous);

View solution in original post

Raviteja Kunal1 · ‎05-31-2022

Hi Ankur

This is the script I am writing on insert of a new record. But I am only able to add only category.

var gr = new GlideRecord('sys_choice');
    if (current.operation() == 'insert') {
        gr.initialize();
        gr.setValue('name', 'incident');
        gr.setValue('element', 'category');
        gr.setValue('language', 'en');
        gr.setValue('label', 'test');
        gr.setValue('value', 'test');
    }

gr.insert();

Aman Kumar S · ‎05-31-2022

Why don't to add one more insert statement for subcategory

if (current.operation() == 'insert') {
        var gr = new GlideRecord("sys_choice");
        gr.initialize();
        gr.setValue('name', 'incident');
        gr.setValue('element', 'category');
        gr.setValue('language', 'en');
        gr.setValue('label', 'test');
        gr.setValue('value', 'test');
        gr.insert(;
        
        var grsub = new GlideRecord("sys_choice");
        grsub.initialize();
        grsub.setValue('name', 'incident');
        grsub.setValue('element', 'subcategory');
        grsub.setValue('language', 'en');
        grsub.setValue('label', 'test');
        grsub.setValue('value', 'test');
        grsub.insert()

    }

Best Regards
Aman Kumar

Raviteja Kunal1 · ‎05-31-2022

Here initially we need to validate whether those values are exist in the table.

Aman Kumar S · ‎05-31-2022

Then glide the sys_choice table, pass these values in addquery, and if not found, call the insert logic for record creation.

something like this, you can similarly write logic for subcategory in the same BR

var checkGR = new GlideRecord("sys_choice");
checkGR.addQuery("element",'category');
similary have more statement for language, label and value
checkGR.query();
if(!checkGR.next){
        var gr = new GlideRecord("sys_choice");
        gr.initialize();
        gr.setValue('name', 'incident');
        gr.setValue('element', 'category');
        gr.setValue('language', 'en');
        gr.setValue('label', 'test');
        gr.setValue('value', 'test');
        gr.insert();
}

Best Regards
Aman Kumar

Ankur Bawiskar · ‎05-31-2022

Hi,

you can use 1 more GlideRecord but before inserting have a check if record exists or not

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader