Add New – Update Existed Price List on SAP Business One 9.2
Using Data Transfer Workbench (DTW)
During this operation we’ll use two tools; SAP Business One 9.2 and Data Transfer Workbench (DTW).
The Scenario in SAP Business One 9.2
We have to create a new blank Price List, in doing so, from Modules > Inventory > Price Lists > Price Lists
After click on “Price Lists” a new window will show up includes all existed price lists in SAP Business One.
Each one of these Price Lists has a number that arranges it during the series, which we’ll use later in the Data Transfer Workbench (DTW).
By clicking on any existed row, then choose “Add new row” we can add blank Price List.
Additional to the numbers and sorting of the Price Lists, we have also symbols and numbers for the Units of Measurements (UoM).
For example; we have the Unit of “G” equaled to number 2 in the series, and “50G” equaled to 3 and so on, …
To know about the sorts of your Units of Measurements, you can check from;
Modules > Setup > Inventory > Units of Measurements
Now we got all required information about the objects number in SAP (Price Lists – Units of Measurements).
Let’s dig in more details about the Data Transfer Workbench (DTW).
We have what we call DTW Templates that contains .xlsx templates for each module we have in SAP Business One like;
Banking, Business Partners, Inventory, … for now, what we’re interested in that; the Inventory Module and its templates.
for that via using the File Explorer on your work machine, through this path;
C:\Program Files (x86)\SAP\Data Transfer Workbench\Templates\Templates\Inventory\Item Master Data
We’ll open the ITM1 – Items_Prices template, that the one which we’ll work on.
According to this template, we’ve to arrange in the column
- A: Item Code, which means sorting the products ascending or descending in a specific arrange.
- B: Line Number, sorting every single item in a specific sort in case of there are frequencies.
- C: Price List, the number of Price List inside of SAP B1 as we saw before.
- D: UomEntry, the symbol of the item, which we saw it in SAP B1, Setup > Inventory > Units of Measurements.
- E: Price, the unit price for each item.
- F: Currency, the currency of the prices for each item.
- G: AutoUpdate, Auto Update choice if we want to let the item be updated automatically. (tNO recommended).
After make sure your file is ready to use, save it from MS Excel into Text (Tab delimited) (*.txt)
Now, we’re ready to forward to run the Data Transfer Workbench (DTW) just as an administrator on your work machine.
Note: you have to close the SAP Business One before begin with this process.
Now we have to provide the following requirements;
- User Code and Password of the SAP Business One for which database we want to work on.
- Server Type (SQL Server 2008, 2012, …)
- Server Name
- Login ID and Password for the Database (you have to use the “SA” credentials).
- Choose which database you want to work on.
We’re starting now with the process in the Data Transfer Workbench (DTW). From Import we’ll start;
Step #1 Select Data Type
Choose whether you want to;
- Setup Data (for example; tax codes, banks and so on)
- Master Data (for example; items, business partners, and so on)
- Transactional Data (for example; sales invoices, purchase orders, and so on)
For our case we have to choose the second one Master Data, which we’ll move afterward to Items Master Data.
Step #2 Specify Operational Type
Select the type of operation you want to perform while importing the data.
- Add New Data
- Update Existing Data
- Add New Data and Update Existing.
In our case, we’ll choose the second one; Update Existing Data.
Step #3 Select a Business Object
Select a business object that you want to import. We’ll choose from Inventory > Item Master Data.
Step #4 Select Data Source
Define data sources for business objects, through click on the three dots, we’ll put our previous TXT file in action. When click on “…” a new window will show up in File Explorer let us choose which file we want to import.
Our choice now will be Items_Prices,
Step #5 Map Object Fields
In this step, DTW shows us if our TXT file matched with the standard template that’s stored in the SAP Business One, clarifying each column.
Step #6 Define Error Handling
Through this step, we’ll set up the options for handling errors that occur during the import process. If we want;
- Cancel Import for one error occurs
- Ignore all errors
- Ignore a custom number of errors.
After finishing the importing process successfully, a new window will show up says that The Process has been completed successfully.
Now to get the result from what we’ve done, let’s open SAP Business One, and move to the Price Lists from Modules > Inventory > Price Lists > Price Lists, and we can see now the price list has been updated successfully with the new figures.