Topic: Data Transfer Workbench – DTW

1. Topic: Data Transfer Workbench – DTW Topic:Using Transform Tables in Data Transfer Workbench E-Learning curriculum SAP Business One

2. Topic Objectives At the conclusion of this topic, you will be able to: • Describe the rules for creating data files according to a certain data structure • Describethe data structure of the various objects • Import data files to the different objects

3. Data Structure • In this lesson we are going to review the basic rules for creating data files • You need to define the files according to a certain data structure • Some data structures are general and are relevant for most object types • Some data structures are object dependent • Within an object, there are one or more data files ,depending on the object structure • For each data file you need to define a different data structure containing the details you want to import

4. Header Details Data Structure – General • Record key:

5. Data Structure – General • Links to another table:

6. Transform Tables • Transform Tables: • Transform table must contain 2 columns: Source and Target • Save your transform table as a Comma Delimited file • You can use a transform tablein future imports

7. Demo - Transform Tables

8. Exercise

9. Data Structure for Update Mode • All the key fields in the templates have to be filled • Fields that you want to update - fill them with new data • Fields that you do not want to change - leave them blank • Fields that you want blank - fill them with ‘\’ • Note: The Update function will not delete any line items from an object (Full record)

10. Data Structure for Update Mode - Details Files • When you update only Details data files - prepare a Header file as well • The line number determines which records in the database will be updated • The line number is mandatory in an Update mode • The first line must start with 0

11. Data Structure for Update Mode - Details Files

12. Drawers code: Assets 100000000000000 (14 Zero). Liabilities 200000000000000. Data Structure – Chart of Accounts • Drawers cannot be imported • It is recommended to import all titles first • When updating accounts use the internal system number in the Code field • Make sure that columns which represent account code are set to Text before saving the file

13. Demo - Chart of Accounts

14. Exercise

15. Header Details Data Structure – Business Partners Header data file: Business Partner Type: CardType • cCustomer • cSupplier • cLid Mail Address information: • ShipToDefault  Default Ship to address • MailAddress  Ship to address Bill To information: • Can be entered in the header or in the details data file • State  Country • ## Currency Contact Person: • Default contact person

16. Data Structure – Items EvalSystem: Define the evaluation system for the item: • bis_MovingAverage/ bis_Standard/ bis_FIFO G/L Method: • glm_WH/ glm_ItemClass/ glm_ItemLevel Add Warehouses: • ‘Auto. Add All Warehouses to New Items’ (System Initialization General Settings  Inventory Items) • 'Add all items into Scheme if can't find corresponding item when updating'  Price Lists: • In the Data file Items Prices DTW uses the field LineNum to index the sequence of the price lists. The LineNum starts from ‘0’ which indexes the first one.  

17. Header Details Data Structure – Marketing Documents

18. Data Structure – Marketing Documents Document Number – This field is mandatory if a manual numbering has been determined Object Type - When importing drafts, use the document object type

19. Data Structure – Service Marketing Documents • ‘DocType’ - In the Header file, type the value dDocument_Service for Service documents • In the Rows file, skip the ItemCode column • The G/L account code is mandatory for Service documents • Import open documents from a legacy system  use the Opening Balance account

20. Demo – Service Marketing Documents

21. Exercise

22. Data Structure – Inventory Documents • Stocktaking After an import of an Stocktaking – perform a Stock Posting in Business One • Choose the range of items, the price source for the transaction and then reconcile • Stock Transfer Make sure you create a header and details file, similar to marketing documents Note: customer information is not mandatory

23. Data Structure – Journal Entries Details data file: • Account Code - When you import journal entry lines with G/L accounts enter the G/L account code in the AccountCode field • Using Segmentation – Enter the internal system number of the G/L account • Business Partner Code - When you import journal entry lines with business partners, enter the business partner code in the ShortName field

24. Importing using ODBC

25. Importing using ODBC • In multiple structure objects  Identify one of the columns as a record key, for the header and details data files • This column must be a unique key in the header data file Example: Select CardCode as RecordKey, * from OCRD where GroupCode = ‘105’ • To copy specific fields: Select CardCode as RecordKey, cardcode, cardname, fax from OCRD where GroupCode = ‘105’

26. Troubleshooting • Warning: backup your database before every import Tip: work with accordance to Business One Tip: work with DI API help files

27. Topic Summary You are now able to: • Describe the rules for creating data files according to a certain data structure • Describe the data structure of the various objects • Import data files to the different objects