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

[Tamara7]

Hi, 

I have a question, how can I change the separator, I need to put a semicolon (;) on my CSV file.

Thank you for your answer.

TD

[Ankur Bawiskar]

Hi,

you require semicolon as separator for field values or for new line?

Regards

Ankur

[Tamara7]

Hi Ankur, 

I needed a semicolon as separator for field values but I solved it changing two lines of your code with .join(";").

Thank you for your answer, your code is really helpful.

Have a nice day !

Regards,

Tamara

[Sherice1]

Hello ANkur,

I tried script thank you it is creating .csv file but I have an issue

requested for and other values are not placed under properly for example i have attached document

labels with proper values are not in place, In the file I have mentioned corrected data, Please help here I think where there is comma it is taking as new value i guess,

Regards,

Chipsy

catalog_variables.csv

[Sherice1]

Hello @Tamara can you please let me know how did you seperate field values by semi colon. can you please paste code here. It will be helpfull please

[Ankur Bawiskar]

Hi,

As of now the above script doesn't take into account the list collector and multi row variable set type of variables; you would require to enhance it

Regards

[Sherice1]

Hello Ankur,

Can you please help/guide me  in enhancing code.. am new to Servicenow

Regards,

Chipsy

[Vidyasagar Anku]

I guess, it would be as below, at the place where you have 

var valueRow = valuesArray.toString();

It can be changed to,

var valueRow = valuesArray.join(';');

[Ankur Bawiskar]

Yes you can join the array with semi-colon and that should work

Regards
Ankur

[Phonsie Hevey1]

Thanks for this blog, it's very helpful!

I'm just wondering whether the following line should be

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

rather than

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

i.e. text/csv rather than test/csv. Perhaps it doesn't make a difference?