Creating a new record in a table for every result in another.

Richard P · ‎10-09-2019

Hi Girls and Guys,

What I am attempting to do, is to assign all the 7,000+ items in a specific catalog a single variable set.

I have written a fix script (below)
and while it seems to iterate through all 7,000+ items in the sc_cat_item table, it only creates a single record on the io_set_item table.

advice where I'm going wrong?
I've played around with grNew.newRecord(); and moving the initialize around, in and out of the loop.

Thanks in advance

var gr = new GlideRecord("sc_cat_item");
var grNew = new GlideRecord("io_set_item");

gr.addEncodedQuery('sc_catalogs=67ea87b21b448810ea17dd3fbd4bcb29');
gr.query();
  
while (gr.next()) {
    grNew.initialize();
    grNew.sc_cat_item = gr.sys_id;
    grNew.variable_set = "6c62fc831b584090ea17dd3fbd4bcb6e";
    grNew.order = "100";
    grNew.insert();
}

Richard P · ‎10-09-2019

Hi All,

Thanks for the input, when I started seeing all the varying ways of achieving this and all producing exactly the same result, I started suspecting the issue did not lie in the script itself.

there's an after insert - business rule on the IO table preventing duplicate input of a variable set on the table...which I find odd since multiple catalog items can utilize the same variable set, thats the point of sets!

I disabled that business rule and off it went perfect, first try.

Thanks for all your help, maybe at least the thread gives options to people trying to find multiple ways of achieving the same insert.

I suspect somehow it tried after the fisrt insert to insert the same record again somehow, although I cannot see how in any of the suggested solutions.

View solution in original post

Harsh Vardhan · ‎10-09-2019

run this in background and check the first glide record row count , check whats the number it's showing , that many record it should create in io_set_item table.

var count=0;
var gr = new GlideRecord("sc_cat_item");
//var grNew = new GlideRecord("io_set_item");

gr.addEncodedQuery('sc_catalogs=67ea87b21b448810ea17dd3fbd4bcb29');
gr.query();
  
while (gr.next()) {
  count++;
}
gs.print(count);

Richard P · ‎10-09-2019

The count is all 7,000+ records

Harsh Vardhan · ‎10-09-2019

can you try to run this in background script ? can you also confirm according to your count it should create 7000 + record in io_set_item table ?

var gr = new GlideRecord("sc_cat_item");
var grNew = new GlideRecord("io_set_item");

gr.addQuery('sc_catalogs','67ea87b21b448810ea17dd3fbd4bcb29');
gr.query();
  
while (gr.next()) {
    grNew.initialize();
    grNew.sc_cat_item = gr.sys_id;
    grNew.variable_set = "6c62fc831b584090ea17dd3fbd4bcb6e";
    grNew.order = "100";
    grNew.insert();
}

Omkar Mone · ‎10-09-2019

Hi

Can you create an object of io_set_item inside while? Something like this -

var gr = new GlideRecord("sc_cat_item");
gr.addEncodedQuery('sc_catalogs=67ea87b21b448810ea17dd3fbd4bcb29');
gr.query();
  
while (gr.next()) {
    var grNew = new GlideRecord("io_set_item");
    grNew.initialize();
    grNew.sc_cat_item = gr.sys_id;
    grNew.variable_set = "6c62fc831b584090ea17dd3fbd4bcb6e";
    grNew.order = "100";
    grNew.insert();
}

Try this and let me know.

Regards,

Omkar Mone

Richard P · ‎10-09-2019

I was surprised it didn't throw me up a variable already defined error...but despite this, no...still only one record created.