Implement Dynamic Field Mapping from Excel to SAP

This topic provides guidelines about how to implement dynamic field mapping using dynamic skip and dynamic formula to ensure that the need for manual mapping is not required each time when a column is added.

Let us assume a scenario of MM02 transaction where the user must update the material description in SAP. Usually, after recording the file through MM02 transaction, you have to define the field (column name) in the Mapper tab to obtain the data from Excel.

It means that if the required data of the Excel file is in any other column, then the user must change the column name in mapper.

The above procedure can be automated easily through Dynamic Skip and Dynamic Formula. The base logic that can be applied to the above scenario is as follows.

• Column in which 'Material Description' is present will be assigned with unique reference.
• Process Runner will find that uniquely defined reference in the external Excel file as instructed internally by Dynamic Skip and Dynamic formula.
• During run time, first Process Runner will search in the Excel file for that unique reference.
• Finally, once the column with defined reference is traced, Process Runner will obtain the data from that column and update it in the SAP.

To understand the above scenario where Material Description is mapped dynamically

1. Record the Process file to update the Material Description using MM02 transaction code. For information about how to record a Transaction, see MM02 - Update Material.
2. Go to the Mapper tab.

3. Change the mapping type for the Material Description (Short Text) field as Fix Single Value.

4. Remove the Excel column header in the Map Value column.

5. Select 'Z-Custom Formula' in 'Dynamic Formula' column of 'Material Description (Short Text)' row to open the Custom Formula Editor and assign a unique reference to 'Material Description' column in the Excel file.

6. Enter or paste the following in Custom Formula Editor window.

   'Write VB.NET code here

   Static IsField1Found As Boolean

   Static IsExecuteFirst As Boolean

   Static XLCol as Integer

   Dim i As Integer

   Dim ColumnX as integer

   ColumnX = 2 'Pass the row number which content Technical Name

   'if executing for the first time, search for the field name [MAKTX] and returns its value.

   If Not IsExecuteFirst Then

   For i = 1 to 10 'Search Field in column 'A' to 'J' base on 'R1C1 reference style' in excel

   If Trim(Ucase(iSheet.Cells(ColumnX, i).Text)) = Trim(Ucase("MAKTX")) Then

   IsField1Found = True

   Return iSheet.Cells(iCurrentExcelRowNumber, i).Text

   Exit For

   End if

   Next

   IsExecuteFirst = True

   End if

   'if the field name [MAKTX] is not found, it will return [REMOVEFIELD] and will be used in Custom Skip

   If IsField1Found = False Then

   Return "REMOVEFIELD"

   End If


7. Select 'Z-Custom Dynamic Skip' in 'Dynamic Skip' column of 'Material Description (Short Text)' row to open the Custom Field Skip Editor.

8. Enter or paste the following code in the Custom Field Skip Editor window.

   'Write VB.NET code here

   'if iValue is ["REMOVEFIELD"] or blank it will skip that field and that field will not be send to SAP

   If iValue = "REMOVEFIELD" Or iValue = "" Then

   Return 1

   End If


9. Save the Process file and select Run.

If dynamic mapping must be implemented for other fields like Material Group, Gross Weight and Net Weight, then users can repeat Step 3 to Step 8 for the corresponding fields in the Mapper.

Also refer to Cloud-Sample 3167 (MM02_Change_Material(Dynamic_Mapping) where all the fields (Material Description, Material Group, Gross Weight, and Net Weight) are mapped dynamically.

If you face any issues, record the transaction as indicated in this section and send your recording and problem description. Please create a support case on Salesforce.