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).

[Mohith Devatte]

@Rahul Raja Samineni can you try this log and check if its going ln side the loop ?

gs.info('generated request'+rc.sys_id);
var gr5 = new GlideRecord('sc_req_item');
gr5.addQuery('request',rc.sys_id);
gr5.query();
if(gr5.next())
{
gs.info('Inside if statement'+gr5.sys_id);
gr5.effective_dates=row[header1];
gr5.update();
}

[Rahul Raja Sami]

please check

[Mohith Devatte]

@Rahul Raja Samineni i think its going inside the loop perfectly there is some problem with the field .

what type of field is effective date  data time or just date ?

and in excel column change the format of that column to date type instead of some text type and try mapping once again 

[Rahul Raja Sami]

the effective dates field is date type 

[Mohith Devatte]

@Rahul Raja Samineni i have update the log just check if the date value is coming in the log 

and then in the excel change the type of the column to date where effective date is stored .

var gr5 = new GlideRecord('sc_req_item');
gr5.addQuery('request',rc.sys_id);
gr5.query();
if(gr5.next())
{
gs.info('Inside if statement and getting the effective date'+row[header1]);
gr5.effective_dates=row[header1];
gr5.update();
}