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

[Todd O]

Cory,

Once again, your answers are spot on and go beyond being helpful.   Thanks for the detailed explanation and link references.   My question is fully answered plus some. Have a great day.

Todd

[Todd O]

Here is one more question that is very much inline with the same topic.   I have an extension table that extends change_request (e.g., change_request_todd).  

So, my extension hierarchy is change_request_todd extends change_request which extends task.

Now, I want to do that same thing this post is all about, that is I want to send 2 and 4 hours reminder emails for my custom change requests in the change_request_todd table.   I do NOT want to execute the code if it's a regular CR from the normal change_request table.  

Once again, I think my scheduled script starts with sysapproval_approver table. I see that has a relationship to the task table through approver column.  

So, do I have to join from "sysapproval_approver" to "task" to "change_request" to my "change_request_todd" table to ensure I only have the CRs I'm looking for? I've tried a few approaches but none of them are working.   Thanks in advance.

Todd

[Slava Savitsky]

Hi Cory,

Have you had a chance to find out what setQueryReferences is meant for? Thanks.

[coryseering]

Hi Slava,

I did. It's used for reducing the total number of queries for related records, when you are getting a large dataset back, and there are reference fields on those records. It tells the system to get your records in the primary set, and then does a single query for the related records (one query per reference field) for those records which are referenced in your result set.

However, there are several factors that also come into play:

1. The size of your data set.

2. The number of unique references.

3. The size of the tables being queried.

4. Certain attributes on the related tables.

5. Other query hints that apply due to the above.

At this point, I don't think it's a particularly useful GlideRecord method to call unless you have worked closely with a team member from our performance Support team, identified a slow operation caused by large datasets with multiple references that also need to be queried, and investigated the query hints already being applied to that to see if this makes any difference.

I recommend ignoring it. It's not going to be a panacea of performance savings, and has a fairly limited set of appropriate use-cases.

[Slava Savitsky]

Hi Cory,

Thanks for the information. Does it means that — if not set explicitly — this option defaults to false?