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

[Ankur Bawiskar]

Thanks for informing.

I have edited it now

[Priya151]

Hi Ankur,

I have a requirement to attach a csv standard template in the RITM when the request is raised with the variable values entered in the catalog item at the time of placing an order.

Can you please suggest how I can achieve this?

Regards,

Priya

[Ankur Bawiskar]

Hi,

This blog should help you and you can enhance it as per your requirement further

Regards
Ankur

[Priya151]

Hi Ankur,

I have tried this blog and its working but the csv template which is getting attached that we want to have a standard template which can be shared to OKTA. 

How to attach the standard template as attached?

In the catalog item there are only few variables which values should be added in the template while attaching in RITM.

Regards,

Priya

import_csv_template_TCS_Revathi_Pichumani (1).csv

[Ankur Bawiskar]

Hi,

I would suggest to open a question so that other members can also help you

Regards
Ankur

[Priya151]

Sure will do it

[Varun41]

Hi Ankur,

Thanks for this blog. It is very helpful!! 

So can you tell me the changes that need to be done if I want excel to be attached instead of csv.

Thanks,

Varun

[Ankur Bawiskar]

You are welcome.

Do remember to bookmark as well

Regards
Ankur

[Ankur Bawiskar]

It's would only work for csv

For generating excel I am not much sure as csv is just like text file but excel structure is different

[Pooja Sharma2]

Hi Ankur,

Thanks for the blog,it is very helpful.Can you please suggest if we have possibility to exact the variables in pdf as well.

Thanks in advance.

Regards

Pooja