How to use 123TEXTQUERY321 column on the table: sysapproval_approver ?

kevinanderson · ‎01-17-2017

We are implementing Service Portal for deployment next month to production. We are using the Data Table widget to display a list of approval records, but I have run into an issue with the search functionality of this widget. The widget uses the 123TEXTQUERY321 column for keyword searches.

I am finding that the query that is generated by the widget is failing when using the keyword text search field on the widget against the table 'sysapproval_approver'.

I am able to reproduce the empty result set by recreating the query in a back ground script. With the 123TEXTQUERY321 column query condition enabled, I get zero results, with it disabled, I can see the target approval record in the output of the results.

Does anyone have an idea why the 123TEXTQUERY321 field returns zero results for the table sysapproval_approver? When I navigate to the dictionary entries for sysapproval_approver, there is no related list entry to create a search index according to the Wiki article:

Administering Zing Text Search - ServiceNow Wiki

Testing the 123TEXTQUERY321 column in a background script

var gr = new GlideRecord('sysapproval_approver');
gr.addQuery('state', 'requested');
gr.addQuery('sysapproval.sys_class_name','sc_req_item');
//gr.addQuery('123TEXTQUERY321', 'RITM0284161');     // enabling this returns zero results, event though the approval record for this approval exists
gr.query();
while(gr.next()){
     gs.print(gr.sysapproval.number+', '+gr.sys_id+', '+gr.sysapproval.short_description)
}

kevinanderson · ‎01-20-2017

I have spent a lot of time on this. I enabled the text index on the sysapproval_approvers table, which allowed 123TEXTQUERY321 query to work on the searches against values in the "approval for" column. The rest of the columns we are displaying in the widget are dot-walked, and do not return matches using 123TEXTQUERY321.

Attempts to modify the widget to use a series of addOrCondition queries also did not work. I gave up, and just disabled keyword search for this macro against sysapproval_approvers.

F_bio Gon_alves · ‎08-23-2017

Hi kevin anderson,

This search box you created seems very interesting and I have been looking for a similar custom functionality to allow end users to filter the records on a table on demand.

What changes have you made to the OOTB Data Table to be able to add it there?

I would like to test it myself.

Best regards,

Fábio Gonçalves

dhananjay21 · ‎03-08-2018

You can refer to the below Link if found helpful mark as helpful

https://codecreative.io/servicenow/how-to-do-full-text-search-with-gliderecord

Rakesh M1 · ‎04-02-2023

Hi Kevin,

Our search in the portal is returning intermittent results and doesnt return results for half typed case numbers. Can you please help me ton understand why that could be happening, also is there any way to text index for 1 particular field in case table as indexing case table means i have to index task table right? and i am not sure that would have any other impacts.

Thanks