Script Design Notes - Bulk Material Management

The first step in this design is to fully understand the flexibility required to meet your needs. Every business is different, so we will look at a simplified example, but the overall logic can be applied to a more complex scenario.

Understanding when and how things are created in SAP and the business' (non-SAP) needs is key. For example:

  • Some companies might create all views, and then just need the flexibility to extend them to multiple plants or sales organizations.
  • Some companies might need the flexibility to create certain views at specific stages within the process in addition to multiple plant extensions.

It is usually easier to maintain multiple, simple scripts where each script represents a single element of the overall material master. In addition, each script will be optional, so you can choose which element you need to create/change for each material record.

Creating the steps

For the following example you can download and reference our collection of sample files (in an Excel workbook).

Note: For the example we used the SAP IDES system, which may not work in other SAP systems.

1. Organize the overall process into sections

In this example, the material master is divided into 3 different groups of views:

  • Basic Data Views (Basic Data 1 and Basic Data 2)
  • Sales Views (Sales: Sales Org. Data 1, Sales: Sales Org. Data 2, and Sales: General/Plant Data)
  • Accounting Views (Accounting 1 and Accounting 2)

Each group can be optional for this case except the Basic Data views.

2. Select CRUD (Create, Read, Update, Delete) Operations

For this example we need to create the following for each of the 3 groups listed above:

  • New material (MM01) or Extend a Material (also MM01)
  • Change a material (MM02)

3. Build the Scripts

For this example we need 6 scripts:

  • Basic Data Views - Create -MM01
  • Basic Data Views - Change - MM02
  • Sales Views - Extend - MM01
  • Sales Views - Change - MM02
  • Accounting Views - Extend - MM01
  • Accounting Views - Change - MM02

4. Use one main spreadsheet or tab for all fields and options in the process

In this example, we used one tab in a workbook to handle all fields of the 6 scripts listed above.

Note: If a field is required for different scripts you do NOT need to maintain the field more than once in your spreadsheet. Instead, you can map the same field/column to multiple scripts.

5. Add Columns

Add columns to your spreadsheet so the user can identify which options they need for each material.

Here (see example at right) we added 6 Columns, titled Controls at the beginning of the spreadsheet (Column A - F) to designate which views and/or function each material needs.

The user just needs to fill in which options are required for each Material with an X.

By adding this indicator we can add a condition in each of the scripts to only run if there is a corresponding X in the appropriate column.

6. Add a condition to each script

  1. Build and test the script independently. Make sure you have mapped the fields from each script to the main spreadsheet where all the data will be held.
  2. Inside the Winshuttle Transaction Mapper, on the Expert tab, highlight all the lines except the log Column (row 1). For this example, this is lines 2 through 24 (basically the entire script, so the script will only run if the condition is true.) This example is for Create Basic Data, which is shown in Column A in the excel file.
  3. Select "IF"
  4. Under Select Column, select the column in your excel file that will indicate when this script should run/execute (Column A for our example).
  5. Under Operator, select =.
  6. Under Value, select the value (X for this example). Also make sure the user knows what indicator to use in the spreadsheet.
  7. Click OK.

7. Link scripts together

After each condition is added to the script you can link them in a chain. For this Design Pattern we linked all 6 scripts together to make it seem like one script (i.e. one button click).

  1. Open the first Transaction script. On the main run screen, under Advanced Run Options, check Link Transaction script.
  2. Select the second script that you want to run by clicking on the search icon to navigate to where the second script is saved.
  3. Under Data File Carry Setting, you would typically select the same settings used in your first script for the seconded linked script for File, Sheet and Start and End Row. For this example all of them are used.
  4. Save and close script 1.
  5. Then open second script and repeat steps 1 through 4 and link the 3rd script. Do this for each additional script you want to link. (In this example we repeated until script 5 was linked to script 6).

 

 

8. Publish the first script

After all scripts are linked together you can publish the first script to the workbook. This will enable users to run the scripts from within the Excel add-in. You only need to publish the first script in the chain to run all of the scripts.

  1. In Transaction, from the main run screen, click the Published icon.

  2. In the Publish to Template or Workbook dialog box, do the following:
    • Select the excel workbook you want to publish the script to
    • Enter the sheet name.
    • Check the Bind to sheet option if this is the ONLY sheet in the workbook that you want the user to against the script.
    • Enter a description for the script. This is name the user will see inside the Excel workbook

9. Run the script from the Excel Add-in

This will be what the end user sees when all of the scripts are run. Remember, each script will only run if you have indicated an X in the Excel file (under Columns A to F for this example) for each material.

Row 4 - only the first 3 scripts will run whereas row 10 just the 4th and 6th script will run.

Additional Recommendations

Use Excel for conditional formatting

The only script required to be first is the Create Basic Data script, so it is the first script in the chain. After it is created, then you can change the data by using the Change Basic Data Script (the 4th in the chain).

The Create Basic Data script is also the script that you can extract/download the material number field to the spreadsheet (see column G in this example). All other scripts are uploaded in Column G.

If the user is not using the first script, then they will need to enter the material number in the spreadsheet.

Note that you can also add some conditional formatting to improve usability. (This is a standard Excel function and not related to Winshuttle software.) For this example, conditional formatting is used to color the cell under column G gray if the first script has an X in this row. This indicates to the user that filling in column G isn't required, because SAP will automatically assign the number. Note: This is not needed if your system does not use auto assignment for Material Number in which, the material number is required in all cases.

Map Create and Change to the same fields

Because you would either create or change, in this example we mapped both Create/Extend scripts (MM01) to the same fields as the Change scripts (MM02).

Because you would not need to use both at the same time, however, if the user accidentally checks both Create and Change for the same material it will just put the same data in again, which effectively won't make any change when the Change script runs.

This configuration enables you to have 3 different, color-coded sections:

  • Basic Data - purple
  • Sales Data - blue
  • Accounting Data - green

There are a couple of exceptions to this, which are also noted in the excel file for the user. Not all fields are required in the Material Info for the Basic Data views. For example, if you are using Create Basic Data, then only Industry Sector and Material Type fields are required. If you use Change Basic Data, then only the Material Number field is required. In addition, the Standard Price field in the Accounting Data section is only required when extending the views for the first time. It can not be edited in SAP in the Change mode.

Make the spreadsheet scalable

In order to make the spreadsheet scalable and easier to change, keep multiple unused columns for each section and then hide those columns.

By keeping several columns blank after each section (for example, the Controls section, Material Info Section, Basic Data Section, etc.) you can easily add additional fields and/or controls later. If you don't keep some extra columns and later need to make changes to the spreadsheet, you may need to remap multiple fields in different scripts.

Hiding the columns keeps the spreadsheet 'clean' and user-friendly.

To hide unused columns in Excel:

  1. Highlight the column
  2. Right click the highlighted column, and then click Hide.

    Download: Bulk Material Management Script and Excel Workbook example