Import Orders Using Standard Mapping File

Modified on Wed, 4 Dec at 1:37 PM

TABLE OF CONTENTS

Setup default import order mapping on an account
Enter Orders in Import File
Import Orders for an Account
Download Import File
Open the Log File

Setup default import order mapping on an account

The default order mapping can be setup on an account from the account details without the need of additional coding from the Dispatch Science development team. The .CSV templates are attached to this article, see bottom of article.

1) Open Accounts and in list of Accounts, click on an Account Name to open its details.

2) In Account Details, in the Order Options section, click on Create Standard Mapping link:

3) You will receive a message, click on OK to create the standard mapping or on Cancel to abort.

Once the mapping has been associated to the account, you will see the confirmation message at the top and the mapping associated to the account.

Enter Orders in Import File

1) Download the import file.

 Order Import supports any file extension type and will convert them to .csv (or .tsv where necessary) when completing the mapping.

2) Fill in the order information:

Required Columns

The following columns should always be filled in:

Dropoff Address Line 1
Dropoff City
Dropoff Province (here you would enter the state)
Dropoff Postal Code (here you would enter the ZIP code)
Service Level (entered as its ID and not its name)
Vehicle Type (entered as its ID and not its name)

Optional Pickup Columns

If the pickup columns are not in the import file or are left blank, the Account User default contact, if setup, will be used to fill in the pickup information. If the Account User does not have a default contact, the Account default contact will be used.

If the Account User and the Account do not have a default contact, the following columns will be required:

Pickup Address Line 1
Pickup City
Pickup Province (here you would enter the state)
Pickup Postal Code (here you would enter the ZIP code)

If the Account user and Account are not setup with a default contact and the columns are left blank, an error will be generated for the order and the order will not be created.

Reference Fields

The first 3 columns will be used to import values in the Reference fields:

Reference Number 1
Reference Number 2
Reference Number 3

Those fields represent the 3 Reference Fields that are setup in the Account Profile. Do not rename those columns even if they are named differently in the profile. They will map to the first, second and third reference field.

The validation in the Account's Order Profile for the Reference fields will be ignored.
This can be turned on if needed. Please contact

The Reference fields' validation setup in the Account's Order Profile is ignored when importing Orders. 
If you would like to have the validation verified and Orders skipped (therefore not get created) when a validation fails, please contact Support to get this validation enabled.

Service Level

The service level should be entered as its ID. You can find this information under Pricing, Service Levels. Open a service level schedule to see the list of Service level IDs.

Ready At and Delivery window

There are 2 different ways to setup the Pickup and Delivery time:

Using Ready At and Service Level

