ALV standard checks are not working for uploaded data through excel.

[Sagar47]

I've added a custom button to the toolbar for uploading an Excel file and displaying its content in an ALV grid. Upon clicking 'save,' the data from the Excel file gets appended to the main data dictionary table. However, certain fields, with fixed values defined through domains, pose an issue. The ALV fails to validate incorrect cell values that don't align with the defined values through domains. Interestingly, manually inserting a row and adding data triggers an error log for any discrepancies.

Note: I'm using a dynamic table because the program adds data to a custom table through an Excel file.

>Below code to transfer the excel data to dynamic internal table <all_table_new>, and in ALV display I'm using this internal table to Display records.

FORM excel_upload .

  DATA: lv_file_path  TYPE ibipparms-path,
        lv_filename   TYPE string,
        lv_program    TYPE sy-repid,
        lv_dynpro     TYPE sy-dynnr,
        lv_field_name TYPE dynpread-fieldname.

  lv_program = sy-repid.
  lv_dynpro = sy-dynnr.

  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name         = lv_program
      dynpro_number        = lv_dynpro
      field_name           = lv_field_name
*     FILE_FILTER          = 'Excel Files (*.xlsx)|*.xlsx|All Files (*.*)|*.*'
    IMPORTING
      file_name            = lv_file_path
    EXCEPTIONS
      no_filename_selected = 1
      OTHERS               = 2.

  IF sy-subrc = 0.
    lv_filename = lv_file_path.

*****************EXCEL TO INTERNAL TABLE***************
    DATA: gv_file TYPE rlgrap-filename.
    gv_file = lv_filename.

    REFRESH it_excel.

    CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
      EXPORTING
        filename                = gv_file
        i_begin_col             = 1
        i_begin_row             = 2
        i_end_col               = 10
        i_end_row               = 100
      TABLES
        intern                  = it_excel
      EXCEPTIONS
        inconsistent_parameters = 1
        upload_ole              = 2
        OTHERS                  = 3.

    IF it_excel IS INITIAL.
      MESSAGE i302(ztdcv1) DISPLAY LIKE 'E'. "Please try again
      CALL SCREEN 0500.
    ENDIF.


    DATA: lv_max_col_count TYPE i,
          lv_max_cat_count TYPE i.

    lv_max_col_count = 0.

***********checking column number in excel data********
    LOOP AT it_excel INTO wa_excel.
      IF wa_excel-col > lv_max_col_count.
        lv_max_col_count = wa_excel-col.
      ENDIF.
    ENDLOOP.

    lv_max_col_count = lv_max_col_count + 1.  " +1 to skip 'MANDT' field
    lv_max_cat_count = lines( it_cat_new ) - 2. "-2 to skip 'MANDT' and 'Line_index' fileds

    IF lv_max_col_count  <> lv_max_cat_count.
      MESSAGE i300(ztdcv1). " column mismatch in table structure and excel data
      CALL SCREEN 0500.
    ELSE.

      SORT it_excel BY row col.

      CALL METHOD cl_alv_table_create=>create_dynamic_table
        EXPORTING
          it_fieldcatalog = it_cat
        IMPORTING
          ep_table        = DATA(ldtab_new).


      ASSIGN ldtab_new->* TO <excel_table>.


      FIELD-SYMBOLS: <excel_wa> TYPE any, " work area of <excel_table>
                     <fs1>      TYPE any.

      FIELD-SYMBOLS: <fs_line_index> TYPE any.

      DATA new_line TYPE REF TO data.

      DATA: lv_row         TYPE i,
            lv_col         TYPE i,
            lv_max_col     TYPE i,
            lv_current_row TYPE i,
            lv_line_num    TYPE i.

      lv_line_num = 1.


