How to extract Service Catalog variable set values and output as a csv file?

L_10 · ‎06-10-2019

Hello Community.

I am very new to ServiceNow and currently am trying to create a service catalog to order company uniform and accessaries.
The below is the sample Try-it page.

When ordered, the variable sets are displayed like the image below in the Requested Item page.

I would like to write these variable set values into a csv file to be exported.
And this is the tricky part.
I need to make the csv format somewhat like the image below (This is the order placement format of our uniform supplier company, and we cannot change it...).
"Item" comes first as the key, and writing into csv is done not per Request Item, but regularly (twice a day, morning and afternoon).

Is it possible to extract variable set values from multiple Request Items into the same csv file?

And my boss says he wants to do this without creating a new table to store these variable set values.
If possible, what is the simplest way to achieve it?

I appreciate your help.

SatheeshKumar · ‎06-11-2019

Step1 : extract the values from your ritm

step2 : pass the values into file creator.

Code to fetch variable set values from RITM:

var mrvs;
var itemID = '70506da8db002300e69dfbef2996194a';
var ritmGR = new GlideRecord('sc_req_item');
if (ritmGR.get(itemID)) {
    mrvs = ritmGR.variables.access_list;
}
gs.print(mrvs);

code to create a CSV file from backgrount script:

var output = "Number,Company,Short_Description"; //header for csv file


var table = "incident";


var recordId = ""; //using for attachment file




var gr = new GlideRecord(table);


gr.addEncodedQuery("caller_id=javascript:gs.getUserID()^active=true"); //Incident was assigned to Beth Anglin


gr.query();


var count = 0;


while (gr.next()) {


       count++;


       output += "\n" + gr.number + "," + gr.company.getDisplayValue() + "," + gr.short_description;


       if (!recordId) {


               recordId = gr.sys_id;


       }


}




gs.print(recordId);


writeAttachmentFile(output);




function writeAttachmentFile(data) {


       var attachment = new Attachment();


       var attachmentRec = attachment.write(table, recordId, "export.csv", "text/csv", data);


}

you need to apply few changes to merge thses to get the desired output.also it is possible to get many RITMs value and insert into the same file but the only blocker is it will be attached in a single record as you configured.

-satheesh

View solution in original post

SatheeshKumar · ‎06-16-2019

No, sys_id of your RITM record

L_10 · ‎06-16-2019

Sorry Satheesh, one more question.

var table = "incident";

var gr = new GlideRecord(table);

I couldn't understand why the incident table.

Is this just an example??

This service catalog is to order uniform, so maybe this shall be replaced by sc item table...?

Thank you so much.

Lit

Yogi3 · ‎06-16-2019

Another suggestion would be to pull variable set data directly from this table sc_multi_row_question_answer

Hope this helps!

SatheeshKumar · ‎06-16-2019

yes, it is example to create a file . you need to replace the same with sc_req_item record.

you cant use sc_cat_item record because your submitted values are related to requested item(sc_req_item) and not sc_cat_item.