Delete columns Del Date, Del no earlier than, Del no later than (columns D, E, F) or leave them blank
Enter a Ready At date-time in the following format yyyy-mm-dd hh:mm (it is important to format this cell to ensure the time is saved as well
Enter a Service Level ID.

These will be used to automatically calculate the order's pickup and drop off windows.

If no Ready At is entered, the Ready At will use the date and time of the import.

Using the Delivery Window

Enter the date of delivery in the Del Date column in the following format yyyy-mm-dd.
Enter a time in the Del no earlier than to indicate when the delivery can start in a 24 hour format (hours before noon: 9:00 and hours after noon 13:00)
Enter a time in the Del no later than to indicate when the delivery should be completed in a 24 hour format (hours before noon: 9:00 and hours after noon 13:00). The time should be equal to or later than the Del no earlier than.
Enter a Ready At that is NOT EARLIER THAN the Del date and Del no earlier than time
Enter a Service Level ID.

The Ready At will be used to calculate the pickup window, the delivery window will be used as entered, the Service Level ID will be used to calculate the Order Total.

Vehicle Type Id

Vehicle Type Id refers to the ID on the vehicle. You can find this information under Pricing in the Vehicles:

Vehicle type is now a required field when using the new Service Level+Vehicle Filtering.

Pickup and Dropoff Wait Times

If a Pickup Wait Time is entered in the column, it will overwrite the contact wait time when a pickup contact is used (see Optional Pickup Columns) and will be used for the pickup load time.

If a Dropoff Wait Time is entered in this column, it will be added to the order delivery unload time.

Pickup and Dropoff Latitude and Longitude

These 4 fields can be left blank or even removed from the import file if they are not used.

If these are blank or not present in the file, the geolocation of the pickup and dropoff address will be done by Dispatch Science.

If you enter a latitude and longitude, the address will be used for display and the latitude and longitude will be used as the geolocation.

Number Of Pieces and Weight vs Items

Depending on the level of information needed for items, the fields to use will be different.

Number Of Pieces and Weight will keep the order items generic, using the default parcel type and no additional information.

If item information such as description, quantity, barcodes are needed, you will use the fields starting with Item_.

Standard Import File without detailed item information

Number Of Pieces

Enter the number of pieces in the order. A parcel, using the lowest ranked parcel type from the parcel type schedule associated with the account, will be used to create the items.

If you enter 0 piece or leave it blank, 1 item will be created by default.

Additional item fields such as Item_Parcel Type Id, Item_Description, etc..,  are not available when using Number Of Pieces and Weight. To use the additional item fields, please use the fields starting with Item and remove the Number Of Pieces and Weight columns.

Weight

If weight is omitted, it will default to 0. If a weight is entered, it will be split in between each of the parcel as entered in Number of pieces.

Standard Import File with additional Item information and barcode

This file will be used if you want to enter the items or parcels for each order.

On the first row of an order, fill in the information for the order and the first parcel.

On the subsequent rows of the order, only fill in the information for the parcels.

See Filled in Standard with items to be used as an example.csv file at the bottom of this article.

Item Information

Item_Quantity

Item_Quantity determines the number for each item row in the import file. Entering a barcode, description, item user field will copy this information to all items created with the Item_Quantity. Item_Weight is the total weight for all items in Item_Quantity and not the individual weight of each item.

Item_Parcel Type Id

This will be the Parcel Type ID on the Parcel Type Schedule associated to the account.

If this is left blank, the item will not be added to the order since this field is required to create an item on the order.

Item_Description

This will be added in the description field on the parcel.

Item_Weight

This is the weight of the item. If a minimum weight was entered in the parcel type, the weight will be displayed in the order but the minimum weight will be used for the calculation of the weight extra fee.

Item_Length, Item_Width, Item_Height

These field will contain the length, width and height in the unit of measure associated with the parcel type as entered in Item_Parcel Type Id if the parcel type was set as customizable. If entries are added in those columns and the parcel type is not set to be customizable, the order will not be created and an error will be added in the log file.

Item_Barcode Template

This will contain the barcode for the item. If there are no barcodes entered, the barcode will be left blank.

If some of the barcodes are entered and others are left blank, the ones that are blank will be blank.

If no barcodes are entered for any of the items in the order and there is a barcode template setup on the account, the barcodes will be created upon import.

Item User Fields

The Item User Fields can be filled in when importing orders. Please use this name convention:

Item_uf itemuserfieldid

As an example, if I have a User Item field with Id ItemCondition and description Item Condition, enter the header of this columns as Item_uf ItemCondition

It is important to leave a space between the Item_uf and the Item user field ID

Order User Fields

You can add your account order user fields to your import file following the below naming convention.

uf userfieldId

As an example, if I have a User field with Id OrderCondition and description Order Condition, enter the header of this column as uf OrderCondition

It is important to leave a space between the uf and the order user field ID

Extra Fee Columns

If extra fees should be added to the orders, you can add columns with the following header:

Extra extrafeeID

For instance, if I have an Extra Fee with ID BOLSignature and a description of BOL Signature, the column header will be:

Extra BOLSignature

It is important to leave a space between the Extra and the extrafeeid

In that column, you will enter the quantity for the extra fee and it will be calculated according to the Extra Fee Schedule associated to the Account.

If the extra fee is hidden for self-serve or a negative extra fee, an error will be added to the log and the order will not be created.

Reuse Route For Matching Address

A column can be added to the file with the following heading: Reuse Route For Matching Address

In this column, you can enter one of 2 possible entries:
Delivery

Pickup

When this column is used, the created order which matches a route property (Account and Service Level) will be associated to the container route matching the pickup or delivery address location of an existing order already on the route within a 164 ft (50m) radius. If no order is found on an existing route matching the address, the order will be added to the route it would be placed in according to the route plan zone or ZIP.

When using Route Name column, the Route name will take precedence over this column and the order will be placed in the Route container for the Route Plan matching the Account and Service Level and this column will be ignored.

Note: If using Route Name, the route name will take precedence if a Route Name is indicated in the row.

Import User Name

If the import is for one Account, this field can be left blank or removed from the file.

If you are importing orders for multiple accounts, the field should contain the email address associated to the Account User for the order.

Attribute Ids

The Attribute Ids column allows the addition of attributes of type Order or Order and Driver.

If a Driver attribute is added to the column, it will be ignored when importing the orders and an error will be added to the log file.

Format

In this column, add the attribute Ids separated by a semi-column (;) as such:

Attribute Ids

NewDriver;Overweight;Fragile

Usage

If the column is omitted or left blank, the default attributes associated to the account will be added to the order.

If one or more attribute Ids are entered in this column, the attributes in the column will be added to the order and the default attributes associated to the account will not be added to the order.

If only an invalid attribute is added to the column such as incorrect Id or a Driver attribute Id, there will be no attributes added to order.

Other Internal Columns

The following column should be used for internal order import and removed when sending the standard import file to your account users.

Route Name

The Route Name can be added to this column. If the order's account and service level matches a Route Plan, the order will be added to the Route Container accordingly in the Route specified. If the Route already exists, the order will be assigned to the existing Route. If the Route does not exist yet, it will be created and order will be assigned to the Route.

See Driver Number to see how to allocate a Driver to a new Route.

Driver Number

If the Driver Number is added to this column, the order will be dispatched to the driver if order is an On Demand Order.

If order falls in a new Route not yet created on a Route Container and the driver is specified, the new route will be allocated to the driver. If a route already exists on a Route Container, the driver will not be allocated to the Route.

Note: If your driver numbers are not unique, this will create an error and the order will not be dispatched.

Driver Pay

The Driver pay can be added in this column and this will set the predefined commission for the order.

Order Price

If a price is indicated in this column, the delivery price will be overwritten on the order.

For convenience, you have different files that can be provided to your accounts (see attached files that the bottom of the article. Files marked as internal will include the above 3 fields. 

You will also find a file name Filled in Standard with Items to be used as an example.csv which does not contain the above 3 fields but give an example on how to fill in the file when there are items. 

There is no file with the following columns as these can be different by accounts:
Attribute Ids, Extra, user fields, item user fields

Import Orders for an Account

1) Click on Orders and then click on Import then click on Import File button..

2) Click on Choose File and navigate to the file location, select the file and click Open. File name will show in the field.

3) Select the Import Type

By Account

When By Account is selected, you will be able to select the account and the user of the account.

For Multiple Accounts

When selecting For Multiple Accounts, you will need to add in the file the Import User Name column which should be the email of the account user for each order.

Select the Mapping.

4) Click on Import to start the importing process.

You will be returned to the list of Import with the status as Requested:

5) You can refresh the page using the browser refresh or the refresh at the bottom right of the list to see the updated status or you can leave this page and you will receive an email when the import is done.

An email will also be sent to the support email associated with Dispatch Science and the selected user.

Once the file is imported, you will see the number of imported orders out of the total number of orders in the file.

You will also see if there were any warnings on the import.

Download Import File

The Import file that was used for an import can be downloaded from the Import list.

1) In the import list, on the right hand side of each row, you have a download menu. When clicking on the download menu, you can download the original import file.