How to have a filtered query on a scope application for a referenced field?

[Todd O]

I have a scheduled job within custom application. I want to query the sysapproval_approver table for particular records that I'm interested in.   That is, I only want records where the sysapproval column value starts with "XYZ."   E.g., I want XYZ0001, XYZ0002, etc.   So, I created this script...

var gr = new GlideRecord('sysapproval_approver');

gr.global.setDisplayValue(true); <-- because I'm working in a scoped application, this function is NOT ALLOWED! So, I removed it.

gr.addQuery('state','requested'); <-- Just having this filter brings back a lot of rows.

gr.addQuery('sysapproval','STARTSWITH', 'XYZ'); <-- Adding this brings back 0 rows even though I have records starting with XYZ.

//   Also, sysapproval is a referenced field to user so it has sys_id values which is why I wanted to use the setDisplayValue to true.

gr.query();

So, since I cannot use setDisplayValue in a scoped application, how to I add a referenced column to a filter in my query.   And, if I just use the filter STARTSWITH, it finds zero records as I think it's trying to compare against the "sys_id" instead of "XYZ."   Any suggestions for this appreciated. Thanks.

Todd

p.s., When I print out the values of the columns I want to filter on, you can see that these referenced fields are bringing back the sys_id. How do I query on referenced fields in a scoped application????

sysapproval: 12a7f4040fcc12008e030dbce1050e6c, approver = 6816f79cc0a8016401c5a33be04be441

[coryseering · Accepted solution]

Hi Todd,

You can dot-walk in the query.

var gr = new GlideRecord('sysapproval_approver');
gr.addQuery('approver.name','STARTSWITH','Change');
gr.query();


gs.info(gr.getRowCount());

Output in my test: 4 (the expected value);

I don't think gr.global.setDisplayValue(true) is valid. You might be thinking of setQueryReferences, but you are correct that it is not available in scoped apps (and I'm not sure it actually queries display values of reference fields);

https://wiki.servicenow.com/index.php?title=GlideRecord#setQueryReferences

I've never seen setQueryReferences in practice. Tomorrow when I get to work, I'll check out the underlying code and see what it actually does. Regardless, it's not part of the scoped API at this time.

[Todd O]

Cory, your answer was spot on. I put that into my query and it worked perfectly.   I guess I didn't fully grasp how to use dot notation in my query until now.   You've helped me tremendously and I'm grateful. Thanks.

Todd

[Todd O]

Cory,

One more quick question very much related to this.   This new scenario is a string comparison instead of a query?   I've tried wild cards to see if a value starts with a certain string and it always comes back as not a match. See example below. I'm sure it's something very simple wrong as I'm still getting used to SN scripting syntax. Thanks in advance.

Todd

if (gr.getDisplayValue() == 'XYZ%') {
       gs.info("found one"); // This never evaluates to true even though I know records exist (e.g, XYZ0001, XYZ0002)
}

[coryseering]

Hi Todd,

There are a couple of assumptions in your code which need to be corrected. The % sign works as a wildcard in MySQL LIKE queries, but GlideRecord doesn't support them. You can do STARTSWITH, ENDSWITH, and CONTAINS (which is equivalent to 'LIKE %YOUR_STRING%'). You never actually use the % wildcard in a GlideRecord Query.

Your code is doing the query, then trying to compare the value. The comparison is being done in JavaScript, so you need to use JavaScript's string comparison functions. You want to know if the display value of the field starts with "XYZ", so in JavaScript a quick way to do that is with indexOf:

if (gr.getDisplayValue().indexOf("XYZ") === 0)
         gs.info("found one")

This only works if you are actually getting the display value of the record you found, not if you're trying to match on the display value of one of it's fields (like the query you wrote earlier looks for). If you want to match on a particular field's display value, pass in the field name:

if (gr.getDisplayValue("approver").indexOf("XYZ") === 0)
       gs.info("found one"); 

You can find more about JavaScript string comparison here:

String.prototype.indexOf() - JavaScript | MDN

And of course, JavaScript has a regex engine as well:

RegExp - JavaScript | MDN

I hope that helps. Just remember that you're never writing SQL, and sometimes you are doing comparison/matching in your query, and sometimes doing it on a result in JavaScript. Always keep in mind the difference between the record you are operating on, and the records represented in the reference fields on that object as well.