Advanced Reference Qualifier for reference field

[Mari2]

Hello,

I have difficulty to setup advanced qualifier, and hope someone can give me some idea.

I have two fields called ‘Owner department’ and ‘Catgory (DX)’ on incident form. Both fields are reference field, and are referencing another table (u_corporate_dx). Depends on the value selected on ‘Owner department’, I’d like to display different options for ‘Category (DX)’.

Owner department field is displaying records, which have ‘Owner department’ tag.

For Catgory (DX) field, I’d like to display the records, which the display value selected on ‘Owner department’ equals to ‘Dependent parent’ value. For example, when NYCAK is selected on ‘Owner department’, I would like to display

 ‘Accutal WF’, ‘BizPlan NYCAK Input Forms’, and ‘NYCAK Accounting Workflows’ for ‘Categry (DX) field. The below is Corporate DX table’s records.

I was originally thinking of using combination of simple Reference Qualifier and onChange client script (field name =   owner department) below. but realized addOption does not work for reference field. And I found several community articles suggesting using Advanced Reference Qualifier. But I do not know how I can achieve this requirement using Advanced Reference Qualifier. Can someone give me some insights? Thank you.

function onChange(control, oldValue, newValue, isLoading, isTemplate) {

   if (isLoading || newValue === '') {

      return;

   }

              var newValue_text = "";

    var gr_value_name = new GlideRecord('u_corporate_dx');

    gr_value_name.addQuery('sys_id', newValue);

    gr_value_name.query();

    while(gr_value_name.next());

    {

        alert(gr_value_name.sys_id);

                             alert(newValue);

                             newValue_text = gr_value_name.u_value;

      //  break;

    }

    g_form.clearOptions('u_category_dx');

    var gr = new GlideRecord('u_corporate_dx');

    gr.addQuery('u_dependent_parent', newValue_text);

    gr.query();

    while(gr.next());

    {

        g_form.addOption('u_category_dx', gr.u_value, gr.u_value);

    }

}

[Tony Chatfield1]

Hi, the script you have posted appears to be a client script, where as a reference qualifier is a filter configured against the reference fields dictionary Reference Specifications
https://docs.servicenow.com/en-US/bundle/utah-platform-administration/page/script/server-scripting/c...
Based on your post I think you are looking for 'User reference qualifier' = Advanced and 'Reference qual' field using javascript to return an encoded query based on values in your current record.
This is untested as your fields are all custom, but something like this should work.

javascript: 'yourDependentParentField=' + current.u_owner_department.sys_id;

There are plenty of OOB ref qual examples available in your instance if you search the dictionary table for Reference qual is not empty

/sys_dictionary_list.do?sysparm_query=reference_qual!%3DNULL&sysparm_view=

[Mari2]

Hi Tony,

Thank you very much for your comment.

Unfortunately, it's not working, because It is filtering the options based on the current u_owner_department value. How can I filter with newValue of u_owner_department? Also, I need to clear option of 'u_category_dx' every time Owner department is changed.  Is there any way I can put both requirements on advanced reference qualifier? Thank you. 

javascript: 'u_dependent_parent=' + current.u_owner_department.sys_id;

[Tony Chatfield1]

Clearing a field on a form and filtering reference data are 2 different functions and I do not see combining the 2 as simple or best practice solution.
Start with an onChange() client script to clear u_category_dx, when u_owner_department is changed.
Then investigate why the reference qualifier is not functioning.

Perhaps I have misunderstood your requirements, but I have just replicated this basic structure on a PDI adding a new Incident group field with a reference qualifier based on sys_user_group 'parent' field;
and the code I supplied above works exactly as expected, set the assignment group and only children of that group are available for selection in the new field (with the ref qualifier), change the assignment group and only children of the newly selected (unsaved) assignment group are available or selection.

[Mari2]

Hi Tony,

Thanks for your comment. Sorry if my explanation was not clear. My test result seems to be different from yours. The below is the reference qualifier I created based on your advice. It is working when Owner Department value is saved (because it's seeing current value), but not when the value is changed. 

My requirements are displaying different options for Category (DX) reference field, when the selection of Owner department is changed, not when the record is saved. I just do not know how make this work. Could you show me how I can make this work for newValue of u_owner_department.getDisplayValue()? I do not mind to use any other method (other than reference qualifier) as long as it meets my requirements. Thank you!

javascript: 'u_dependent_parent=' + current.u_owner_department.getDisplayValue() + '^sys_tags.f6c24cf787176110814c217f8bbb356a='+ 'f6c24cf787176110814c217f8bbb356a';