Column Header Searching on a Data Table in the ServicePortal

[maxmorganti]

Hello, this article is to help inform people of how to build column heading searching on the data table widget in the ServicePortal. The searching works just like it does on the backend tables with a slight variation.

The searching automatically does a contains search on most fields, it even works on List field types. To search, all you need to do is input your value and hit the enter key. The table will then filter down to the specific records defined in your search.

You can search multiple fields at once. It will also take into account the predefined filter on the data table. Resetting it is a little tricky, so there is a reset filter button on the top of the widget. However, if you are only searching on one field at a time, clearing out the value and hitting enter will also reset the table to no filter, or the predefined filter if there is one.

There are a few caveats to this. For reference fields, it filters based on the display field of the table, so if there is not one, you have to go in and define that, like the user field which is done in the widget. Then integer and decimal fields cannot do contains searches, so it is an IS search. For choice fields, it searches the choice table based on the field and table, then searches the label and returns the actual backend value of the choice to filter the table. There are likely more column types that will need to be added and I will do my best to routinely update the widget download as well.

Attached is the update set with the configured data tables. If you found this helpful, please go ahead and mark the article as helpful.

[febailey]

Awesome!! Thanks Max

[Vladimir Chere1]

Great! Thanks! It would require some more work for string fields that are set as choices (which is often encountered ie 'Category' field on incident) so it retrieves sys_choice record (similar to choice fields) but not run for example 'categoryLIKEhelp' because it will not find anything in that case. But yeah could be even more cases where  it depends on the dictionary configuration where certain operators don't work but other does.

[maxmorganti]

Actually, I have a new version that handles that! I take the value that is input, query the choice table based on the inputted text and table, along with the actual field to filter down and return that value. Along with integer and decimal fields, further testing showed that those needed adding, which I have done. I will post an updated code soon!

[abhi56]

Hi @maxmorganti @febailey @Vladimir Chere1  can you please help me this widget is working fine untill there is a new reference field which is referencing to another table 

 can you please help me with the exact code where should i change to solve this problem ,in the attached screenshot 

assigned to field is reffering to aisa_ui_action this table

and it is showing like assigned.to.null

where should i change in the below server script or in the client script

if (input.table_field) {

        var column = input.table_field;

        var field = input.table_field;

        var record = '';

        var display = '';

        var sys = [];

        var ge = grForMetaData.getElement(column);

        column = ge.getED().getInternalType();

        if (column == 'reference') {

            var gz = new GlideRecord('sys_dictionary');

            gz.addEncodedQuery('element=' + field + '^internal_type=reference');

            gz.query();

            if (gz.next()) {

                record = gz.getValue('reference');

            }

            if (record == 'sys_user') {

                data.filter = input.table_field + '.name' + 'LIKE' + input.text + '^' + input.table_filter;

            } else {

                var grDict = new GlideRecord('sys_dictionary');

                grDict.addEncodedQuery('name=' + record + '^display=true');

                grDict.query();

                while (grDict.next()) {

                    display = grDict.getValue('column_name');

                }

                data.filter = input.table_field + '.' + display + 'LIKE' + input.text + '^' + input.table_filter;

            }

        } else if (column == 'glide_list') {

            var gq = new GlideRecord('sys_dictionary');

            gq.addEncodedQuery('element=' + field + '^internal_type=glide_list');

            gq.query();

            while (gq.next()) {

                record = gq.getValue('reference');

            }

            if (record == 'sys_user') {

                var us = new GlideRecord('sys_user');

                us.addEncodedQuery('nameLIKE' + input.text);

                us.query();

                while (us.next()) {

                    sys = sys + us.getValue('sys_id') + ',';

                }

                data.filter = input.table_field + 'LIKE' + sys.toString() + '^' + input.table_filter;

            } else {

                var grDict = new GlideRecord('sys_dictionary');

                grDict.addEncodedQuery('name=' + record + 'display=true');

                grDict.query();

                while (grDict.next()) {

                    display = grDict.getValue('column_name');

                }

                var gh = new GlideRecord(record);

                gh.addEncodedQuery(display + 'LIKE' + input.text);

                gh.query();

                while (gh.next()) {

                    sys = sys + gh.getValue('sys_id') + ',';

                }

                data.filter = input.table_field + 'LIKE' + sys + '^' + input.table_filter;

            }

        } else if (column == 'boolean') {

            if (input.text == 'true') {

                data.filter = input.table_field + '=' + 'true' + input.table_filter;

            } else {

                data.filter = input.table_field + '=' + 'false' + input.table_filter;

            }

        } else if (column == 'choice') {

            var grChoice = new GlideRecord('sys_choice');

            grChoice.addEncodedQuery('name=' + data.table + '^label' + 'LIKE' + input.text + '^element=' + field);

            grChoice.query();

            if (grChoice.next()) {

                data.filter = input.table_field + '=' + grChoice.getValue('value') + '^' + input.table_filter;

            }

        } else if (column == 'integer' || column == 'decimal') {

            data.filter = input.table_field + '=' + input.text + '^' + input.table_filter;

        } else {

            data.filter = input.table_field + 'LIKE' + input.text + '^' + input.table_filter;

        }

        if (input.text == '') {

            data.filter = input.resetfilter;

        }

    }

    if (input.searchReset) {

        data.filter = input.resetfilter;

    }