How to implement fuzzy search with NextTables

Especially if you are working with a large list of possible entries, finding the right one can be challenging. In the “How to always find the right values with NextTables” article, you learned the possibilities of the search algorithm. In this article I will guide you step by step through the technical side of implementation.

At the end of this article, you will be able to customize your search results and enhance them with a context menu. You can see an example below.

Fuzzy search example

Technical implementation

In the How to implement a BAdI for NextTables article you have learned how to create a BAdI implementation. To make sure that the BAdI is executed only for a certain InfoObject, you have to setup the filter criteria accordingly. Please define the respective InfoObject in the filter settings. In our example we use the InfoObject /NLY/PERNR.

Search filter

The meta data of the search can be adjusted using the SET_META_EXIT method. For example, you can change the search bar to dropdown or activate and disable strict search.

Search BAdI Meta Method

The dropdown search suites best for short result lists and always loads all values locally first. Then, the search is executed on the locally loaded values. Thus, dropdown is ideal for short lists. On the other hand, the search function is executed for the entered search term only (eventually it is executed multiple times, if you enter the letters quite slowly). Therefore, SEARCH setting suites best for objects with large master data selections.

You can find the sample code below.

METHOD /nly/if_search~set_meta_exit.

ch_s_search_info-min_char = '3'.
ch_s_search_info-searchmode = i_stype.
ch_s_search_info-searchname = i_searchname.
ch_s_search_info-strict = 'X'.
ch_s_search_info-searchstyle = 'SEARCH'.

ENDMETHOD.

The search logic is implemented in the implementing class. We will utilize the method /NLY/IF_SEARCH~DO_SEARCH_EXIT to customize the search. Double click on the method to create an implementation.

Method implementation

In our coding, we define the table for the search results and the necessary variables. After checking the search term for consistency, we define the SQL query in the variable lv_sql. 

In this example we setup a view enhancing the personnel IDs with additional information such as company codes, addresses, cost centres etc. We also define the mode of the fuzzy search and weight the respective elements. Here, the family name NACHN is the most important element (weight 1.0), followed by first name (VORN) and ID (PERNR). Afterwards, the SQL query is executed.

In case of error, you can define respective messages to be presented to the user. If the search query was executed successfully, the search results with respective context menu are prepared.

As the last step, you need to define the search as customer exit implemented. Otherwise, the standard search will be executed again, overwriting your results.

You can find the complete coding below:

  METHOD /nly/if_search~do_search_exit.

TYPES:
BEGIN OF ts_changes,
score TYPE decfloat34,
pernr TYPE /b787/oipernr,
nachn TYPE c LENGTH 40,
vorna TYPE c LENGTH 40,
pstlz TYPE c LENGTH 10,
stras TYPE c LENGTH 60,
ort01 TYPE c LENGTH 40,
emplstatus_txtmd TYPE c LENGTH 40,
bukrs TYPE c LENGTH 10,
bukrs_txtmd TYPE c LENGTH 40,
kostl TYPE c LENGTH 10,
kostl_txtmd TYPE c LENGTH 40,
orgeh TYPE c LENGTH 10,
orgid_txtmd TYPE c LENGTH 40,
plans TYPE c LENGTH 10,
plans_txtmd TYPE c LENGTH 40,
END OF ts_changes,
tt_changes TYPE TABLE OF ts_changes.

DATA: ls_search_result TYPE /nly/ts_search_result,
lv_sql TYPE string,
lo_t_table TYPE REF TO data,
l_search_term TYPE string.

* Search term
DATA(lv_search_term) = i_search_term.

FIELD-SYMBOLS:
<fs_s_table> TYPE ts_changes,
<fs_t_table> TYPE tt_changes.

REPLACE ALL OCCURRENCES OF '%20' IN lv_search_term WITH ` `.
REPLACE ALL OCCURRENCES OF '%22' IN lv_search_term WITH `"`.

IF lv_search_term IS INITIAL.
l_search_term = '*'.
ELSE.
l_search_term = |{ lv_search_term }|.
ENDIF.

CREATE DATA lo_t_table TYPE tt_changes.
ASSIGN lo_t_table->* TO <fs_t_table>.


lv_sql = |SELECT TOP 300 DISTINCT SCORE() AS SCORE, pernr, nachn, vorna, pstlz, stras, ort01, emplstatus_txtmd, bukrs, bukrs_txtmd, kostl, kostl_txtmd, orgeh, orgid_txtmd, plans, plans_txtmd |
&& |FROM "ZCDSPERSSEARCH" | " Personnel View
&& |WHERE CONTAINS(("NACHN", "VORNA", "PERNR", "STRAS", "ORT01", "BUKRS", "KOSTL", "ORGEH", "PLANS", "PLANS_TXTMD", "ORGID_TXTMD", "BUKRS_TXTMD", "KOSTL_TXTMD" ), '{ l_search_term }', FUZZY(0.7, 'similarCalculationMode=compare')|
&& | , weight (1, 0.8, 0.8, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.3, 0.3, 0.3, 0.3 )) |
&& |ORDER BY score() desc|.


TRY.
" Prepare SQL connection and statement
DATA(lo_result) =
cl_sql_connection=>get_connection(
)->create_statement(
)->execute_query( lv_sql ).

lo_result->set_param_table( REF #( <fs_t_table> ) ).

" Get result
lo_result->next_package( ).
lo_result->close( ).
CATCH cx_sql_exception INTO DATA(err).

" Error handling
DATA l_error(200) TYPE c.
l_error = |{ err->get_text( ) }|.

RAISE EXCEPTION TYPE /nly/cx_search_rest
EXPORTING
textid = /nly/cx_table_rest=>custom_message
msgv1 = l_error(50)
msgv2 = l_error+50(50)
msgv3 = l_error+100(50)
msgv4 = l_error+150(50).
ENDTRY.

LOOP AT <fs_t_table> ASSIGNING <fs_s_table>.
CLEAR ls_search_result.
ls_search_result-title_key = |{ <fs_s_table>-pernr alpha = out }|. " As search terms are handled in external format on the front end.
ls_search_result-title_desc = |{ <fs_s_table>-nachn } { <fs_s_table>-vorna } ({ <fs_s_table>-pernr ALPHA = out width = 1 }) |.
ls_search_result-sub_title = | { <fs_s_table>-plans_txtmd } in { <fs_s_table>-orgid_txtmd } |.
ls_search_result-content = |Adresse: { <fs_s_table>-stras } | && | { <fs_s_table>-ort01 } |
&& | <br>BuKrs: { <fs_s_table>-bukrs } { <fs_s_table>-bukrs_txtmd }|
&& | <br>Kost: { <fs_s_table>-kostl } { <fs_s_table>-kostl_txtmd }|
&& | <br>Score: { <fs_s_table>-score }|.
ls_search_result-score = <fs_s_table>-score * 100.
APPEND ls_search_result TO e_t_search_result.

ENDLOOP.


* set as customer exit implemented
e_is_implemented = abap_true.
ENDMETHOD.

As you implement your custom logic, please make sure that all involved pieces are activated, not only the implementing class. You need to activate the following objects:

  • implementing class together with methods
  • BAdI implementation
  • Enhancement implementation

Technical Tutorials

Do you have a question regarding NextTables?