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!

[valeriemorris]

It's still just doing one row at a time, but now it's adding the number to the end...

[SanjivMeher]

Thats how it works in a while loop. You can update one by one.

Add below statement if you dont want to run the workflow.

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;

item.setWorkflow(false);

count++;

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

item.update();

}

[anukarimikonda]

You can write Database Views, see the linl below.

Database views

[valeriemorris · Accepted solution]

Well, oddly enough, the while loop in my original script worked (updated ALL records instead of just one) when I changed my variable name from "item" to "it." I got the idea that it could be the variable name from this thread: Issue with updating multiple rows using Background script. ServiceNow is weird sometimes. Thanks for the suggestions, guys!