Field map script with glide record

[E_19]

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 · Accepted solution]

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.

[E_19]

Thank you Brian this has helped me to debug it. There was an underscore missing on u_nr_   at the end. I have rerun my code with the 2 corrections and now the vendor_contract number is correct for every customer.

I added your logging script to my library for future use

Thanks,

Ellie

[Dennis R]

I'm hazy on a couple of things you're doing, but a couple of things I noticed above:

1) If you're trying to update target, you'll need to run target.update(); after setting target.u_sla.

2) target isn't defined anywhere in this function, so if it's not set in a parent scope somewhere, it won't be accessible.

3) You're fetching the display value of vendor_contract, not the id.   Is this intentional?   I'm not seeing it used anywhere.   If you want to use it to populate a reference field, use getValue instead of getDisplayValue.

4) You misspelled navision in your query.

Hope this helps,

--Dennis R

P.S.   You can simplify what you have above a bit to:

var gr = new GlideRecord('ast_contract');
if (gr.get('u_navision', source.getValue('u_nr'))) {
     target.u_sla = gr.vendor_contract;
}

The get() function automagically runs a next(), so you don't need a separate call for it.   get() returns false if no record is found matching the simple criteria.

[E_19]

Hi Dennis,

Its a field map script. I just tried your script and same results as Brians the u_sla field has the same vendor_contract number.

Thanks for pointing out my typo not sure when that got in there was correct earlier.

Any idea why its not getting the vendor number for each record?

Thanks,

Ellie

[lavanya27]

Hi Ellie,

try below code

var gr = new GlideRecord("ast_contract");

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

  gr.query();

  if(gr.next()){

    answer = gr.vendor_contract;

  }

else answer="";