How to fetch Multirow variables on RITM/SCTASK using REST api

ursnani · ‎06-29-2020

Hi All,

I am trying to fetch all the variables on RITM/SCTASK and I found this article

https://snprotips.com/blog/2016/7/15/scripted-rest-apis-in-servicenow-how-to-retrieve-catalog-item-v...

Which helped me in getting the variables using a Scripted REST api to get all the variables. But this Scripted API is not fetching Multi-row variable set which is on RITM/SCTASK. Can some one please help me in modifying the code so that I can fetch Multi-Row variable set as well.

Thank you.

ursnani · ‎06-29-2020

Hi Asif,

Thanks for you input/suggestion and finally it worked.

here is the final code how it looks.

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
    var variableName, i, sctaskVariables, sctaskNumber, grSCTask, variablesObject, sctaskNumbers;
	
	var objApproval = {}; //The object that should ultimately be returned.
	
    //Set sctaskNumber from header value, convert to an array, and trim each element.
    sctaskNumbers = trimAndSplit(request.getHeader('sctask'), ','); //Calling custom script include.
    //sctaskNumbers = request.getHeader('ritm').split(','); //<--Commented out, but you can enable it if you don't have the trimAndSplit script include.
    if (!sctaskNumbers) {
        //If sctask header was not set or was malformed, return an error.
        response.setStatus(417);
        return 'Unable to process request. Request header \'sctask\' not defined. ' +
            'Please define a request header called \'sctask\' with the value set to the ticket number for the SCTASK you\'d like to retrieve the variables for. Value provided for SCTASK: ' + sctaskNumbers;
    }

    grSCTask = new GlideRecord('sc_task');
    //For each SCTASK in the sctaskNumbers array...
    for (i = 0; i < sctaskNumbers.length; i++) {
        //Get the SCTASK number
        sctaskNumber = sctaskNumbers[i];

        //Get the GlideRecord for the SCTASK, or throw an error if we can't.
        if (!grSCTask.get('number', sctaskNumber)) { //If we can't find the RITM...
            response.setStatus(417); //set the status to indicate a bad argument
            response.setHeader(sctaskNumber,
                'Unable to locate Catalog Task: ' + sctaskNumber); //add a header to indicate which RITM was bad.
            gs.logError(
                'Scripted REST API ritm_vars_api unable to process request. SCTASK not found: ' +
                sctaskNumber); //Log an error so the admin knows what happens if the requestor asks.
            continue; //Continue the loop with the next RITM in case there's anything valid we *can* return.
        }
        //Get the object containing the catalog variables.
        sctaskVariables = grSCTask.variables;
        var mrvs = JSON.parse(grSCTask.variables.test_copy);
        for (var j = 0; j < mrvs.length; j++) {
			objApproval['Row'+j] = mrvs[j];// Build the object with group and approval order 
        }
    }
    //responseObj = addObjToObj(mrvs);
    gs.log('FInal OBJ == ' + JSON.stringify(objApproval));

    return objApproval;

    function trimAndSplit(inputString, token) {
        var i, inputArr;
        var resultArray = [];

        //Give token a default value if not specified
        token = (!token) ? ',' : token;

        if (!inputString || typeof inputString !== 'string') {
            gs.logError(
                'trimAndSplit function received an invalid argument in the first argument. The argument received was: ' + inputString + '. The only acceptable first argument for trimAndSplit, is a string.');
            return [];
        }
        if (inputString.indexOf(token) < 0) {
            //Return the original string in an array, because no instances of the token were found.
            return [inputString];
        }

        //convert the input string to an array, splitting on the provided token (usually ',')
        inputArr = inputString.split(token);
        for (i = 0; i < inputArr.length; i++) {
            //Trim each element in the split string array, then push it to the return array.
            resultArray.push(inputArr[i].trim());
        }

        //return the trimmed and split array.
        return resultArray;
    }

})(request, response);

View solution in original post

sachin_namjoshi · ‎06-29-2020

You can use the REST API to pull data from any table.

variables are actually called "Options" once they have a value assigned and are stored in the Options table [sc_item_option]. The table called "Variables" ([item_option_new]) only stores the definition of that variable (i.e., it's type, associated question, hint, etc.). So the Options table is where you want to look at for your actual values.

To get to the Options you are looking for, you will need to look at a Many-to-Many table [sc_item_option_mtom] relating your Requested Items to their Options. You should have RITM sys_id and then use below:

A.you would query [sc_item_option_mtom] to get a list of the Options (your variables) that belong to it.

B)you can use that list of option sys_id's to query [sc_item_option] for the values you are after.

Regards,

Sachin

Ankur Bawiskar · ‎06-29-2020

Hi,

please share your script

MRVS value is stored in json format

Regards
Ankur

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

asifnoor · ‎06-29-2020

Hi ursnani,

This line actually gives you access to variables

//Get the object containing the catalog variables.
        ritmVariables = grRitm.variables;

//If you want to access variables which are MRVS, then use the below code

mrvs = ritmGR.variables.variable_set_name;
var rowCount = mrvs.getRowCount();
for (var i = 0; i < rowCount; i++) {
	var row = mrvs.getRow(i);
	var var1 = row.your_var_name;
	var var2 = row.your_var_name2;
}

Ref: https://community.servicenow.com/community?id=community_blog&sys_id=865b5aeddbc023c0feb1a851ca9619f9

Kindly mark the comment as a correct answer and helpful if it helps to solve your problem.

Regards,
Asif
2020 ServiceNow Community MVP

ursnani · ‎06-29-2020

Hi Asif,

Thanks for the code and It almost worked. But its returning only the last row in the object. but not not all the rows. here is the code I using. Can you please let me know where to modify so that I can get all the rows instead of only last row in mrvs.

var mrvs = grRitm.variables.test_copy;
var rowCount = mrvs.getRowCount();
gs.log('Naveen ' +mrvs+'Rows == '+rowCount);
for (var j = 0; j < rowCount; j++) {
var row = mrvs.getRow(j);
var var1 = row.test_1;
var var2 = row.test_2;
responseObj ={
"test_1":row.test_1,
"test_2":row.test_2,
};
}
}

gs.log('FInal OBJ == '+responseObj);

return responseObj;

When I print responseObj its giving an output as below in postman.

{
"result": {
"test_1": "Haridasu",
"test_2": "Naveen"
}
}

and When I print 'mrvs' its giving me all the rows and values in an array as below.

Naveen [ {
"test_1" : "10",
"test_2" : "20"
}, {
"test_1" : "30",
"test_2" : "40"
}, {
"test_1" : "44",
"test_2" : "45"
}, {
"test_1" : "kumar",
"test_2" : "Test"
} ]

Rows == 4

When I return mrvs, it theowing an error cannot map object as mrvs is an array.

Can please help in getting all the rows on the object that is returned.

Thanks,

Naveen.