addJoinQuery Conditions

[valeriemorris]

I am working on a background script that I'm not exactly sure how to write. It involves 4 different tables:

1. sc_cat_item
2. u_cmdb_app_manual_tasks
3. u_cmdb_sccm_packages
4. u_cmdb_app_adgroup

I added a boolean field to the sc_cat_item table called u_automated to indicate whether or not the fulfillment path for the item is automated. In order to populate the field correctly, I need to check values on the 3 other tables. There needs to NOT be an associated record in the u_cmdb_app_manual_tasks table, and there DOES need to be an associated record in EITHER u_cmdb_sccm_packages or u_cmdb_app_adgroup. The primary key field could be 'u_application_instance' since it exists on every table (although it is called u_app_instance on one or two.)

I know I need to use addJoinQuery to join the tables and then do an update that sets the field to true when the conditions are met, but I'm not sure how to write the joins as described above. If any of you can help me with this, I'd greatly appreciate it!

[SanjivMeher]

Refer the below link for join query example.

http://wiki.servicenow.com/index.php?title=GlideRecord#addJoinQuery

If you are not able to achieve it using the above, I would suggest doing an GlideRecord individually on each table.

[valeriemorris]

Thanks, Sanjiv. I had already read through that wiki and still wasn't able to figure out how to write a single script to do what I needed, so I did run the script separately on tables 3 and 4 to set to true and then on table 2 to set back to false if manual tasks exist. This should work from the total record count I'm seeing it pull, but now I'm running into a different issue. Every time I run my script, I get the total number of records that SHOULD be updated, but only one actually seems to update at a time instead of all 109. The below is a result of me running it five times:

Here is my script:

var item = new GlideRecord('sc_cat_item');

item.addNotNullQuery('u_application_instance');

//item.addJoinQuery('u_cmdb_sccm_packages','u_application_instance','u_app_instance');

item.addJoinQuery('u_cmdb_app_adgroup','u_application_instance','u_app_instance');

//item.addJoinQuery('u_cmdb_app_manual_tasks','u_application_instance','u_app_instance');

item.query();

gs.print(item.getRowCount());

while (item.next()) {

item.u_automated = true;

gs.print('Automated set to true for ' + item.name);

item.update();

}

Any ideas? What is the Catalog Item Cache it's creating?

[valeriemorris]

Update: the sys ID it's giving is for the last updated catalog item.

[SanjivMeher]

Try below

var item = new GlideRecord('sc_cat_item');

item.addNotNullQuery('u_application_instance');

//item.addJoinQuery('u_cmdb_sccm_packages','u_application_instance','u_app_instance');

item.addJoinQuery('u_cmdb_app_adgroup','u_application_instance','u_app_instance');

//item.addJoinQuery('u_cmdb_app_manual_tasks','u_application_instance','u_app_instance');

item.query();

count=0;

// gs.print(item.getRowCount());

while (item.next()) {

item.u_automated = true;

count++;

gs.print('Automated set to true for ' + item.name+'     -     '+count);

item.update();

}