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.

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.

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.

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.

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

Which License is needed for this feature Professional ✘ | Enterprise ✔