The templates below may be used to upload data into SAP Business One using Data Transfer Workbench (DTW). As noted in each template’s details, certain data is expected (such as item master details) before the templates can be used to load data into B1.
Web Items Template
Download: Web Items Template
Use this excel template to create web items.
Web items are the basic building block for products displayed on the web. This template contains the fields outlined below. These entries are “Parent” entries for Web Item Details.
Web Items Template Fields
|Code||Required||Alphanumeric, 50 characters or less. Any unique code. This may match the name.||This field serves as the key of the record in SAP Business One system and it is mandatory. This field is used as a reference in the child template (Web Item Details). When adding new data, the value of this field is user defined (e.g., 1,2,3, etc.). When updating existing data, the value of this field should be the real record key in SAP Business One.|
|Name||Required||Alphanumeric, 100 characters or less. Any unique name. This may match the code.|
|U_TYPE||Required||“Single” in most cases. “Variant” may be used.||This defines the Web Item Type, which may be either “Single” or “Variant”. If “Variant” is used, ensure
|U_ACTIVE||Required||“Y” or “N”||Web Items marked as active (“Y”) can be viewed when assigned to a collection. Inactive items (“N”) can be configured, but are not visible on the web.|
|U_ITEMCODE||Required||Valid B1 item codes||This field ties a Web Item with a specific B1 Item Master Data record. As such, this Item Code must exist in SAP Business One before this template loaded via DTW.|
|U_VARIANTCODE||Conditionally Required||Variant Code IDs||This field links a Web Item to a Variant Type (for example, “phone” versus “t-shirt”), allowing for variant child items to be configured (see Web Item Variants Template for full details).
Valid Variant Type codes may be determined using the Web Product Variants UDT. To do this, go to Tools > User Defined Windows > ZEDS_VARIANTS - Web Product Variants and view the Code for the desired Variant Type.
If variants are not needed, this field may be removed from the template.
Web Item Details Template
Download: Web Item Details Template
Use this excel template to define advanced details about web items. Web items must first be defined, for example, using the Web Items Template above.
Note that Web Item Details are used to supplement an existing Web Item. Typically, each Web Items entry will have one corresponding Web Item Details entry. The exception to this is the scenario where multiple locales (languages) are added to the system. In the case of multiple locales, there will be more than one entry (one per applicable locale) for each Web Items record.
Web Item Details Template Fields
|Parent Key||Required||Linked Code from parent template||Ensure this code points to a valid Code from the Web Items Template.|
|LineNum||Required||“0” for most cases||Typically, this is “0”.
This line number is used to define distinct details for a specific locale. Increment this number for each distinct Locale entry per Parent Key
|Code||Required||Unique alphanumeric code||This field serves as the key of the record in SAP Business One system and it is mandatory. When adding new data, the value of this field is user defined (e.g., 1,2,3, etc.). When updating existing data, the value of this field should be the real record key in SAP Business One.|
|U_LOCALECODE||Required||“ZLOC0001” for most cases||Local Code defines which locale (language & region) will be used, as defined in the UDT ZEDS_APPLOCALE. “ZLOC0001” (English for Customer Portal) is the only locale that is installed out-of-the-box. Other locales may be used, if properly defined in the ZEDS_APPLOCALE table.|
|U_WEBNAME||Required||Alphanumeric, 254 characters or less.||This defines the web name, as displayed to customers.|
|U_WEBDESC||Optional||Text field with HTML Formatting||This HTML-formatted description may be displayed to customers, provided other template settings are enabled.|
|U_PRIMEIMAGE||Optional||Alphanumeric, 254 characters or less.||This primary image path will be used to load product images as per the templates. This is the image path, relative to the web server.
Best practice is to load images to the website’s /b2b/ folder, as described
|U_THUMBNAIL||Optional||Alphanumeric, 254 characters or less.|
|U_MEDIAFOLDER||Optional||Alphanumeric, 254 characters or less.|
|U_TEMPLATE||Required||“product-details.liquid” in most cases.||This defines which web template the item details should use. The provided template is “product-details.liquid”. Other templates may be customized, in which case, that custom version may be assigned to an item’s details.|
|U_ADDPROP||Optional - This field may be removed or left blank||UDF values as defined in SAP Business One||This additional property may optionally be used to define other details for the item. This definition can be managed using standard SAP Business One UDF management.
This sample property is not provided in the template, as it is an optional UDF. As with any UDF on the Web Items UDO, this field may optionally be added to this template.
|Other custom UDF’s||Optional||As defined in B1||If desired, custom UDF’s may be applied to the Web Item Details UDO in SAP Business One. These UDF’s may then be added to the DTW template.|
Web Item Variants Template
Download: Web Item Variants Template
Use this excel template to assign child items to a Variant parent item.
A few notes:
- Variant child items will not be displayed on their own page, rather, child items are displayed in their parent variant web item details page. If this scenario has already been configured, the variant item may be removed from the variant items list using the parent item’s Web Item Details screen and the item will display as a product on its own page (based on the details configured in the child item’s Web Item Destails screen). Detailed configuration is described in Product Details Management for B2B Marketplace.
- The parent item must be properly configured as a Variant item type. On the parent item’s Web Items Template, ensure that the parent item’s
U_TYPEfield is set to Variant. On the variant child item’s Web Item Variants Template, and ensure the
ParentKeyfield points to the correct parent Web Item Code (not item master code) configured in the parent item’s Web Items template.
- Each Variant child item should only be assigned to a single parent item.
Many child items may be configured for each Variant parent item. For example:
ParentKey LineNum Object U_OITMCODE Parent-A 0 ZEDS_WEBITEMS CHILD-1A Parent-A 1 ZEDS_WEBITEMS CHILD-2A Parent-A 2 ZEDS_WEBITEMS CHILD-3A Parent-B 0 ZEDS_WEBITEMS CHILD-1B Parent-B 1 ZEDS_WEBITEMS CHILD-2B Parent-B 2 ZEDS_WEBITEMS CHILD-3B
- Variant Property configuration may be applied to Item Master Data, four UDFs found on the Item Master Data (OITM) may be used to configure this information. Depending on the Variant Type definition, each UDF may define features of the item, allowing filtering on the Web Item details page. The Variant Type definition determines which (if any) UDF is used for advanced Variant filtering. Use InterConnect Manager Add-On to view the Variant Type property details, then configure the relevant properties in Item Master Data for each child item.
Web Item Variants Template Fields
|ParentKey||Required||Linked Code from parent template||Ensure this code points to a valid Code from the Web Items Template. The assigned parent code must be properly configured as a Variant.|
|LineNum||Required||A unique integer||Line number allows for multiple child items to be assigned to the same parent. Ensure that this number is unique. Best practice is to assign the next integer Line Number for the same
||This defines the parent object type. This field may be omitted. If it’s included, the value must be
|U_OITMCODE||Required||A valid Item Code from Item Master Data (OITM)||This field defines the child Item Code, as per SAP Business One’s Item Master Data.|
Uploading Templates with Data Transfer Workbench
These templates rely on existing data in SAP Business One. As a result, supporting data (such as Items) must already exist prior to using these import templates.
For assistance with filling in these templates, or loading the completed templates into SAP Business One, speak with your SAP Business One consultant.
Using DTW to Import New Web Items
Before beginning data import, prepare the relevant data using the above templates.
Connect to a company within DTW with an superuser (for example, manager) to ensure full data access during DTW data import.
After logging in, press the Import button to open the Data Import Wizard.
Select Master Data.
Select Add New Data to import new Web Item lines.
Select User Defined Data - Objects (UDO) - UDO_ZEDS_WEBITEMS from the Business Objects selection screen.
Select processed data spreadsheets which have been prepared as outlined in the previous sections. Note that Parent data Web Items (ZEDS_WEBITEMS) is required. Child data is optional, and may be updated in a future data import. Child data applies to Web Item Details (ZEDS_WEBITEMDETAILS), Tags (ZEDS_WEBITEMTAGS), and Variant Details (ZEDS_WEBITEMVARS).
Review object mappings to ensure data is as desired. Use the Mapping Rules, Source Data and Target Data tabs to review prior to import.
Select Ignore Up to 10 Errors and Process Valid Records. Press the Simulation button to preview the import before-hand. This is important as it provides an opportunity to review any errors without impacting the database.
Wait for import to complete.
Review import details.
Updates to Existing Web Items
Web Items records are defined in a UDO. As such, when updates are performed to child tables Web Item Details or Web Item Variants, all fields must be included for the Parent row, as well as the relevant child table being updated. For example, if Product Images need to be updated, then all Web Item Details fields and related parent details must be included as per the template. Excluded fields will be loaded into the database as blank.
Also, when updating with DTW, ensure that old records are deleted, otherwise duplicate records will be created during the update. This can be done in the Data Import Wizard screen during Step 5: Map Object Fields. Select Source Data, and select the option to “delete and add” from the bottom drop-down.