How do I use the LTL Rate Maintenance utility to manage my rates from Excel?

LTL Rate Maintenance Utility

This utility was originally written to be used by Artronix support staff to help new customers help manage FreightWare rate sheets from Excel spreadsheets.  Since then, it has been enhanced to allow updating of existing rates in addition to loading new rates.  This utility is not part of the standard product. 

Upon running, the first dialog is to establish communication with the target database on the proper server.  You will either be asked to confirm your established connection or establish a new connection if one is not found.

1 - Select the server where the target database resides.
2 - You must have a Windows login that permits access to the target database.
3 - Select the target FreightWare database.
4 - Test the connection.

The next dialog will again confirm your connection with another Test Connection button.

1 - Click the Test Connection to confirm the connection.
2 - Choose whether you will be exporting rates into Excel or importing rates from Excel.

The format of the Excel file is extremely important with this utility.  Any deviation from this format will result in a failure of the procedure. It is best practice to Export a rate sheet into Excel.  This resultant Excel spreadsheet will be formatted correctly.  Artronix can provide an initial formatted spreadsheet if necessary.

Export Rates into Excel

This procedure will export rates out of the FreightWare rating system into an Excel spreadsheet. 

1 - Select the Rate Sheet to export.
2 - Effective On: In FreightWare, rate sheets can have multiple rates for the same points depending on the date.  Specify any date in which the version of the rates you want to export were in effect.
3 - Output Filename: Accept the default or specify.

Clicking the Export to Excel file should display the number of rows in the Excel worksheet and land the file in your Documents folder.

The file should be in the following format.

The last 3 columns, LowLimit1, Rate1, RateType1 repeat as a group of 3 columns 11 more times.  FreightWare allows up to 12 rate (weight, quantity) breaks.

When importing, all columns must be present but some may not need to be populated.  * indicates the column data is mandatory and must be populated to Import Rates from Excel.

Rec ID: This is the sequential line number and only appears when a spreadsheet is the result of an export.  You do not need this column to be populated if this rate sheet is to be imported.
*RateSheetID: This is the name of the rate sheet. 
*RateBase: The rate base is determined by the origin and destination points.  Many points in the same geographic area will typically index to the same rate base.
RateBaseHigh: Not used at this time.  Column must be present but not populated.
*CommodityClass: Typically used to identify the class of freight (70, 92.5, 100, etc.) as rates change with the density of the product.  FAK can be used when the class doesn't matter.
*Effective:  Date the rates take effect.
*Expire: Date the rates expire.
*Minimum: Set to zero if no minimum charge.
*Maximum: Set to zero if no maximum charge.
LinkID: This field is the index used when updating existing rates rather than creating a new set of rates.  When you import, you will be asked whether you are updating or creating.  If updating, then use an exported spreadsheet, which will provide the LinkIDs for each of the rates. 
LowLimitx: where x is a value 1 through 12.  This is the first of a set of 3 columns for each weight or quantity break in a rate sheet.  LowLimit is the lowest weight or quantity in that break.
Ratex: where x is a value 1 through 12.  This is the rate if the weight or quantity is at least the low limit in the previous column.
RateTypex: where x is a value 1 through 12.  This indicates if the rate is weight-based or quantity-based.  0 = per cwt.  1 = flat rate.  2 = per individual lb.

Import Rates from Excel

This procedure will read the rates from the Excel spreadsheet and import them into the FreightWare rating system.

1 - Select the spreadsheet (workbook).
2 - Select the tab (worksheet) that contains the rates to import.
3 - FreightWare allows rates to be different for different date ranges.  When rates change, it is recommended that you create a new instance of the rates with a different range of effective/expire dates.  This this case you would leave the Replace Existing checkbox UNCHECKED. 
      Note: Checking the Replace Existing Rate Table REPLACES rates.  Generally you do not want to do this.  Consult with Artronix before using this selection.