Importing data from an ERP system, other system, …

1 Importing data from an ERP system , other system , or database . (Note: If your ERP system is SAP and you would like to hire a consultant to set up data exchange between SAP and CargoWiz, contact us at While we provide a method Easy Import From Excel for quickly bringing in cargo data on a one-time basis, there can be situations where more frequent Importing with an ERP link is desirable. Once set up, it can present the user with only the cargo currently intended for shipment rather than a pool of cargo items from which to select. With the Excel method the user typically has many more cargo items displayed than are required for the shipment. He must wade through them (or use Find) to locate and set the shipment Quantity. Some CargoWiz users do not have a stable pool of cargo, rather it will usually be new, as in the case of logistics provider with a great many customers who have a variety of cargo.)

2 Some manufacturers have thousands of products, it is just much more convenient to bring in only the items on a particular shipment. For the ERP programmer: You program the ERP system to create a tab delimited .txt file with cargo data of certain types and in cer-tain tab positions. User action to initiate import File , Import from ERP system or other data source User browses to the location where the .txt file is to be found. User opens the file. The incoming data is given a validation check by CargoWiz. If invalid data is found a log file is created on the user s desktop with details of the errors. Invalid data triggers restoration of the previous grid data. A message advises of the failure. If the data is valid the cargo grid is filled with it and it becomes the active shipment ready for loading.

3 A message appears announcing success, and the message closes automatically in a few seconds. Import File Format:: Note: Two files are provided in case you wish to see or do test runs with them. One is ERP Import test with Errors and the other is ERP Import test without Errors . They are in the user s Application Da-ta\CargoWiz\ERP folder. Here is an example of a small Import txt file displayed in Notepad: The data must be as follows: Top Row It must have the column headers spelled exactly as shown here. In some cases they are short versions of column heads in the program. ShortID and Description, which might have lengthy contents, have been moved to the right end to help keep columns aligned to the extent possible. Line Your routine will need code to increment the line count, and place these numbers as shown.

4 Qty Must be an integer, or empty. If empty CargoWiz will set it to 0. Length, Width, Height Must be numeric and not empty. Many places after a decimal may be used. Orients This is Orientations Allowed and refers to restrictions on how the cargo may be turned. There are 4 acceptable values, 1,2,4 and 6. 1 means it cannot be turned 2 means it can be turned horizontally 4 means it can be turned any which way, but not tipped up on either end. For instance a box of fishing rods might be unstable on end. 6 means it can be turned any which way. Max Stack Must be either empty or a nonzero integer. Empty is the most common value, which allows the algorithms to stack without restriction if there is space. Refers to Max Can Stack . A value of 1 indicates one identical cargo of the same type can be stacked above the bottom Bottom Must be empty or 1 or 0.

5 Usually empty. The value 1 interprets to true, empty and 0 to false. Re-fers to the Boolean value of Bottom Only If bottom = true the algorithms will only load the cargo on the truck bed, not on other cargo. Group Must be empty or an integer. Usually empty. If set, other cargo with the same number become part of that numeric group. Group 1 is loaded first. Refers to Loading Priority or Group column. Useful for deliveries. Weight Must be numeric or empty. Many places after a decimal may be used. Units Must be empty or an integer. Usually empty. Short for the Units Inside column. It is a convenience col-umn for users who need to deal with Total Units as a business quantity. For example, a customer might order a thousand pair of shoes, in reality 12 shoe boxes are placed in a carton.

6 CargoWiz cares only about the cartons that it must load, but if 12 were placed in the Units Inside column it will display 12 x the Quantity in the Total Units column. ShortID Can be empty or any string, including strings with numbers and characters. Usually it is a Part Num-ber or SKU. If empty CargoWiz will assign a number to, which can help in understanding the load layout. Description (Same requirements as ShortID) Can be empty or any string, including strings with numbers and characters. Usually it is a Part Number or SKU. If empty CargoWiz will assign a number to it, which can help in understanding the load layout. Taboo OnTop Usually empty. To prevent some cargo types from being placed on the cargo, create a comma delimited string of the Record IDs of cargo types that are to be Taboo On Top.

7 To obtain the Record ID for a Cargo, select it in the cargo grid, then hover the mouse on the color box until the record ID Tool Tip appears .of the row. An example string might be 47,23, 5 . _____ Preferred location for the import file: The File Open Dialog defaults to open to c:\Documents and Settings\<User>\Application Data\CargoWiz\Shipments folder (Path may be different depending on OS) but will remember the last folder visited, helpful if you want locate the import files elsewhere. Return of Loading Results for the ERP system Any time a truck or container is loaded the Export Loading Report to ERPs selection becomes enabled under File on the menu. The exported file is given the same name as the imported file, but prefixed with EXPORT OF so that the re-trieving ERP can recognize it as a results file.

8 This could be convenient. Perhaps include a PO number in the filename and the ERP can relate it to other info. The created file is placed in the users CargoWiz\ERP folder with some data such as floor length taken, cargo counts, etc, as can be seen in the sample EXPORT OF Import test without in the ERP folder. It also sends top and angled view images (.bmp) of the loaded truck/container to the folder, although this is typi-cally insufficient to reveal all of the cargo loading steps. _____ Sample code VB6: Here is some sample code in VB6 that creates a txt file for manual import by the user. If you create code in an- other language we would be happy to add it here and give you credit if desired. Private Sub CmdCreateTabbedFile_Click() Note: this examples works on an already existing recordset, you may need to create one.

9 '!!!!!!!! Write a txt file for CargoWiz from ERP Recordset !!!!!!! Dim fso As FileSystemObject Call GetUsersDestopPath Set fso = New FileSystemObject 'Creates a text file UsersDesktopPath & "\From ERP For ", True 'Creates a FileObject Set OutFromErpFile = (UsersDesktopPath & "\From ERP For ") 'Opens the file as TextStream Set OutFromErpFile = (ForWriting, True) 'Next 3 lines create the Header line, which looks like this, but with bigger tab space: 'Line Qty Length Width Height Orients Stack Bottom Group Weight Units ShortID Description "Line" & Chr(9) & "Qty" & Chr(9) & "Length" & Chr(9) & "Width" & Chr(9) & "Height" & Chr(9) & _ "Orients" & Chr(9) & "Stack" & Chr(9) & "Bottom" & Chr(9) & "Group" & Chr(9) & "Weight" & Chr(9) & "Units" _ & Chr(9) & "ShortID" & Chr(9) & "Description" & Chr(9) & ("Taboo On Top" Set rs = ' set my variable, "rs", to the recordset object For x = 1 To linecounter = linecounter + 1 linecounter _ & Chr(9) & ("Quantity") _ & Chr(9) & ("Width") _ & Chr(9) & ("Depth") _ & Chr(9) & ("Height") _ & Chr(9) & ("Orientations Allowed") _ & Chr(9) & ("Max Can Stack (No stacking = 1)") _ & Chr(9) & ("Floor Load Only"))

10 _ & Chr(9) & ("Load Group") _ & Chr(9) & ("Weight of Pallet/Crate (with product)") _ & Chr(9) & ("Units Inside") _ & Chr(9) & ("Part Num") _ & Chr(9) & ("Description") & Chr(9) & ("Taboo On Top" ") Next End Sub Notes: 10-3-11 Changes made in the program and in this document. Primarily there is no longer a need to have the trailing \ in the path for robotic operation. There was also a problem parsing it in the program since the addi-tion of Tabu on Top. 2-5-15 Small update for Taboo in vb6 code