Fix Script to update value from another table

[Sheryl Lyke1]

I need to update multiple values in a table with values from a newly imported table. This is a one time fix so I'm trying with a fix script. When I run it, no values are updated but there are also no errors.

I imported the new vendor numbers into a table (u_updatecanadavendors) that has current vendor number and new vendor number. I just want to update the vendor_code in the existing talbe (ap_vendor) with the new vendor number where the current vendor numbers match. I can't do this with a transform map because it's the foreign key that is being updated.

Is there a better approach to this. It seems like it should be a simple request. I'm new-ish to SN and have more experience in SQL. I want something similar to an update table query where the tables are joined on foreign key of current vendor number.

var gr = new GlideRecord('u_updatecanadavendors');

gr.addQuery('u_erp','Canada');

gr.query();

while(gr.next()){

  var vend = new GlideRecord('ap_vendor');

  vend.addQuery('vendor_code','gr.u_vendor_number');

  vend.query();

  if(vend.next()){

        vend.vendor_code = gr.u_new_vendor_number;

        vend.update();

}

}

*Duplicate question worded differently and changed script.

[Jon Barnes · Accepted solution]

check this line:

vend.addQuery('vendor_code','gr.u_vendor_number');

it should be

vend.addQuery('vendor_code',gr.u_vendor_number);

I can't confirm this will fix it yet cause I don't know the structure of those tables, but try that and see if it works.

[Sheryl Lyke1]

Good catch. But still no luck

[Jon Barnes]

I would recommend putting some output in there to see what is going wrong.   try this and see if the output gives you clues...

var gr = new GlideRecord('u_updatecanadavendors');

gr.addQuery('u_erp','Canada');

gr.query();

gs.print('total rows from source table: ' + gr.getRowCount());

while(gr.next()) {

  var vend = new GlideRecord('ap_vendor');

  vend.addQuery('vendor_code', gr.u_vendor_number);

  vend.query();

  if(vend.next()) {

        vend.vendor_code = gr.u_new_vendor_number;

        vend.update();

    } else {

        gs.print('no match found for vendor number: ' + gr.u_vendor_number);

    }

}

[Abhinay Erra]

try this

var gr = new GlideRecord('u_updatecanadavendors');

gr.addQuery('u_erp','Canada');

gr.query();

while(gr.next()){

  var vend = new GlideRecord('ap_vendor');

  vend.addQuery('vendor_code',gr.u_vendor_number);

  vend.query();

  if(vend.next()){

        vend.vendor_code = gr.u_new_vendor_number;

        vend.update();

}

}