******Looping it_excel to get records from excel file**************
      LOOP AT it_excel INTO wa_excel.

        lv_max_col = lines( it_cat ).

        lv_row = wa_excel-row.
        lv_col = wa_excel-col.
        lv_col = lv_col + 1.

        IF lv_col <= lv_max_col.
          DATA(lv_fieldname) = it_cat[ lv_col ]-fieldname.

          IF lv_row > lv_current_row.
            IF  <excel_wa> IS ASSIGNED.

              ASSIGN COMPONENT 'LINE_INDEX' OF STRUCTURE <excel_wa> TO <fs_line_index>.
              IF sy-subrc = 0.
                <fs_line_index> = lv_line_num.
                lv_line_num = lv_line_num + 1.
              ENDIF.

              INSERT <excel_wa> INTO TABLE <excel_table>.
            ENDIF.

            CREATE DATA new_line LIKE LINE OF <excel_table>.
            ASSIGN new_line->* TO <excel_wa>.
            lv_current_row = lv_row.
          ENDIF.

          ASSIGN COMPONENT lv_fieldname OF STRUCTURE <excel_wa> TO <fs1>.
          IF sy-subrc = 0.
            <fs1> = wa_excel-value.
            UNASSIGN <fs1>.
          ENDIF.
        ENDIF.

        AT LAST.

          ASSIGN COMPONENT 'LINE_INDEX' OF STRUCTURE <excel_wa> TO <fs_line_index>.
          IF sy-subrc = 0.
            <fs_line_index> = lv_line_num.
            lv_line_num = 1.
          ENDIF.

          INSERT <excel_wa> INTO TABLE <excel_table>.
        ENDAT.

      ENDLOOP.



**********Assiging <excel_table> data to <all_table_new>*******
    FIELD-SYMBOLS: <wa_all_table_new>         TYPE any.
    CREATE DATA new_line LIKE LINE OF <all_table_new>.
    ASSIGN new_line->* TO <wa_all_table_new>.
    REFRESH <all_table_new>.

        LOOP AT <excel_table> INTO <excel_wa>.
      MOVE-CORRESPONDING <excel_wa> TO <wa_all_table_new>.

      INSERT <wa_all_table_new> INTO TABLE <all_table_new>.

      CLEAR <wa_all_table_new>.
      CLEAR <excel_wa>.
    ENDLOOP.

    ENDIF.
  ENDIF.


ENDFORM.

>Below code to display ALV

MODULE display_data_0500 OUTPUT.

  CLEAR event_new.

  DATA : lwa_layout_0500 TYPE lvc_s_layo.

  DATA : wa_stable TYPE lvc_s_stbl.


  IF gt_cust_new IS INITIAL.

    CREATE OBJECT gt_cust_new
      EXPORTING
        container_name = cust_new.

    CREATE OBJECT gt_grid_new
      EXPORTING
        i_parent = gt_cust_new.

    CLEAR lwa_layout_0500.
    lwa_layout_0500-cwidth_opt = 'X'.

    CALL METHOD gt_grid_new->set_table_for_first_display
      EXPORTING
        is_layout            = lwa_layout_0500
        it_toolbar_excluding = it_exclude_0500
      CHANGING
        it_fieldcatalog      = it_cat_new
        it_outtab            = <all_table_new>.



    IF event_new IS INITIAL.
      CREATE OBJECT event_new.
    ENDIF.

    gt_grid_new->register_edit_event( i_event_id = cl_gui_alv_grid=>mc_evt_enter ).
    gt_grid_new->register_edit_event( i_event_id = cl_gui_alv_grid=>mc_evt_modified ).

    SET HANDLER event_new->handle_data_changed_new FOR gt_grid_new.
    IF p_text = 'X'.
      SET HANDLER event_new->handle_toolbar_txt_upld FOR gt_grid_new.
    ELSE.
      SET HANDLER event_new->handle_toolbar_xls_upld      FOR gt_grid_new.
    ENDIF.
*    SET HANDLER event_new->handle_toolbar_xls_upld      FOR gt_grid_new.
    SET HANDLER event_new->handle_user_command_new FOR gt_grid_new.

    CALL METHOD gt_grid_new->set_toolbar_interactive.

  ELSE.

    CALL METHOD gt_grid_new->refresh_table_display.
    CALL METHOD cl_gui_cfw=>flush.


*    CALL METHOD gt_grid_new->set_table_for_first_display
*      EXPORTING
*        is_layout            = lwa_layout_0500
*        it_toolbar_excluding = it_exclude_0500
*      CHANGING
*        it_fieldcatalog      = it_cat_new
*        it_outtab            = <all_table_new>.

    CALL METHOD gt_grid_new->check_changed_data( ).


*******Implicit data insertion into 'gt_mod' as type 'I'********
*******since there's no user action involved in the ALV grid during Excel upload*************
    FIELD-SYMBOLS: <fs_data> TYPE any,
                   <fs_val>  TYPE any.
    REFRESH gt_mod.

    LOOP AT <all_table_new> ASSIGNING <fs_data>.


      ASSIGN COMPONENT 1 OF STRUCTURE <fs_data> TO <fs_val>.

      IF sy-subrc = 0.

        gt_mod-alv_indx = <fs_val>.
        gt_mod-indx = <fs_val>.
        gt_mod-type = type_ins.
        gt_mod-save = 0.
      ENDIF.
      APPEND gt_mod.
      UNASSIGN <fs_val>.

    ENDLOOP.
  ENDIF.


