Create requests from excel import

[rba-chris]

Hi Everyone,

I'm new to the community and eagerly learning lots about this amazing platform.

I have an excel file that I'm importing into a custom table. The excel file has 4 columns full of data and about 100+ rows of data in it. From that excel file, I need each row to automatically create a new REQ (and subsequent RITM, TASK).

I also need to populate four variables on the catalogue item as I add it to the cart from the columns in the excel sheet.

I'm starting with this code and just am unsure how to populate the catalog item's variables in the script based on the excel sheet import.

Business Rule

When: after

Insert/Update/Delete: true

Condition: <your_condition_here>

createRequest();

function createRequest() {

//Create Request

var cart = new Cart();

//substitute your cat item

var item = cart.addItem('00000000000000000000000000000000');

//Set Variables in your Cart Item

//substitute your req for

cart.setVariable(item, 'requested_for','00000000000000000000000000000000');

cart.setVariable(item, 'request_short_description', 'Created from Business Rule');

cart.setVariable(item, 'request_description', 'Created from Business Rule');

cart.setVariable(item, 'request_type', 'others');

var rc = cart.placeOrder();

cart.description = 'servicenowelite.com';

var rc = cart.placeOrder();

gs.addInfoMessage('Request Item Created: ' + rc.number);

}

Source:

http://www.servicenowelite.com/blog/2014/2/6/request-generation-methods

[Pradeep Sharma]

Hello Chris,

Please refer to Michael suggestion from the below thread.

Import REQ and Generate RITM

[rba-chris]

Hi Pradeep,

Thank you for that - I did review it and didn't see how I can update the variable I created on the request item. So, I have a PO Number variable (sc_v_po_number) in the catalog item and a PO Number column in the excel spreadsheet. As I'm scripting the import business rule, how do I set the value from the excel sheet for PO Number into the request item and the cart so that the values can be seen in the RITM and subsequent tasks?

Thanks!

Chris

[Pradeep Sharma]

Hello Chris,

Thanks for the update. Try to create import set against request table and then create request items via transformation script. The below line will help in variable mapping.

cart.setVariable(item, 'sc_v_po_number',source.u_sc_v_po_number); //Here u_sc_v_po_number referes to the field column name from import set table.

[rba-chris]

Hi Pradeep,

You have been very helpful and I now am able to generate the needed requests. There's only one issue - I'm getting extra requests now for each row in the excel sheet. One request is created correctly, and then the extra request doesn't have a RITM associated with it.

Here's the exact code:

(function transformRow(source, target, map, log, isUpdate) {

  var cart = new Cart(GlideGuid.generate(null));

  var item = cart.addItem('48f0218e131d030075c1b5104244b034');

  cart.setVariable(item, 'short_description', 'TEST CREATION BY CHRIS');

  cart.setVariable(item, 'sc_v_po_number',source.u_po_number);

  cart.setVariable(item, 'sc_v_po_name',source.u_po_name);

  cart.setVariable(item, 'sc_v_serial_number',source.u_serial_number);

  cart.setVariable(item, 'sc_v_part_number',source.u_part_number);

  var rc = cart.placeOrder();

})(source, target, map, log, action==="insert");