Generate csv file with the catalog variables and attaching to RITM record

[Ankur Bawiskar]

Recently I have see many questions regarding how to generate csv file with the catalog item variable values in it. The header row of csv should be the Variable Label and the rows should be the variable values.

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

So here is an approach on achieving this:

1) Create an after insert business rule on sc_req_item table

2) Have below script in the business rule

You can also embed the script in the Run script activity of the workflow being attached to the Catalog Item

This will generate a csv file and add it as an attachment to the RITM record

Note: Give file name as per your convenience; For example I have given catalog_variables

Note: This will work only global scope and not for custom scoped application as GlideappVariablePoolQuestionSet() is not allowed in scoped app. To make it work for scoped app you need to do following:

a) query item_option_new table with the catalog item

b) iterate over every record and get the Label from Question column and get the variable value

For Global Scope:

var ritmSysId = current.sys_id;
var set = new GlideappVariablePoolQuestionSet();
set.setRequestID(ritmSysId);
set.load();
var vs = set.getFlatQuestions();

var valuesArray = [];

var csvHeader = [];

for(var i=0;i<vs.size();i++){

var variableLabel = vs.get(i).getLabel();

csvHeader.push(variableLabel.toString());

var variableValue = vs.get(i).getDisplayValue();

valuesArray.push(variableValue.toString());

}

var csvHeaderRow = csvHeader.toString();
var valueRow =  valuesArray.toString();
var sa = new GlideSysAttachment();

var document = csvHeaderRow + "\n" + valueRow;

var ritmRec = new GlideRecord('sc_req_item');
ritmRec.get(ritmSysId);

sa.write(ritmRec, "catalog_variables.csv", "text/csv", document);

For Scoped App:

var csvHeader = [];
var valuesArray = [];

var gr = new GlideRecord('item_option_new');
gr.addEncodedQuery('cat_item=' + current.cat_item);
gr.query();
while(gr.next()){
var label = gr.question_text;
var value = current.variables[gr.name]

csvHeader.push(label.toString());
valuesArray.push(value.toString());
}

var csvHeaderRow = csvHeader.toString();
var valueRow =  valuesArray.toString();
var sa = new GlideSysAttachment();

var document = csvHeaderRow + "\n" + valueRow;

var ritmRec = new GlideRecord('sc_req_item');
ritmRec.get(ritmSysId);

sa.write(ritmRec, "catalog_variables.csv", "text/csv", document);

Note: As of now the above scripts don't take into account the multi row variable set & list collector

Thanks for reading the blog and do provide your inputs/suggestions if any.

Hope you find this article helpful. Don’t forget to Mark it Helpful, Bookmark.
Thanks,
Ankur Bawiskar

ServiceNow MVP 2020,2019,2018

My Articles & Blogs