Page tree
Skip to end of metadata
Go to start of metadata


This document explains how to export rate cards to do bulk updates to rates before re-importing to CartonCloud.


Overview

When exporting transport rates to Excel, only the rate ids are downloaded making it difficult to relate the rate to the customer charge card.  A way around this is to download both the Transport rates and the charge rates.  You cn then marry up the id in both files and clearly identify the relevant charge card while updating rates.

Files to Export and Marry up

These are the 2 files to be exported so that the linking of the charge card to the transport rates can be performed.

NB:  the files names can be confusing after download.

  1.  Filename is customer_rate_charges.csv
  2. Filename is rates.csv

For the 2 files to marry up at a row level, the id (column G) from customer_rate_charges (File 1.) must match the id (column A) from file named rates.csv (File 2.).

Once you've exported the files, open in excel.  Insert enough columns (7 columns minimum) to the left of column A in File 2.  Copy and paste the columns from File 1. into the newly available columns.

Check that the Id number from the inserted file matches the id number from the base file.  If there is a miss match, insert cells to data on the inserted table until both ids match.

Matched                                                                                                                                                                                        Unmatched


Note:  Each Rate has a unique ID.

Once you have linked the rates table to the customer rate charges table, you'll be able to update charges for a particular rate card.  Below is a table of headings that explains the columns in the rates file (File 2.). It's based on an example of consignment types Kegs, Cartons and Pallets.

Any Consignment type that has been ignored in the Rate card will not be exported.

Rate ComponentHeadingDefinition
Overall Rate InformationRate.idThis is the rate id and must match the id from the Customer Rate Charges file.
Rate.rate_class_idThis defines the Rate Card type (Income or Expense)
Keg Consignment Type DataRate.Data.Keg.calculation_methodThis defines the calculation method for the associated consignment type. (1 = non-cumulative, 2 = cumulative)
Rate.Data.Keg.per_rateEnter the value of the rate being charged for this id. 'Null will be shown if there is no entry in this field
Rate.Data.Keg.rate_typeThis defines the rate type for the associated consignment type. (1 = flat rate, 2 = sliding scale)
Overall Rate Information for Field Rate TypeRate.Data.allow_zero_chargeThis is the flag for allowing zero charges.  It's found at the bottom of the rate card. ('0' = don't allow, '1' = allow zero charges)
Rate.Data.apply_fuel_levyThis is the flag for applying fuel levy.  ('0' = no levy, '1' = apply levy) associated with Field Rate
Carton Consignment Type dataRate.Data.cartons.calculation_methodThis defines the calculation method for the associated consignment type. (1 = non-cumulative, 2 = cumulative)
Rate.Data.cartons.per_rateIf you have chosen sliding scale as the rate type, then this field should be = 0 as its based on field rate
Rate.Data.cartons.rate_typeThis defines the rate type for the associated consignment type. (1 = flat rate, 2 = sliding scale)
Rate.Data.cartons.rates.0.fromEnter the starting qty for the first sliding scale component
Rate.Data.cartons.rates.0.rateEnter the $ value for the first sliding scale range.
Rate.Data.cartons.rates.0.rate_typeThis defines the rate type for the associated consignment type. (1 = flat rate, 2 = sliding scale).  In this case should be '2'.
Rate.Data.cartons.rates.0.toEnter the ending qty for the first sliding scale component
Rate.Data.cartons.rates.1.fromEnter the starting qty for the second sliding scale component
Rate.Data.cartons.rates.1.rateEnter the $ value for the second sliding scale range.
Rate.Data.cartons.rates.1.rate_typeThis defines the rate type for the associated consignment type. (1 = flat rate, 2 = sliding scale).  In this case should be '2'.
Rate.Data.cartons.rates.1.toEnter the ending qty for the second sliding scale component
Overall Rate Information for Drop Rate TypeRate.Data.drop_rateIf 'Drop Rate' Rate type is used, then enter the $ rate
Rate.Data.fuel_levy_percentageThis is the flag for applying fuel levy.  ('0' = no levy, '1' = apply levy) associated with Drop rate
Rate.Data.max_chargeMinimum charge for this rate card
Rate.Data.min_chargeMaximum charge for this rate card
Rate.Data.minimum_chargeThis is the minimum charge for the rate card after testing the rate charges for a consignment
Pallet Consignment Type dataRate.Data.pallets.calculation_methodThis defines the calculation method for the associated consignment type. (1 = non-cumulative, 2 = cumulative)
Rate.Data.pallets.per_rateIf you have chosen sliding scale as the rate type, then this field should be = 0 as its based on field rate
Rate.Data.pallets.rate_typeThis defines the rate type for the associated consignment type. (1 = flat rate, 2 = sliding scale)
Rate.Data.pallets.rates.0.fromEnter the starting qty for the first sliding scale component
Rate.Data.pallets.rates.0.rateEnter the $ value for the first sliding scale range.
Rate.Data.pallets.rates.0.rate_typeThis defines the rate type for the associated consignment type. (1 = flat rate, 2 = sliding scale).  In this case should be '2'.
Rate.Data.pallets.rates.0.toEnter the ending qty for the first sliding scale component
Rate.Data.pallets.rates.1.fromEnter the starting qty for the second sliding scale component
Rate.Data.pallets.rates.1.rateEnter the $ value for the second sliding scale range.
Rate.Data.pallets.rates.1.rate_typeThis defines the rate type for the associated consignment type. (1 = flat rate, 2 = sliding scale).  In this case should be '2'.
Rate.Data.pallets.rates.1.toEnter the ending qty for the second sliding scale component
Overall Rate InformationRate.Data.percent_of_invoiceIf '% of Invoice' Rate Type is used, then enter the % amount here.  If not, then Null
Rate.Data.rate_typeFlag for defining Rate Type (Field rates = 1, % of Invoice = 2, Drop rate = 3)
Rate.Data.use_highest_calculated_rateFlag for using Highest Calculated Rate (0 = no, 1 = yes)


Once you have updated and saved the rates on the Rate File.  You can import the file back into CartonCloud and then do a thorough check that the rates have imported correctly.








Links to further detailed reading

Please click on the links below for further information relating to this or follow the links via the menu on the left.






  • No labels