Script Design Notes - Bulk Condition Record Management

Design a master spreadsheet for all scenarios.

Make sure to map every script to the same master spreadsheet. See Spreadsheet Design Notes.

Create and test each script independently for each scenario

If you are using Query to extract existing records, make sure the outputs include all the required inputs for the change scripts. Use Excel formulas in Query to calculate values for the script inputs. This will prevent the user from requiring additional calculations after the extraction.

How to add a formula to Query:

  1. Create formula in Excel and test
  2. Copy the formula into one of the columns under Excel Expression (see below).
  3. Type the cell of the first row you want the results to appear in the Expression Evaluation cell. The example below shows the first row of data in row 7, hence the example of Excel expression =TEXT(AA7,"#") with the expression evaluation cell of L7.

Query will extract data directly out of SAP tables, but sometimes the format the data is stored in is not usable. An Excel formula can be used to correct the format of the extracted data.

Note that when you extract data for condition records, different key combinations use different tables. Therefore each key combination requires a different query. You can link all queries together to extract required condition records to one spreadsheet.

When creating change scripts for the condition records, use several scripts with minimal if conditions instead of one large script with lots of logic. This makes it easier to modify the script later. To keep it simple use one scripts for each key combination for each condition type.

Add an overall if condition to identify when to use the script

After each script is created, add an overall if condition that identifies which record to run for each upload script.

Usually this will be one or two if conditions that specifies the condition type, key combination, or both. If you are using two conditions this is synonymous to AND logic, and one condition must be completely inside the other condition so that the script will only run if both conditions are met.

Creating an overall If Condition

  1. Open Winshuttle Transaction.
  2. Open the Mapper.
  3. On the Expert tab, highlight all the lines except the Log column (for this example lines 4 to 62). In this example the script will only run for condition type PR00, which is stored in column C of the worksheet.
  4. Select IF.
  5. Under Select Column, select the column in your Excel file that will indicate when this script should run/. In this example it is Column C.
  6. Under Operator, select = (equals).
  7. Under Value, select the value you want (in this example - PR00).
  8. Click OK

Linking scripts together

After each condition is added to the script then you can link them all in a chain. In this example we linked all 4 scripts together to make it seem like one script (i.e. one button).

  1. Open the first script.
  2. On the main Run screen, inside the Advanced Run Options, check Link Transaction script
  3. Click and browse to the second script that you want to run
  4. Select File, Sheet, and Start and End Row. In this example we checked all of them, usually you will want to use the same settings from the first script for your File, Sheet and Start and End Row.
  5. Repeat this for each script in the chain (i.e. open script 2 and link to script 3, etc.)

    Tip: Save a copy of all linked scripts for a non-linked version. This can be helpful if you need to test a single script.

Also note that when you link Queries you will not need if conditions, only a chain of all required queries.

Publish the first script in the linked chain to the master worksheet

After all scripts are linked together and tested, you can publish the first script to the workbook to allow users to run the scripts from within the Excel add-in. You only need to publish the first script in the chain to execute all scripts.

  1. On 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 Bind to sheet. This prevents the user from using the script against a different sheet in the same workbook.
    • Enter a description of the script (e.g. Update All Pricing). Note: This is name the user will see inside the Winshuttle Add-in within the Excel workbook.

Run the scripts from the Excel Add-in

The end user can now run all scenarios easily with a "smart" spreadsheet from the Excel Add-in for convenience.

Download and reference the Bulk conditions record management sample files. There are two sheets to enter prices based on 2 scenarios.  All scripts and queries are already published in the Excel add-in.

  • Manually entering new prices not maintained in SAP and modify existing prices
  • Auto updating prices already maintained in SAP with a standard increase across the all condition records

    Note: The sample files/examples will probably not work in all SAP instances because they depend upon how each condition type and key combination is configured within the SAP instance. 

See below for additional information.

AllPrices Sheet

This sheet is designed to enter new prices that are not maintained in SAP with manual price modifications of prices already in SAP. 

Columns A, C, and D are drop downs and should be entered by the end user.  Columns B, E, and F should normally be hidden (in an end user example), but they are not hidden in the example to show the calculations.  Columns B, E, and F store calculations based on Columns A, C, and D to format the remaining columns (G through T). 

Light blue indicates a required field. Gray indicates fields not required for that condition.  When the Run button is clicked, scripts created for all scenarios are chained together and run based upon the selection for each row. This example has 4 scripts. 

Also note some if conditions were used to combine some of the combinations.  It would even be simpler to use 10 scripts chained together one for each condition type & key combination ( 5 for create and 5 for modify).

UpdatePrices Sheet

This scenario is for updating prices already in SAP with a standard increase.  First the user will run a set of linked Queries to download all requested, existing pricing records for PR00 condition type.  In this example it pulls for 3 different key combinations of PR00, and several formulas are applied to the Query outputs. 

The user first needs to enter the Price Increase as a percentage (%) and the new validity dates (see cells I2, O2 & O3), and then click Run to upload all prices based on the newly calculated price.  Note that some If conditions were used to combine some key combinations into the same script.  It would even be simpler to use one script for each condition type and key combination.