How to query by display values of reference fields?

Go to solution
balu3
Kilo Guru

‎09-05-2018 12:48 AM

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

0 Helpfuls
  • 16,919 Views
1 ACCEPTED SOLUTION

Go to solution
Asbj_rn
Mega Expert

‎09-05-2018 01:55 AM

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;
}

 

 

View solution in original post

0 Helpfuls
6 REPLIES 6

Go to solution
vijay138
Kilo Contributor
In response to Asbj_rn

‎10-07-2020 05:23 PM

Asbjørn, what can be done if table has no dictionary with display true like sys_user_group, still it shows name in the display wherever we are referencing. 

0 Helpfuls

Go to solution
christianmehrin
Tera Contributor

‎04-01-2020 05:56 AM

for future reference - how to identify display column. 

function getDisplayColumn(tableName) {
	var gr = new GlideRecord("sys_dictionary");
	gr.addQuery("name", tableName);
	gr.addQuery("display", true);
	gr.query();
	if (gr.next()) {
		if (gr.hasNext()) {
			return undefined; // not unique
		}
		return gr.element.toString();
	}
	
	var tu = new TableUtils(tableName); 
	var tables = tu.getTables(); 
	var arrayUtil = new ArrayUtil();
	var parentTables = arrayUtil.convertArray(tables);
	if (parentTables.length > 1) {
		var answer = getDisplayColumn(parentTables[1]);
		if (answer) {
			return answer;
		}
	}
		
	gr = new GlideRecord(tableName);
	gr.initialize();
	if (gr.isValidField("name")) {
		return "name";
	}
	if (gr.isValidField("u_name")) {
		return "u_name";
	}			
	return "sys_created_on";
}

This will not take care of references being display value.

0 Helpfuls