fetch the data from the custom table

chandan86patra
Tera Contributor

Hi All,

 

I have created two fields in the ServiceNow Catalog Item: Address and Location. Both are reference fields pointing to a custom table where the address and location values are stored in their respective fields.

My requirement is to allow users to search for locations by typing a few characters. For example, if a user types “Ban”, the Address field should display all records related to Bangalore and same location field in the catalog item.

Could you please guide me on how to achieve this requirement?

 

 

Thanks ,

 

Chandan 

3 REPLIES 3

HarishKumar6668
Kilo Sage

Hi @chandan86patra ,

A reference field only takes a value once the user selects a record from the list. Typing “Ban” will show matching records as suggestions, but nothing is set until one is chosen, so the typed text can’t be used to filter another field.

The usual approach is to make Address dependent on Location:

  1. User types “Ban” in the Location field and selects Bangalore from the suggestions.
  2. The Address field then shows only the addresses linked to that location.

To set this up:

Location variable: use the type-ahead as normal. If you want typing to match on more than the display value, add these variable attributes:
ref_auto_completer=AJAXTableCompleter,ref_ac_columns=<your_location_field>;<your_address_field>,ref_ac_columns_search=true

Address variable: add an advanced reference qualifier that filters on the selected location, for example:
javascript&colon;’<location_field_on_custom_table>=' + current.variables.<location_variable_name>;

 

If this resolves your issue, please mark the response as helpful and accept it as the solution.

Regards,
Harish

 

Tanushree Maiti
Tera Patron

Hi @chandan86patra 

 

1)Open the reference variable which referenced to location (cmn_location)

2) Under Under the Type Specifications tab, add the following string to the Variable attributes field:

 

(Update the column as per your request &table . No of columns depend on which column you want  to search)

 

Generic format : ref_ac_columns_search=true, ref_ac_order_by=column_name, ref_ac_columns=column_name1, column_name2, ref_auto_completer=AJAXTableCompleter

 

e.g   

ref_ac_columns_search=true,ref_ac_columns=name;city;state,ref_auto_completer=AJAXTableCompleter,

 

Note: If you want a reference field that points to the table can find records by any part of a value, not only by the beginning of the value Then

refer: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0750081

 

Also check Performance Hacks: Reference Fields (4 of 4) Contains vs. Starts With

 

 

yashkamde
Giga Sage

Hello @chandan86patra ,

 

So for this you will have to variable, change the autocomplete search behavior from STARTSWITH to CONTAINS :

Navigate to sys_properties.list >

yashkamde_0-1795157457063.png

Also to allow searching by extra fields on the referenced table add Variable Attributes to variable :

 

ref_auto_completer=AJAXTableCompleter,ref_ac_columns=<variable_name>;
<variable_name>,ref_ac_columns_search=true,ref_ac_contains=true

 

If my response helped mark as helpful and accept the solution.