addEncodedQuery is ignored

[thomaskennedy]

I have a function to track down references to a particular sys_id in various tables:

_getTableReferences: function(refTable, sysId) { }

The refTable param is like so:

{
  "table": "incident",
  "column": "location",
  "condition": "sys_created_on>=javascript:gs.beginningOfLast12Months()"
 }

I want all rows from refTable.table where refTable.column (this is always a Reference column) equals sysId, unless refTable.condition exists, in which case I want only rows also matching that condition. So this is what I wrote:

var gr = new GlideRecord(refTable.table);
gr.addQuery(refTable.column, sysId);
if(refTable.condition) gr.addEncodedQuery(refTable.condition);
gr.query();
while (gr.next()) {...}

But I find that although the condition gets added, it does not filter the results. To use my example above, incidents are included regardless of their created date.

So I tried adding the encoded query first:

var gr = new GlideRecord(refTable.table);

if(refTable.condition) {
  gr.addEncodedQuery(refTable.condition);
}
			
gr.addQuery(refTable.column, sysId);
gr.query();
while (gr.next()) {...}

But that made no difference. I've seen many posts on this general subject, but they all involve an encoded query that contains a complex condition or an OR, which mine does not. What's going on here?

[Brad Bowman]

Are you certain the other 2 parameters are working - so you're only getting incidents for the specific location?  I don't know if this will be helpful, but I tried to re-create this in a Fix Script in my PDI - using a certain caller (Fred Luddy) instead of a location.  For what it's worth, here's my script:

var refTable = '{ "table": "incident", "column": "caller_id", "condition": "sys_created_on>=javascript:gs.beginningOfLast12Months()" }'; 

getTableReferences(refTable);

function getTableReferences(refTable){
	refTable = JSON.parse(refTable);
	gs.print(refTable.condition)
	var gr = new GlideRecord(refTable.table);
    gr.addQuery(refTable.column, '5137153cc611227c000bbd1bd8cd2005');
    if(refTable.condition) gr.addEncodedQuery(refTable.condition);
	gr.query();
	while (gr.next()) {
		gs.print(gr.number)
	}
}

The results are the expected 2 incidents with that caller created in the last 12 months.  When I comment out the if line, I get all 12 incidents that he has opened.  I know that GRs ignore the addQuery/addEncodedQuery if there's something it can't interpret, but as long as yours actually contains ...javascript then a colon instead ':' as this text editor keeps converting it to, then that condition should be valid.

[thomaskennedy]

Yes I'm quite sure about the other param, the location's sysId. From my tests document: the matches (all from incident in this case) are being captured under the location they reference ("Store 297"). But they're not being filtered by date - all of those are over two years old. With the condition applied, the references block should be empty for this store.

I tried a similar fix script in our test environment (dev is down now for some reason). I'm hard-coding the sysId param on line 9.

With line 10 remarked out it returns 48 rows. With line 10 included it returns no rows, as expected. This is the same as your result.

The only other thing I've not tried is replacing the date expression with a literal.

[Kristen Ankeny]

I would double check the syntax on the encoded query. Filter a list to the query you want, right click on the breadcrumb filter and copy query, and try that.

Other possibility is that you need to add a .toString() at the end of the refTable.condition. It might be going through as an object instead of a string.

[Brad Bowman]

So it works for you in a Fix Script, but how are you using it / where is it that doesn't work?  In that test case you're most likely not supplying a refTable variable like the Fix Script, so maybe that's the difference?  If you log refTable.condition, does it show the expected value?