Automate Data import from Excel File Attachment via Catalog item along with request number

[SreeragM]

Hi all, 

So i refered this post for Automating data import from excel file which is a attachment via catalog item ( link to the article: https://www.servicenow.com/community/developer-blog/automate-data-import-from-excel-file-attachment-...) 

Based on the same i created a business rule which works fine, it does import the data from the sheet to the custom table created.

Business Rule: 

When: after

order: 100

Insert

filter: item = Import user(catalog item name)

Table : Request item [sc_req_item]

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

    // Add your code here

    var attachment = new GlideSysAttachment();

    var attached = attachment.copy('sc_req_item', current.getUniqueValue(), 'sys_data_source', 'de1ece57c340321044eddacc050131bf'); //sys_id is the id of Data source

    if (attached) {

        gs.info('Attachment Copied from: ' + current.number);

        var grImp = new GlideRecord("scheduled_import_set");

        if (grImp.get('66a412dfc340321044eddacc050131b7')) { // sys_id is of the Schdeuled import

            gs.executeNow(grImp);

            gs.info('Execution done');

        }

    }

    updateRequestNumber(current.number);

})(current, previous);

I also require the request number from the catalog to also appear in the table, so I wrote a script include which is called in the BR : updateRequestNumber(current.number);

Script include:

function updateRequestNumber(number) {

    gs.info('Test3:' + number);

    var userDemoGR = new GlideRecord('u_users_demo');//u_users_demo is the custom table created where the imported data is being stored.

    // userDemoGR.addQuery('u_request_number', '!=', number);

    //userDemoGR.addOrCondition('u_request_number', '');

    //userDemoGR.addQuery('u_request_number','');

    userDemoGR.query();

        while (userDemoGR.next()) {

            if (userDemoGR.u_request_number == '') {

                userDemoGR.u_request_number = number;

                // gs.info('[updateRequestNumber] Updating record ' + userDemoGR.sys_id +

                //     ' with request number: ' +number);

            }

            else{

                continue;

            }

            userDemoGR.update();

        }

    }

Now the issue is when i upload and submit the catalog item with the excel sheet is that the records are imported but the Number doesnt show. 
When i Upload another file then the record which is previously uploaded gets the current number but the new records goes empty.

image 1: after 1st sheet is uploaded

Image 2: after sheet 2 is uploaded

i have attached both excel sheets used

please help me find a solution

[Rafael Batistot]

Hi @SreeragM

Your Business Rule runs immediately after the catalog item is submitted → it copies the Excel attachment → triggers the scheduled import → but at that moment the data has not yet been inserted into your custom table (u_users_demo).

So when your updateRequestNumber(current.number) runs, there are either no records yet or only older ones from the previous import. That’s why:

• First upload → nothing updated (because no records yet).

• Second upload → it updates the records from the previous import (because now they exist).

 You need to update u_request_number after the import is finished.

Try use an onAfter transform script. See an example of code:

 var reqItem = new GlideRecord('sc_req_item');
    if (reqItem.get(source.sys_import_set.sys_attachment.table_sys_id)) {
        target.u_request_number = reqItem.number;
    }

[SreeragM]

@Rafael Batistot Should add this script in the data source or in the BR itself.

It would be helpful if you could give the complete process of the changes that i need to make. 

Thank you

[Rafael Batistot]

@SreeragM 

In your BL call:

updateRequestNumber(current.number);

Remove this and add a onAfter scrip in your transformap