Exporting open orders into CSV file

Content


Overview

You can generate a comma delimited file from an open order book manually. This functionality is refered to as Ad-hoc output and is quite useful when you need to produce a file to upload to your shipping company to populate address list and produce shipping labels, or when you need to select a batch of orders and create a fulfilment list for a drop shipper.

Usage To generate a file from open orders

  • Go to Open Orders screen
  • Select the orders you want to be included in the output file
  • Right click > Action > Export into File
  • Select the group name of the Ad-hoc export setup
Note: Ad-hoc export rules must be setup in the system before you can generate output files from open orders screen.

The system will generate a CSV file in the location you have setup in the ad-hoc export configuration.

Only the orders you have selected will be included in the the file. Also, if you have Conditions specified, only orders that fulfil the specified condition will be included in the output

Setup and configure ad-hoc export

Before you can use ad-hoc output you need to configure the format of the files that will be produced and set where the files will be created.

Go to Settings > Import / Export Automation > Data Export > Adhoc Output.

The export settings are arranged into groups which may contain several files which will be produced. For example you can setup ad-hoc export group which will generate 3 files to be uploaded to your shipping courier, such that when you select this group in the Open Orders the system will take all selected orders, run an conditional expression for each order to see which file to include the order into. Lets look at a specific example:

  • Your shipping courier (lets call it FastShipping) requires you to upload a file for orders being shipped to United Kingdom in one file
  • European orders included in a different file which need additional column for Service Enchancment
  • Insured parcels into a completely different file in which you have to specify the value for which the parcel will be insured for.

To accommodate these requirement you will need do the following:

  • Click Add New
  • Enter a new Group Name – FastShipping output
  • Output Type – select the type of file you want to be produced. In this case all we need is customer address and order weight, so all we really need is the header of each order
  • Enter the name of the output step, for the first file enter UK Orders only

export group setup for open orders ad-hoc output

  • Specify the Output Path (i.e. where the file will be created) for example

    d:\FastShippingFiles\ukorders.csv
    (to select the file destination during the export, leave Output Path blank)
  • Conditional Expression – is usually a logical expression to filter the orders being exported, for example for UK orders file, we want to include only orders going to United Kingdom and only those that have PostageServiceName as FastShipping, the expression is:

    PostalServiceName = 'FastShipping' and cCountry='United Kingdom'

  • In the Output Column Mapping you can remap the columns and the order in which they are being outputted, specify the column names being produced and change the format of the file. i.e. In the screen shot above, we have changed the column cFullName to CustomerName.
  • Click Save, to save the settings.
    (At this point you can click Produce File to generate a sample file, which will apply the condition expression to all open orders and create a file as if you have selected all orders in t he open order book)
  • Do the same steps to set up another two types of files, i.e. click Add New, Choose group as FastShipping output, select type as Order header, and then specify where the file will be created and the conditions for order inclusion

  • You will end up with a GroupName : FastShipping output, which contains 3 file generation instructions
  • Go to Open Orders, select the orders you want to export into a file, right click >Action > Export Into File > select FastShipping Output
  • Assuming you have selected the orders that qualify for inclusion into the output file, based on your conditions – three files will be created on your system. Each file will contain the orders that qualify.

Condition Expression

Condition Expression is a a logical statement which checks whether a selected order qualifies for the inclusion into the file. You can check any logical statement (for example 1=2 is a logical statement which checks whether 1 equal 2, which will of course yields false, unless you are an electron). Similarly you can check whether a cCountry field equals 'United Kingdom' etc. You can see the fields you can evaluate in the column mapping selection

Examples of some expressions:

  • checks whether the order value is over 100
    fTotalCharge > 100
  • checks whether the order value is over 100 and country is United Kingdom
    fTotalCharge > 100 and cCountry !='United Kingdom'
  • Status of the order is NOT unpaid the country is United Kingdom and the total value of the order is less than 1.50
    Status !='unpaid' and cCountry='United Kingdom' or fTotalCharge < 1.50
  • Order is less than 31 days old
    DATEADD(d,-31,getdate())>dReceievedDate

Output types

  • Open Order output header – outputs all open orders. Does not include order items
  • Open Order output complete – outputs all open orders, includes all order items, weight and value for each order item. If the order contains two or more items, two or more lines will be created in the output file for each order.
  • Open Order output header (ONLY ONCE) - outputs all open orders. Does not include order items. An order will be included into a file only once, if the order has been outputted into a file before it will not be included. This export routine will put EXPORTED into an Audit Trail for an order
  • Open Order output complete (ONLY ONCE) - outputs all open orders, includes all order items, weight and value for each order item. If the order contains two or more items, two or more lines will be created in the output file for each order. An order will be included into a file only once, if the order has been outputted into a file before it will not be included. This export routine will put EXPORTED into an Audit Trail for an order.
  • Open Order items with default supplier - outputs all open orders, includes all order items, weight and value for each order item. If the order contains two or more items, two or more lines will be created in the output file for each order. The export also looks up default supplier for the stock item and includes the supplier code for the item (if specified). Only linked items will be included.
  • Open Order items with default supplier (ONLY ONCE) – the same as above, but generated only once.
    Assigned Stock in Open Orders – sums up all linked stock in open orders, produces a file with combined stock levels included.