Importing record producers and variables

General Add comments
by:

At our Facilities Management application, we had to create some record producers, a good amount of them, some with the same groups of basic questions (Who?, When?, Where?), but lots of different specific questions.
We can create the record producers, variable sets and variables manually, but it would take a lot of time to do so.

The Challenge

Create record producers with different categories, questions and order. Must be created quickly and easily from an excel file.

The Solution

– Created the Categories and the variable sets on the instance.
– Created an excel file with two sheets, one for the record producer and variable sets, the second for the variables.

Here is an example of sheet 1:

Note that the requestor, location, time and remarks columns point to existing variable sets.

Below is sheet 2:

On the second sheet, we define the questions, labels, type of variable, order, reference qualifier or choice list.

To create the record producers we import the excel file to our instance. And run the transform map with the following scripts:

(function transformRow(source, target, map, log, isUpdate) {
               
    //Fetch Catalog sys_id
    var catalog = new GlideRecord("sc_catalog");
    catalog.get('title', 'Facilities Management');
   
    var categoryFromData;
   
    //Fetch Category sys_id
    var category = new GlideRecord("sc_category");
    category.addQuery('sc_catalog', catalog.sys_id.toString());
    category.addQuery('title', source.u_category.toString());
    category.query();
    if(category.next()) {
                    categoryFromData = category.sys_id.toString();
    }
   
    //Fetch scope sys_id
    var scope = '';
    var application = new GlideRecord("sys_app");
    application.addQuery('scope', 'x_lsmcb_fm');
    application.query();
    if(application.next()) {
                    scope = application.sys_id.toString();
    }
   
   
    target.sys_scope = scope;
    target.table_name = 'x_lsmcb_fm_facility_order';
    target.active = 1;
    target.category = categoryFromData;
    target.sc_catalogs = catalog.sys_id.toString();
               
})(source, target, map, log, action==="update");

The above script will create the actual Record Producers, the onAfter will assign the existing variable sets and create the new variables. Below is the onAfter script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
    //Create Questions in Record Producer
    var irpVariables = new GlideRecord("u_imp_irp_variables");
    irpVariables.addQuery("u_record_producer", source.u_name.toString());
    irpVariables.query();
    while (irpVariables.next()) {
        var variables = new GlideRecord("item_option_new");
        var type;
        switch (irpVariables.u_type.toString().trim()) {
            case 'Yes/No':
                type = 1;
                break;
            case 'Multi Line Text':
                type = 2;
                break;
            case 'Multiple Choice':
                type = 3;
                break;
            case 'Numeric Scale':
                type = 4;
                break;
            case 'Select Box':
                type = 5;
                break;
            case 'Single Line Text':
                type = 6;
                break;
            case 'CheckBox':
                type = 7;
                break;
            case 'Reference':
                type = 8;
                break;
            case 'Date':
                type = 9;
                break;
            case 'Date/Time':
                type = 10;
                break;
            case 'Label':
                type = 11;
                break;
            case 'Break':
                type = 12;
                break;
            case 'Macro':
                type = 14;
                break;
            case 'UI Page':
                type = 15;
                break;
            case 'Wide Single Line Text':
                type = 16;
                break;
            case 'Macro with Label':
                type = 17;
                break;
            case 'Lookup Select Box':
                type = 18;
                break;
            case 'Container Start':
                type = 19;
                break;
            case 'Container End':
                type = 20;
                break;
            case 'List Collector':
                type = 21;
                break;
            case 'Lookup Multiple Choice':
                type = 22;
                break;
            case 'HTML':
                type = 23;
                break;
            case 'Container Split':
                type = 24;
                break;
            case 'Masked':
                type = 25;
                break;
            default:
                type = 6;
                break;
        }
        variables.initialize();
        variables.question_text = irpVariables.u_question_text.toString();
        variables.name = irpVariables.u_name.toString();
        variables.type = type;
        variables.sys_scope = target.sys_scope;
        variables.mandatory = irpVariables.u_mandatory;
        variables.active = true;
        variables.order = irpVariables.u_order;
        variables.reference = irpVariables.u_reference.toString();
        variables.use_reference_qualifier = irpVariables.u_reference_qual_type.toString();
        variables.reference_qual = irpVariables.u_reference_qual.toString();
        variables.default_value = irpVariables.u_default_value.toString();
        variables.cat_item = target.sys_id.toString();
        variables.insert();
        //If Multiple Choice type
        if (variables.type == 5) {
            var choices = irpVariables.u_choice_list.toString();
            var choice = choices.split(";");
            for (var i = 0; i < choice.length; i++) {
                var choiceList = new GlideRecord("question_choice");
                choiceList.initialize();
                choiceList.sys_scope = target.sys_scope;
                choiceList.question = variables.sys_id.toString();
                choiceList.text = choice[i];
                choiceList.value = choice[i];
                choiceList.order = i;
                choiceList.insert();
            }
        }
    }
    // Assign the variable sets
    if (source.u_requester.toString() != "") {
        var existingSet = new GlideRecord("item_option_new_set");
        if (existingSet.get("description", source.u_requester.toString())) {
            var setItem = new GlideRecord("io_set_item");
            setItem.initialize();
            setItem.variable_set = existingSet.sys_id;
            setItem.sc_cat_item = target.sys_id.toString();
            setItem.sys_scope = target.sys_scope;
            setItem.insert();
        }
    }
    if (source.u_location.toString() != "") {
        var existingSet = new GlideRecord("item_option_new_set");
        if (existingSet.get("description", source.u_location.toString())) {
            var setItem = new GlideRecord("io_set_item");
            setItem.initialize();
            setItem.variable_set = existingSet.sys_id;
            setItem.sc_cat_item = target.sys_id.toString();
            setItem.sys_scope = target.sys_scope;
            setItem.insert();
        }
    }
    target.update();
})(source, map, log, target);

I hope you are able to reuse this script to quickly import many record producers and variables! Please drop a comment if you would like more information!
.img[at].img

Leave a Reply