Field map script with glide record

E_19 · ‎04-07-2017

Hi All,

I have a Transform map field map script running against core_company table. For each record that is imported I need to check against 'ast_contract' tbl and retrieve the vendor_contract number and add it to the u_sla field.

The ast_contract table data

u_navision	Vendor contract
104365	R0260
104360	R0270
104359	R0280
104355	R0220
104354	R0230
104353	R0240

I cannot figure out how to iterate through the result set and update u_sla for each record with the unique number.

answer = (function transformEntry(source) {

var gr = new GlideRecord("ast_contract");

var vend = gr.getDisplayValue('vendor_contract');

gr.addQuery('u_navison',source.u_nr.toString());

gr.query();

if(gr.next()){

target.u_sla = gr.vendor_contract;

}

return ""; // return the value to be put into the target field

})(source);

Any suggestions greatly appreciated.

Thanks,

Ellie

Brian O_Donnell · ‎04-07-2017

I would start adding some logging statements so that you know what you are actually dealing with:

answer = (function transformEntry(source) {   

   gs.log(new GlideDateTime().getNumericValue() + ' - ' + source.<some unique identifiable field> + '; having u_nr = ' + source.u_nr.toString(),'ImportSet');

   var gr = new GlideRecord("ast_contract");   

   gr.addQuery('u_navision', source.u_nr.toString());   

   gs.log(new GlideDateTime().getNumericValue() + ' - ' + gs.getEncodedQuery(),'ImportSet');

   gr.query();

   var returnVal = '';

   if(gr.next()){   

       returnVal = gr.vendor_contract.toString();   

   

   gs.log(new GlideDateTime().getNumericValue() + ' - returnVal = ' + returnVal,'ImportSet');

   

   return returnVal;

})(source);

you'll find the logging in the script logs with source of "ImportSet"

that will give you an idea of what is coming in and how it is parsing it.

I think i fixed the typo I knew of in the script, but there may be others - especially with the field names since I was just going by what you had presented.

View solution in original post

Brian O_Donnell · ‎04-07-2017

Assuming that the source.u_nr value is the same as the items in the u_navison field on your ast_contract table, I think what you are looking for is:

answer = (function transformEntry(source) {


   var gr = new GlideRecord("ast_contract");

   gr.addQuery('u_navison', source.u_nr.toString());

   gr.query();

   if(gr.next()){

   return gr.vendor_contract;

   }


})(source);

E_19 · ‎04-07-2017

Hi Brian,

I have tried similar to the above before and the u_sla all have the same value i.e.. the first value that is found.

I just used your code and tried it again but the results are like below all the same vendor_contract number

Any other idea?

Thanks,

Ellie

Brian O_Donnell · ‎04-07-2017

Try changing the end:

answer = (function transformEntry(source) {   

   

   var gr = new GlideRecord("ast_contract");   

   gr.addQuery('u_navison', source.u_nr.toString());   

   gr.query();   

   if(gr.next()){   

   return gr.vendor_contract.toString();   

   } else

   return '';

   

})(source);

E_19 · ‎04-07-2017

Hi Brian,

No change I update the end as suggest but still the same vendor number for all records.

I doubled checked my load files and the ast_table and they definitely have different vendor number for customers.

Any other suggestion?

Ellie