ENDMODULE.

[Sandra_Rossi]

I can't read your code, it's just one line and it's full of < br >. Ctrl+Shift+V (unformatted paste) may help to avoid < br >.

[Sandra_Rossi]

Don't use cl_alv_table_create=>create_dynamic_table as explained here:

Alternative to CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE | SAP Blogs

[Sandra_Rossi]

In the field catalog (not your code), you need to refer to the DDIC.

[Sandra_Rossi]

Thanks for editing the format of your question.

It's still missing the information about your field catalog, which needs to refer to the DDIC.

[Sagar47]

I'm creating the field catalog 'it_cat' using this method, where 'p_table' represents the table name provided by the user from the selection screen. Please let me know if any further clarification needed.

Data: it_cat TYPE TABLE OF lvc_s_fcat. "Type of it_cat

  REFRESH it_cat.
  CALL FUNCTION 'LVC_FIELDCATALOG_MERGE'
    EXPORTING
      i_structure_name       = p_table
    CHANGING
      ct_fieldcat            = it_cat
    EXCEPTIONS
      inconsistent_interface = 1
      program_error          = 2
      OTHERS                 = 3.

[Sagar47]

Now, refresh_table_display is working correctly. Previously, the column XYZSTYLEZYX was missing in <all_table_new>.

However, ALV is still unable to capture the wrong cell value that does not fall under the defined value through domains.

[Sandra_Rossi]

XYZSTYLEZYX doesn't concern your question.

[Sagar47]

Got it, but any solution to fix the ALV standard validation issue?

[Sagar47]

The aforementioned standalone program is not relevant to my issue. In my program also when a user manually modifies any cell value, ALV performs its standard checks. However, when we upload data from Excel and display it in ALV before saving it to the actual DDIC table, ALV fails to detect incorrect cell values that do not fall within the defined range.

[Sandra_Rossi]

My answer is not relevant. I'm deleting it.

[Sandra_Rossi]

Apologies. Your question is about validating the domain values in ALV. I think it's not part of standard checks, only foreign keys with check activated are part of standard checks.

[Sagar47]

This code effectively conducts standard validation for both domain values and foreign keys with activated checks when manually entering or modifying data in ALV. However, it doesn't function when loading data programmatically.

[Sandra_Rossi]

Yes, the standard doesn't do any validation on the ALV internal table

• when you pass it through set_table_for_first_display
• when you change it directly by program

The validations are done by the ALV framework only if you make your ALV editable, and the user changes values on the screen, and only on fields where foreign keys have been defined with error check activated.

[Sagar47]

Thank you for the clarification but is there a method or approach to programmatically trigger validation for the data loaded via the program itself? Essentially, is there a way to implement validation programmatically?

[Sandra_Rossi]

I guess you mean a generic way to check domain values, foreign keys. You can find answers in the forum but don't mention ALV to search. It's not easy to find the right place → how to find the checktable assigned to a field dynamically | SAP Community.

[Sagar47]

Thank you, Sandra, for your consistent follow-up. With the provided FM, I am now able to validate the Excel data programmatically.

[Sandra_Rossi]

Test program to check the domain fixed values and foreign keys of any table/structure field, generically via DDUT_INPUT_CHECK:

REPORT.
PARAMETERS p_table TYPE tabname DEFAULT 'SFLIGHT'.
PARAMETERS p_field TYPE fnam_____4 DEFAULT 'CARRID'.
PARAMETERS p_value TYPE string DEFAULT '??'.

START-OF-SELECTION.
  DATA msgid TYPE symsgid.
  DATA msgty TYPE symsgty.
  DATA msgno TYPE symsgno.

  CALL FUNCTION 'DDUT_INPUT_CHECK'
    EXPORTING  tabname         = p_table
               fieldname       = p_field
               calling_program = sy-repid
               value           = p_value
    IMPORTING  msgid           = msgid
               msgty           = msgty
               msgno           = msgno
    EXCEPTIONS no_ddic_field   = 1
               illegal_move    = 2
               OTHERS          = 3.
  IF sy-subrc <> 0.
    WRITE: 'call failed'.
  ELSE.
    IF msgty CA 'AEX'.
      WRITE: / 'error', msgty, msgid, msgno.
    ENDIF.
  ENDIF.

NB: domain value tables are not checked, it checks only foreign keys.