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

rajesh9885 · ‎10-09-2019

try using a for loop

var count = [];

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 .push(''+gr.parent.sys_id);

}

for (var i = 0; i < count .length; i++)

{
You code to insert
}

Hope this helps

rajesh9885 · ‎10-09-2019

did you check if for loop works for you?

Richard P · ‎10-09-2019

hi...much the same...i added some extra debugging and this is the results

#### Compiler Stats ####
Compiles: 5,600, time: 17,263ms
Total classes: 5,641, bytecode length: 35,890,162
Total loaders created: 5641, unloaded: 32, existing: 5609
Interpreted compiles: 1,172, time: 110ms, total bytes: 1,599,888
Cache name: "syscache_expression", max: 8,192, size: 6,772, seeks: 2,420,169,228, hits: 2,420,155,655, misses: 13,573, flushed: 0, puts: 0
*** Script: 7770
*** Script: Phishing Diamond 3 Montly 501-1000 Seats
[0:00:00.067] Total Time


the 7770 is the number of the getrowcount of gr.

and the "Phishing Diamond 3 Montly 501-1000 Seats" is the single first result of the table, where it should list all items from this script (which is yours with the debugging)

var gr = new GlideRecord("sc_cat_item");
gr.addEncodedQuery('sc_catalogs=67ea87b21b448810ea17dd3fbd4bcb29');
gr.query();
  gs.print(gr.getRowCount());
if (gr.next()) {
	gs.print(gr.name);
    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();
}

Community Alums · ‎10-09-2019

Hey there,

try to add + "" after the sys_id :

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 + "";

...

Cheers,

Joro

Richard P · ‎10-09-2019

same result I'm afraid