Create multiple RITM dor a single REQ

[Kalyani35]

Hi team, 

I have a catalog item, where user will attach an excel attachement. this excel file will have the details/columns such as first name, last name, date of joining, city, country.

Now once the user attaches the attachment and the catalog item is submitted, we want to create RITM per user mentioned in the excel file.

For example, if we have 4 users in the excel then we would like to create 4 RITMs associated with single REQ for this catalog item. Can you please suggest what is the best approach to achieve it.

thanks!

[SwarnadeepNandy]

Hello @Kalyani35,

There are two options, how I see it.

1. Create a Data Source and Transform map for the excel to the RITM. Preconfigure all the transformation rules. Then after the RITM is submitted, run a flow. In the flow, copy the attachment from the RITM to the DataSource and run the transformation. And the Transform map would create new RITMs.
2. You can use a script that runs a query on the sys_attachment table and parses the excel file data. You can use a business rule to run the script when the catalog item is submitted.

var attachment = new GlideRecord('sys_attachment'); // create a GlideRecord object for the sys_attachment table
attachment.addQuery('table_name', 'sc_req_item'); // add a query to find the attachments related to the sc_req_item table
attachment.query(); // execute the query
while (attachment.next()) { // loop through each attachment
  var excel = new sn_impex.GlideExcelParser(attachment.sys_id); // create a GlideExcelParser object to parse the excel file data
  var sheet = excel.getSheet(0); // get the first sheet of the excel file
  var rows = sheet.getRows(); // get all the rows of the sheet
  for (var i = 0; i < rows.size(); i++) { // loop through each row
    var row = rows.get(i); // get the current row
    var firstName = row.getCell(0).getValue(); // get the value of the first cell (first name)
    var lastName = row.getCell(1).getValue(); // get the value of the second cell (last name)
    var dateOfJoining = row.getCell(2).getValue(); // get the value of the third cell (date of joining)
    var city = row.getCell(3).getValue(); // get the value of the fourth cell (city)
    var country = row.getCell(4).getValue(); // get the value of the fifth cell (country)
    var ritm = new GlideRecord('sc_req_item'); // create a GlideRecord object for the sc_req_item table
    ritm.initialize(); // initialize the record
    ritm.cat_item = attachment.table_sys_id; // set the cat_item field value to the catalog item sys_id
    ritm.request = attachment.table_sys_id; // set the request field value to the catalog item sys_id
    ritm.short_description = 'New user request for ' + firstName + ' ' + lastName; // set the short_description field value based on the user name
    ritm.description = 'First Name: ' + firstName + '\nLast Name: ' + lastName + '\nDate of Joining: ' + dateOfJoining + '\nCity: ' + city + '\nCountry: ' + country; // set the description field value based on the user details
    ritm.insert(); // insert the record
  }
}

Please change the script accordingly. This is just a draft script.

Hope this helps.

Kind Regards,

Swarnadeep Nandy

[Kalyani35]

Hi Swarnadeep,

Thanks for reply. I am aware about the excel parsing.

If we take any of your approaches, then would not system already create one RITM as soon as the we submit the catalog item(as per the general nature). How can we restrict that? Also, I also have to map the information in the excel to the respective variables in the RITM. 

[SwarnadeepNandy]

@Kalyani35,

Maybe I have missed that point. I thought you would like to create the existing RITM.

In that case, I think you can create a Record Producer and Generate Record in any Task table just for tracking purpose which is not visible anywhere.

Regards,

Swarnadeep Nandy