Reference Qualifier With Related List Query

[Yifeng Zhang]

I Found this very interesting inconsistancy over Encoded Query.

In Kingston, I have a filter on that table that's based on one of its field AND a condition on a related list.

This can be accomplished just through the v3 list filter or via scripts using GlideRecord.addEncodedQuery. I've tested in a fix script.

However, when I copy the exact encoded query into an Advanced Reference Qualifier for filtering the reference field, all of sudden it no longer works.

Let me give you an example:

Let's have a [base] table with [faction] field.

And another related table [structure] that has a reference field[u_base] to base.

consider a filter:  I want bases that belongs to RED faction that has at least one structure.

The encoded query: "faction=RED^RLQUERYstructure.u_base,>=1^ENDRLQUERY"

Keep in mind this is indeed the correct query, I got mine from copying directly from the filter.

This query will work either in the v3 List filter, or if you script using GlideRecord.addEncodedQuery() but it stops working if you put it as a reference qualifier in a reference field for [base]. What will happen is the filter will only execute things before the "RLQUERY", so it returns all the red bases.

But anywhere else it works just fine..

 This is a mock up example where I understand I could just work around it by getting rid of the related list, but I do require to work with related list.

Now the funny part is that I know the Ref Qualifier supports "RLQUERY", if I only filter either condition by itself, it will work perfectly, it is only when I combine the two conditions, that it no longer executes anything after the RLQUERY.

So Can anyone tell me if there's something I didn't configure right? is there a work around this issue? Is this a known issue? Are we looking to fix this?

 EDIT:

I've found the solution myself. 

So in the reference qualifier you can use a script include, and the script include and query database view and return a string containing lots of sys_ids.

So bascially, you prepare your query that involves multiple tables into a single database view. and your script include on the refernce field queries that database view and return a string of related sys_ids and attached to something like "sys_idIN" + sysids

And put this as ref qual then the reference field just works correctly... 

This is not the most beautiful solution, hope it had OOB support for such requirement but at the moment this is the best work around avaliable..

The benefit of this is that, it does limit the performance impact on the system. because the database view is only queried once, comparing a more straightforward solution of putting a calculated bit on every record to query the condition, which will run N times.

[The SN Nerd]

Hey,

I just came across this myself!
I figured it would be a simple, powerful way to improve selection options for our Service Portal.

After testing in Xplore by copying an encoded query using list v2 condition builder, I found it didn't work as a reference qualifier!

My next thought was to do as you have suggested.

Good to know it will work!

I second your request to make this behavior work from the encoded query!

[Niclas]

I have just observed the same issue with the following update: The related list query in a reference qualifier will work if you only use the related list query. So RLQUERYstructure.u_base,>=1^ENDRLQUERY will work, but faction=RED^RLQUERYstructure.u_base,>=1^ENDRLQUERY will ignore the related list query. I observered that for a scoped catalog variable. The behavior seems to be the same in backend as in portal. 

Obviously this is a bug. I created a HI ticket and referenced your post here. Maybe it will get fixed, let's see! 🙂