How to populate Manager field(reference type) and effective date from excel sheet when using Glide Excel Parser.

[Rahul Raja Sami]

I am using Glide Excel parser and Cart API to create requests from the excel.

everything works fine but cannot populate Manager and effective date fields from Excel 

(function executeRule(current, previous /*null when async*/ ) {

    var gr = new GlideRecord('sys_attachment');
    gr.addQuery('table_sys_id', current.sys_id);
    gr.query();
    gs.log('request ---------------' + current.sys_id);
    if (gr.next()) {
 
        var parser = new sn_impex.GlideExcelParser();
        var attachment = new GlideSysAttachment();
        // use attachment sys id of an excel file
        var attachmentStream = attachment.getContentStream(gr.sys_id);

        parser.parse(attachmentStream);

        //retrieve the column headers
        var headers = parser.getColumnHeaders();
        var header1 = headers[0];
        var header2 = headers[1];
        var header3 = headers[2];
        var header4 = headers[3];
        var header5 = headers[4];
        var header6 = headers[5];
        var header7 = headers[6];
        var header8 = headers[7];
        var header9 = headers[8];
        var header10 = headers[9];

        while (parser.next()) {
            var row = parser.getRow();
            var message = "";
            var gr1 = GlideRecord('sys_user');
            gr1.addQuery('name', row[header3]);
            gr1.query();
            if (gr1.next()) {

                var success = true;
                var cartId = GlideGuid.generate(null);
                var cart = new Cart(cartId); //calling the cart API  
                var item = cart.addItem('52d4943187b6e810d0d4ca27cebb3521');
                cart.setVariable(item, 'requested_by', gr1.sys_id);
                cart.setVariable(item, 'first_name', row[header2]);
                cart.setVariable(item, 'last_name', row[header3]);
                cart.setVariable(item, 'access', 'AD');
                cart.setVariable(item, 'off_user_email', row[header4]);
                cart.setVariable(item, 'manager', row[header9]);
                cart.setVariable(item, 'effective_date', row[header1]);

                var rc = cart.placeOrder();
             
            }
        }
    } 
})(current, previous);

the excel sheet contains 10 columns. (9th column is Manager and 1st column is Date).

[Rahul Raja Sami]

Hi Mohith,

this is not what I am expecting

if I do like this i will populate the manager from the user record but not from the sheet but I want to update the record from the sheet . 

I worked on that, it is fine now

but can you help me with populating the date?

[Mohith Devatte]

@Rahul Raja Samineni try this before after placing the order .I recently faced the same issue and it worked when i did like this 

After placing the order try this

Also remove this line  cart.setVariable(item, 'effective_date', row[header10]);

 var rc = cart.placeOrder();
var gr = new GlideRecord('sc_req_item');
gr.addQuery('request',rc.sys_id);
gr.query();
if(gr.next())
{
gr.effective_date=row[header10];
gr.update();
}

I hope this will solve the issue 

[Rahul Raja Sami]

Hi Mohith

before it showed some date but now it is showing blank.

    var gr = new GlideRecord('sys_attachment');
    gr.addQuery('table_sys_id', current.sys_id);
    //         gr.addEncodedQuery('table_sys_id=' + current.sys_id + '^ORtable_sys_id='+ email.sys_id);
    gr.query();
    gs.log('request ---------------' + current.sys_id);
    if (gr.next()) {
        gs.log('In if loop');


        var parser = new sn_impex.GlideExcelParser();
        var attachment = new GlideSysAttachment();
        // use attachment sys id of an excel file
        var attachmentStream = attachment.getContentStream(gr.sys_id);

        parser.parse(attachmentStream);

        //retrieve the column headers
        var headers = parser.getColumnHeaders();
        var header1 = headers[0];
        var header2 = headers[1];
        var header3 = headers[2];
        var header4 = headers[3];
        var header5 = headers[4];
        var header6 = headers[5];
        var header7 = headers[6];
        var header8 = headers[7];
        var header9 = headers[8];
        var header10 = headers[9];

        //print headers
        //     gs.info(header1 + " " + header2 + " " + header3 + " " + header4);

        while (parser.next()) {
            var row = parser.getRow();
            var message = "";
            var gr1 = GlideRecord('sys_user');
            gr1.addQuery('name', row[header3]);
            gr1.query();
            if (gr1.next()) {
                //
                var gr2 = GlideRecord('sys_user');
                gr2.addQuery('name', row[header10]);
                gr2.query();
                if (gr2.next()) {

                    var gr3 = GlideRecord('cmn_location');
                    gr3.addQuery('name', row[header7]);
                    gr3.query();
                    if (gr3.next()) {
                        var gr4 = GlideRecord('cmn_location');
                        gr4.addQuery('name', row[header8]);
                        gr4.query();
                        if (gr4.next()) {


                            var success = true;
                            var cartId = GlideGuid.generate(null);
                            var cart = new Cart(cartId); //calling the cart API  
                            //var cart = new Cart();   //calling the cart API  
                            var item = cart.addItem('52d4943187b6e810d0d4ca27cebb3521');
                            cart.setVariable(item, 'requested_by', gr1.sys_id);
                            cart.setVariable(item, 'first_name', gr1.first_name);
                            cart.setVariable(item, 'last_name', gr1.last_name);
                            cart.setVariable(item, 'access', 'AD');
                            cart.setVariable(item, 'location', gr4.sys_id);
                            cart.setVariable(item, 'off_user_email', row[header4]);
                            cart.setVariable(item, 'reporting_manager', gr2.sys_id);
//                             cart.setVariable(item, 'effective_dates', row[header1]);
                           
                            var rc = cart.placeOrder();
							var gr5 = new GlideRecord('sc_req_item');
gr5.addQuery('request',rc.sys_id);
gr5.query();
if(gr5.next())
{
gr5.effective_dates=row[header1];
gr5.update();
}

please see this

[Mohith Devatte]

@Rahul Raja Samineni are you sure the name of the effective date field is correct?

should not it be this "effective_date" and also map the correct the column which is row[header1].Is this the second column in the excel ?

gr5.effective_date=row[header1];

[Rahul Raja Sami]

ya it is correct only gr5.effective_dates = row[header1];

i have updated the field names as per the sheet.