GlideRecord AddQuery not allowed on custom ("u_") tables?

[scottmbjorke]

I have two tables represent a parent and child relationship (one to many):

1. Feature [rm_feature], whose primary key is its 'number' column.

2. Feature Install Group [u_feature_install_grp], which has a foreign key column called 'u_feature' that holds the parent Feature number.  Note this table appears to be a custom table, given it begins with "u_"...

In a server-side script (see below), I need to loop through each parent, then through each child for the parent to grow a concatenated string from each child, then update the entire string back onto the parent (as a very de-normalized list of children).

ISSUE: When I use AddQuery on the child/custom table to get back only children for the current parent (see line 8), I get back no rows (which can't be correct because the child query uses the foreign key back to the parent table, via the u_feature column). However, if I remove line 8 (ie, just set the table and query it without any conditions), I successfully get back 599 records, which is all of them (understandably).

Anyone have any ideas why my use of AddQuery is apparently destroying my child GlideRecord query? Is there a restriction on tables starting with u_?  Is it because I'm referring to a sys_id rather than a string value? Am I just missing something completely obvious??? Something else?

Please note: I've written many successful scripts like this in the past, but this one is driving me crazy as something about it feels different than my other similar read-then-update/parent-child scripts.  Also be aware I do NOT have admin role -- I'm just writing and testing these EXTREMELY CAREFULLY without the aid of the admin console.

a) I've also tried grTEMP.addQuery('u_feature','=',FeatureID); as well as grTEMP.addEncodedQuery('u_feature=' + FeatureID);    ==> SAME RESULTS

b) I've also added .GetDisplayValue() to both the parent FeatureID and the child's FeatureID ==> SAME RESULTS.

c) I've queried the child table directly using 'View/Run' reports query builder and can find all the children records for a sample parent value, so I know for fact the data is there.

=========================

1. var gr = new GlideRecord('rm_feature');
2. gr.addQuery();
3. gr.query();   // returns all the Features
4. while (gr.next()) {                    // loop through each Feature in the parent GlideRecord
5.    var UpdateNeeded = false;
6.    var FeatureID = gr.number;   // returns a value like FETR0010101
7.    var grTEMP = new GlideRecord('u_feature_install_grp');  // create a temp child GlideRecord
8.    grTEMP.addQuery('u_feature',FeatureID);    // want all child records having the parent's FeatureID
9.    grTEMP.query();
10.    var rowcount = grTEMP.getRowCount();
11.    while ( grTEMP.next() ) {
12.           var InstallGroup = grTEMP.u_install_group.getDisplayValue();
13.           FIGList += InstallGroup + ',';
14.           UpdateNeeded = true;
15.         }
16.   }
17.   if ( UpdateNeeded ) {
18.        gr.user_input = FIGList;
19.        gr.update();
20.   }
21. }

Thanks in advance!!!!!!

[scottmbjorke]

The script is run on demand and only at a time after all data has settled down and can be written for audit purposes. I'm writing to user_input field because a) I don't have admin role and b) user_input is the only field not already being used and can accept a dynamic string as a sort of mini-log.  Lastly, again because I'm not an admin, my script runs within the Condition section of an on-demand Scheduled Report.

[Mahendra RC]

Hi Scott,

looking at your code it seems that you are looping through all the parent and then through all the child record for each parent. Hence say if you have 100 parent records and you have around 10 child record for each parent. so the above code will get all 100 records from db and then for each parent record it will get the child records from child table inside your inner while loop and this will query your child table 100 times and reduce the performance. 

1) you can direct query the child table and fetch all the records from child table where u_feature field IS NOT EMPTY in one go and then store the data for those child record in JSON object

2) you can update the parent record for the child data stored in JSON object and update the parent record.

this is will query child and parent table only once. (i.e only 2 times DB will be queried) by below code:

(function () {
	var childDataJson = {};
	var grTEMP = new GlideRecord('u_feature_install_grp');
	grTEMP.addEncodedQuery('u_featureISNOTEMPTY');    
	grTEMP.query();
	var rowcount = grTEMP.getRowCount();
	while (grTEMP.next() ) {
		var sysId = grTEMP.getValue('u_feature');
		var groupValue = grTEMP.getDisplayValue('u_install_group');
		if (!childDataJson[sysId]) {
			childDataJson[sysId] = {};
		}
		if (childDataJson[sysId]) {
			if(!childDataJson[sysId]["install_group"] && groupValue) {
				childDataJson[sysId]["install_group"] = groupValue;
			} else if (childDataJson[sysId]["install_group"] && groupValue){
				var groupList = childDataJson[sysId]["install_group"];
				groupList += ',' + groupValue;
				childDataJson[sysId]["install_group"] = groupList;
			}
		}
	}
	gs.info(JSON.stringify(childDataJson));
	var parentIncArray = [];
	for (var parentInc in childDataJson) {
		parentIncArray.push(parentInc);
	}
	gs.info("ARRAY : " + parentIncArray);

	var grParent = new GlideRecord('rm_feature');
	grParent.addEncodedQuery('sys_idIN' + parentIncArray);
	grParent.query();
	while (grParent.next()) {
		var parentSysId = grParent.getUniqueValue();
		if(childDataJson[parentSysId]["install_group"]) {
			grParent.user_input = childDataJson[parentSysId]["install_group"];
			grParent.update();
		}
	}
})();

Please let me know if you have any issue in this.

Please mark this correct/helpful, if it resolves your issue.

Thanks