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!!!!!!

[Brent Sutton · Accepted solution]

Hi Scott,

Is "u_feature" a reference field on the "u_feature_install_grp" table? If so you'll need to use the rm_feature sys_id rather than number (line 6 of your script). I've made the adjustments in the script below:

var gr = new GlideRecord('rm_feature');
gr.addQuery();
gr.query();   // returns all the Features
while (gr.next()) {                    // loop through each Feature in the parent GlideRecord
	var updateNeeded = false;
	var featureId = gr.getUniqueValue();   // returns sys_id of the feature
	var featureGrp = new GlideRecord('u_feature_install_grp');  // create a temp child GlideRecord
	featureGrp.addQuery('u_feature',featureId);    // want all child records having the parent's FeatureID
	featureGrp.query();
	//var rowcount = featureGrp.getRowCount(); //not sure why you are using getRowCount. This is normally not good from a performance prospective.
	
	while (featureGrp.next() ) {
		var installGroup = featureGrp.u_install_group.getDisplayValue();
		FIGList += installGroup + ',';
		updateNeeded = true;
	}
}
if ( updateNeeded ) {
	gr.user_input = FIGList;
	gr.update();
}

I've also changed some of the names of your variables and amended to use camel case i.e. camelCase. Please beware of these changes in case the variables are used further on in your script.

Let me know if it worked for you.

Brent

P.S. If my suggestion helped then please mark as helpful and/or correct so other community members can benefit from this information.

[scottmbjorke]

Thanks, Brent. I suspected issue was sys_id vs number, but I couldn't confirm it because I wasn't aware of the existence of the getUniqueValue() method. On an earlier attempt, when I tried getting the sys_id via just gr.number, the script didn't work. The column is named 'number' so why wouldn't gr.number give me the same result as gr.getUniqueValue(), or if it did give same value, I can't figure out why my script didn't work.  Oh, well.

Thanks for the tip. My script is alive and well and doing it's thing now.

Scott

[Brent Sutton]

Hi Scott,

Glad that I could help. Just checking that it was my post that solved your issue? I think you’ve selected Jim’s answer as correct.

Brent

[Jim Coyne]

As Brent mentioned, the big issue with the script is the use of the number instead of the sys_id in the filter.

However, I am curious as to why you are updating the user_input field?  Would displaying the child records in a Related List solve the issue?

Are you going to do this on an on-going basis?  If yes, what happens if a record from "u_feature_install_grp" is deleted?  The user_input field would not be updated with the above logic.

And the last question - how are you running the script?  🙂