Help with Reference Qualifier – Search by Multiple Fields (Name, Code, Country)

[abhisheksat]

Hi everyone,

I have a catalog item that includes a reference field referencing the core_company table. By default, the lookup works fine when searching by the company name, but now my client wants to be able to search by company name, company code, and country.

I tried configuring the reference auto-completer as follows:

ref_auto_completer = AJAXTableCompleter
ref_ac_columns = name;u_company_code;u_country_ref
ref_ac_columns_search = true

However, this doesn’t seem to be working at all — it still only searches by the name field.

Has anyone successfully configured a reference field (especially in a catalog item) to search across multiple columns like this?
Any tips or examples would be greatly appreciated!

Thanks in advance!

[abhisheksat]

Didn't work

[Ravi Gaurav]

Hi @abhisheksat 

You’ve correctly tried to configure the reference field to search by multiple columns (name, u_company_code, u_country_ref), but in Catalog Items, the reference auto-completer configuration doesn’t always behave the same as it does on platform forms. The platform form honors ref_ac_columns, but the Service Catalog UI has limited support for this attribute.

Use a Client Script with GlideAjax (Works in Catalog / Portal)

To achieve multi-column search in catalog items or portal, you’ll need to build your own search logic.
Here’s how:

Step 1: Create a Script Include (Server-Side, Client Callable)

var CompanyLookupHelper = Class.create(); CompanyLookupHelper.prototype = Object.extendsObject(AbstractAjaxProcessor, { getMatchingCompanies: function() { var term = this.getParameter('sysparm_search_term'); var results = []; var gr = new GlideRecord('core_company'); gr.addEncodedQuery('nameLIKE' + term + '^ORu_company_codeLIKE' + term + '^ORu_country_refLIKE' + term); gr.setLimit(10); gr.query(); while (gr.next()) { results.push({ sys_id: gr.getUniqueValue(), label: gr.getDisplayValue() + ' (' + gr.u_company_code + ', ' + gr.u_country_ref.getDisplayValue() + ')' }); } return JSON.stringify(results); } });

Step 2: Add a Catalog Client Script (onChange or onKeyUp Event)
Attach this to your reference variable (let’s call it company😞

function onChange(control, oldValue, newValue, isLoading) { if (isLoading || newValue == '') return; var ga = new GlideAjax('CompanyLookupHelper'); ga.addParam('sysparm_name', 'getMatchingCompanies'); ga.addParam('sysparm_search_term', newValue); ga.getXMLAnswer(function(response) { var matches = JSON.parse(response); if (matches.length > 0) { var msg = 'Matches found:<br>'; matches.forEach(function(m) { msg += m.label + '<br>'; }); g_form.showFieldMsg('company', msg, 'info'); } else { g_form.showFieldMsg('company', 'No matches found', 'info'); } }); }

This way, when a user types part of a name, code, or country, you can dynamically show matches.

[abhisheksat]

Final Update:
It turned out the issue was caused by a semicolon (;) at the end of the line after true. Once I removed it, everything started working correctly.

Thank you to everyone who took the time to help and suggest solutions!

[J Siva]

@abhisheksat  Great!!