How to query by display values of reference fields?

[balu3]

Hi All,

How can we query by display value of fields, especially field of type reference?

We are planning to implement an advanced search feature in Service Portal. This would facilitate for specific search queries, whose end results are quite different from using a simple text search as it works.
So, in advanced search section we have a type selector which facilitates the user to restrict the type of search to a specific section (table to be more specific).

Over there, the users can select specific filters using autocomplete feature. Such as say Knowledge Base for Knowledge table.

Here is the problem.
For the autocomplete script to be universal, we need to query the table based on the display value of the table. In the below code, what should be present in place of <display_field> is what I'm seeking for more than 3 days.

var getResults = function (table, field, searchText) {
 var resultArray = [];

 var gr = new GlideRecord(table);
 gr.initialize();
 var type = gr.getElement(field).getED().getInternalType();
 if (type == 'reference') {
   var queryTable = gr[field].getReferenceTable();
   var grQuery = new GlideRecord(queryTable);
   grQuery.addQuery(<display_field>, 'CONTAINS', searchText);
   grQuery.query();
   while (grQuery.next()) {
     resultArray.push({ label: grQuery.getDisplayValue(),
                        value: grQuery.sys_id.toString() });
   }
 } else {
   gr.addQuery(field, 'CONTAINS', searchText);
   gr.query();
   while (gr.next()) {
     resultArray.push({ label: gr[field].getDisplayValue(),
                        value: gr[field].toString() });
   }
 }
 return resultArray;
}

Well, this is actually necessary because the display value can be changed anytime by Admins, and the script should automatically work for the changes made.

Please help.

Thanks

Balu

[Asbj_rn · Accepted solution]

Hi Balu, 

In order for getting this to work you will need to find the actual field that is the display value before your query for the field.

try looking in the sys_dictionary table for fields from the table. make sure to handle the case were nothing configured manually.

How ServiceNow finds Display Values (link😞

Reference fields look for the display value in the following order:

1. A field with display=true in the system dictionary on the lowest sub-table for extended tables.
2. A field with display=true in the system dictionary on the parent table.
3. A field named name or u_name.
4. The Created on field of the referenced record.

In code you could do something like this just prettier 😉

var getResults = function (table, field, searchText)
{
    var resultArray = [];

    var gr = new GlideRecord(table);
    gr.initialize();
    var type = gr.getElement(field).getED().getInternalType();
    if (type == 'reference')
    {
        var queryTable = gr[field].getReferenceTable();
        var grQuery = new GlideRecord(queryTable);

        // Lookup default value.
        var grDict = new GlideRecord("sys_dictionary");
        grDict.addEncodedQuery("name=" + queryTable + "^display=true");
        grDict.query();

        var DisplayField;
        //only one display value is allowed.
        if (grDict.getRowCount() > 0)
        {

            grDict.next()

            DisplayField = grDict.getValue("column_name");
        }
        else
        {
            //add further logic to find get defaults in case nothing is set manually
            //DisplayField = system defaults
        }

        //user whet ever you find to be the display value.
        grQuery.addQuery(DisplayField, 'CONTAINS', searchText);
        grQuery.query();
        while (grQuery.next())
        {
            resultArray.push({
                label: grQuery.getDisplayValue(),
                value: grQuery.sys_id.toString()
            });
        }
    } else
    {
        gr.addQuery(field, 'CONTAINS', searchText);
        gr.query();
        while (gr.next())
        {
            resultArray.push({
                label: gr[field].getDisplayValue(),
                value: gr[field].toString()
            });
        }
    }
    return resultArray;
}

[balu3]

Hi Asbjørn,

Thanks a lot.

I hoped there was another way to get the display field from the GlideRecord itself.

Like increasing the number of queries will slow down the process (although not evident in case of DB operation from application server environment, but still).

Also if there was a method provided by ServiceNow, they would have optimized the approach, or will optimize it going forward. So I'm kinda hesitant to write custom logic's or queries unless it is really required.

Are you aware of any inbuilt methods for getting the display field name.

Thanks

Balu

[SakshiA28791999]

I am also facing same issue,. Fo assignment group in incident , reference table is sys_user_group table but it does not have display as true for any field . 

[balu3]

Hi Asbjørn,

Looks like there is no other way.

Anyways, your answer cleared my doubt. Like this is the only approach, and the script what you have given looks neat.

Thanks a lot.

Thanks

Balu