Excel Import
General
The Excel Import File functionality offers the option of importing TMS-order from an Excel-file.
Functionality
On the page: Excel Import File Format List multiple import formats can be created. This offers the option to create import formats for various document types like: WMS-Receipt, WMS-Shipment and TMS-orders. When creating TMS orders based on a Excel Import File Format the functionality: Address Import Analysis is applicable.
Tab: General
This tab contains the general information on the Excel Import Format.
Actions
| Action | Description |
|---|---|
| Import Document | Starts the wizard to import orders from an Excel file. |
| Copy File Format | Copy File format, creates a copy of the current selected record. |
| Init | Intializes fields on the header and lines based on the field: Document Types. |
Fields
| Field | Decription |
|---|---|
| Code | An unique code for the Excel Import Format. |
| Description | A text which describes the Excel Import Format. |
| Filename Reg. Ex. | - |
| Line start at row | Must contain the linenumber on which the orderdata is recorded in the Excel file. |
| Application | Must contain the value: TMS. |
| Document Type | Not applicable when the application contains the value: TMS. |
| Document Type(TMS) | Select the value applicable, normaly: Order. |
| Customer Order | Only to be filled when the Excel Import Format is applicable on a specific customer. |
| Order type | Only to be filled when the Excel Import Format is applicable on a specific order type. |
| Blocked | Check if the Excel Import Format is no longer valid. |
| Do not Open Card After Creaton | - |
| Add Files as Recordlink | When the Order is created a copy of the Excel-file is added as a document link. |
| Skip Inventory Check | Not applicable on TMS. |
| Read DateTime in UTC Date | Check if the imported dates/times do not match the values in the Excel file. |
Tab: Line
For each table/field a line is created automatically. Only lines containg a Range value or a Default value will be imported.
| Field | Decription |
|---|---|
| Destination Table | Value: Header or Line. |
| Description | The name of the field in the 3PL - database. |
| Range | Enter the column number of the Excel file which contains the data to be recorded in the field. |
| Max. Length | The maximum length of the value to be imported from the Excel field. |
| Default Value | When filled the value will be set in the field. |
| Mapping Code | - |
| Format | - |
| Split Document | Must be checked on the header-line containing the field: External Document No. |
| Type Code | - |
| Skip Row Filter | - |
Warning
On the Tab: Fields, destination table: Line, Description: No. the field: Range or Default must contain a value. If left empty no goods lines will be imported.
Settings
The settings on the pages: Address Setup are applicable.
Best Practice Example
The following steps describe how to create new orders using the Excel Import Format.
Step 1. Download Excel file with orders
Click on TMS Demo Data Order Import) to download an Excel-file with some demo-orders.
Step 2. Open Import page.
To import orders from an Excel-file the the following options apply:
- Go to page: Order List and click on action: Functions > Other > Import from Excel
- Go to page: Excel Import File Format List and click on action: Home > Import Documents
The following window will be opened to upload the Excel-file:
Note
If multiple Excel Import File formats have been created you will be requested to select a Excel Import File format before the window to upload a file will be shown.
Step 3. Orders imported
On the Role Centre the Cue: Orders Import will show the number of orders imported.
- Click on this Cue to open the page: Order list.
- Select all lines
- Click on the Action: Next Status to change the status to: 06-CHECKAUTO.
Note
- Based on the address details received in the Excel-file, document-addresses will be created on each order. The document-addresses can be consulted by clicking on Action: Related > Order > Document Addresses.
- This proces can be automated by enabling the function-set(Set status: 06-CHECKAUTO) on the status: 05-IMPORT
Step 4. Check Document Addresses
On the order(s) will be checked if an excisting address can be applied on Document-addresses. If addresses are found on both the From- or To-address the the order-status will change to: 06-CHECKAUTO. If no addresses are found, or only on the From- or To-address the order-status will change to: 06-CHECKMANUALLY. On the role-center 2 Cues will inform users to start actions:
- Cue Order Check: Contains the orders which have require attention before they can be processed.
- Cue Address not found: Contains the documents-addresses with no link to an existing address. By Clicking on this cue the page: Address Import Analysis will be openend.
Step 5. Address Import Analysis
The user can link the document-address to an existing address or create a new address based on the document-address. See page: Address Immort Analysis for more help. When an address is applied on both the From- and To-address the orderstatus will be changed to: 15-CALCULATE
Step 6. Order Calculate
On changing the status to: 15-CALCULATE automatically the functions included will be executed. For example: a check on mandatory fields, calculated the distance on the order and execute Contract Strategy so revenue-lines are generated. When executed the status of the order will be changed to: 20-RELEASED.
Remarks per demo order
| Customer Reference | Remark |
|---|---|
| 45070120 | As this is the first order import, the 2 document addresses have to be linked to an existing addresses. Step 1 to 6 apply. |
| 45070237 | As the From-document-address has been linked when processing the previous order only the To-document-address has to be linked to an existing address. Step 4 to 6 apply. The order contains 2 goods-lines as both line 4 and 5 in the Excel-file contain the same value in Customer Reference(column: v) |
| PO78035 | As the To-document-address has been linked when processing the first order, only the From-document-address has to be linked to an existing address. Step 4 to 6 apply. |
| 45070461 | As the document-addresses on this order have already been linked to an existing addresses no address-analysis will be required and the orderstatus will automatically processed. Only step 6 applies. |
| 45070499 | As this order contains an address for wich no match can be found based on the geo-coordinates the user will have to create a new address. |