Reference qualifier not working on variable on portal

[Tariq Alshara]

Dear ServiceNow community, I hope all is well!

I am currently facing an incredibly annoying issue with a variable on a catalog item

I have a "Lookup select box" variable on a Catalog Item called "City" that is looking up its values from  a custom table I have created, this variable needs to present choices based on a selection from another "Lookup select box" variable called "Project" which is also looking up its values from the same custom table. 

I have built a reference qualifier to make sure that the "City" variable presents the values that have the selected "Project" against them in my custom table, here is my reference qualifier and data from both the custom table and the variables on the portal:

javascript:"u_project="+current.variables.project_1_12+"^ORDERBYorder";

As you can clearly see, although I have the "ORDERBYorder" clause added in my reference qualifier, it is being completely disregarded, the values are being sorted in a different manner than the order that I want and no matter what i try it is not working

I have tried creating a custom ordering field and using it but it did not work, I have also checked all the types of my fields on my custom table, the "order" field is an Integer and both the "Project" and "City" are of type Translated Text

What is also weird is that when I attempt using the ordering clause solely in my reference qualifier, it sorts the values by order, here is the syntax I used and how it affects in the portal:

javascript:"ORDERBYorder";

Any clue why this is happening and how I can fix it?

[BharathChintala]

@Tariq Alshara 

Both scenarios won’t work. Keep condition in reference qualifier

for order write before query BR on custom table

this will be only option for you

[Tariq Alshara]

@BharathChintala ,

Mind explaining why they won't work? I would like to know the reason for this issue

I will try the BR and reply to this comment

[BharathChintala]

@Tariq Alshara 

When you write query with fields system understands filter records. And other things like orderby kindof things also it will understand but when you club them system will not understand the format.

I suggest you to go with below options

1. Add the "ref_ac_order_by=<field_name>" to Attributes in the reference field.
2. Create the before query business rule as below:
   (function executeRule(current, previous /*null when async*/) {
      current.orderByDesc('<field_name>');
   })(current, previous);

sometimes you need to write both.

[Tariq Alshara]

@BharathChintala ,

1. I've tried that but it did not work because my variable is a "Lookup select box" and not a "Reference" 

2. I just tried this before query business rule but it did not work

Any other ideas?

p.s: I cannot change my variable type to "Reference" as I have another variable pointing towards the same table

[Shubham_Shinde]

Hello @Tariq Alshara ,

Came across this article (KB0695435), and as per this article the Dependent variable of type "lookup select box" does not honour ORDERBY on reference qualifier in Service Portal. 

And in the workaround it has been suggested to use reference variable along with "ref_ac_order_by" attribute instead of dependent Lookup select box variable.

Regards,

Shubham