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

how many record it should create ?

Richard P · ‎10-09-2019

7770

Harsh Vardhan · ‎10-09-2019

try to use GlideRecordSecure class.

give a try

var gr = new GlideRecordSecure("sc_cat_item");

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

Richard P · ‎10-09-2019

still same...thanks...bizarre aint it.

I know its iterating because if I put

var gr = new GlideRecord("sc_cat_item");
var grNew = new GlideRecord("io_set_item");
gr.addEncodedQuery('sc_catalogs=67ea87b21b448810ea17dd3fbd4bcb29');
gr.query();

while (gr.next()) {

gs.print(gr.name)

}

on its own it lists all 7k catalog items.

if I add that line to the above script, or an of the suggestions...it only runs once
and reports this

*** Script: Phishing Diamond 3 Montly 501-1000 Seats
[0:00:00.076] Total Time