Export Wizard

The Export Wizard program can be used for various exports, including fuel taxes and accounting data. For example, it can be used to export data for:

  • Great Plains Accounting

  • IDSC

  • Inter-Tax

  • MacroPoint

  • Optimum Solutions Export

  • PC*Miler

  • T-Chek

  • TCG LTL/Traffic Costing Info (SMC3)

Each of these export types requires a specific TruckMate License as described in Setting up the Export Wizard.

The wizard is dynamic in that the pages/questions you see vary slightly depending on the export Type selected. Some of the available screens are:

  • Sub-Types (if a type exports multiple files, you may choose to not export them all)

  • Filters (you may not want to export all transactions: you will be prompted for a date-range, or month, etc.)

  • File directory (where you want to write the file(s)).

Once the export files have been generated, the Export Wizard provides features that allow you to see a summary of which files have been generated, how many records are in each file and the ability to open/view the files. You may quit the wizard once the files have been generated (if your goal was simply to create the files) or you can carry on and FTP the file(s) to a website.

To access Export Wizard:

From this location Go to

TruckMate Menu and TruckMate Task List

  • TruckMate 2023.x and later:
    Imports & Exports > Export Wizard
    All export formats are available from this option.

  • TruckMate 2022.x and earlier:

    • Utilities > Export Wizard

    • Fuel Management > Export to Inter-Tax

    • Fuel Management > Export to PCMILER Fuel Tax

    • Fuel Management > Export to T-Chek

Windows Start Menu

TruckMate 20xx > Export Wizard

Also, in TruckMate 2022.x or earlier:

  • TruckMate 20xx > Export to Inter-Tax

  • TruckMate 20xx > Export to PCMILER Fuel Tax

  • TruckMate 20xx > Export to T-Chek

Windows Explorer

C:\Program Files (x86) > TruckMate 20xx > ExportWiz.exe

Setting up the Export Wizard

Requirements

Each of the export types requires a specific TruckMate License.

TruckMate License Number Export Type

29001

Inter-Tax

29002

T-Chek

29003

PC*Miler

29004

Great Plains Accounting

29005

IDSC

29007

Optimum Solutions Export

29008

TCG LTL/Traffic Costing Info (SMC3)

29009

MacroPoint

Setting up Application Configurator for Export Wizard

Configure Export Wizard using the options found under Configuration Options > EXPORTWIZ.EXE.

Option Description

Allow FTP Settings Change

  • True (default)
    Allows users to change the FTP settings during a Fuel Tax Export.

  • False

Allow Path Change

  • True (default)
    Allows users to change the path where the export file will be created.

  • False

Billing Register Export – Goup ID:Type ID

Indicates the Export Group & Type being used to export TruckMate A/R to the system of record. This value will identify the EXPORT_LOG_DETAIL entries to examine when calculating the credit a client has used up. If an order has gone through BILLBR.EXE but has not been exported yet, it will be included.

Default FTP Location

Enter a default FTP file location on the FTP Server.

Default FTP Server

Enter a default FTP Server address, usually in the form of 'ftp://hostsitename.com' or 'ftp://198.161.0.1'.

Default Path

Enter a default path that will contain the files needed for importing.

Link DrvPay to Trip

  • True (default)
    TruckMate will include a link associated the driver pay records with the records from the trip.

  • False

Use Global Configurations

  • True
    Use the same settings (as configured above) for all Users of the Export Wizard.

  • False (default)

Setting up Security Configuration for Export Wizard

There are no specific options available in the Security > Business Events section of Security Configuration directly related to the Export Wizard executable.

Creating the Export File Directory

The Export Wizard will create export files and requires a file directory location indicating where the files will be stored. Create this file folder before running the Export Wizard. There are no requirements for a specific name or location.

Customized TruckMate code

For TruckMate users who are already using the TruckMate Export to create integration files and some of the export file creation routines have been customized, you will need to contact TruckMate support in order to upgrade to the latest TruckMate version. A custom "stub" will need to be created to allow the latest version of TruckMate to use your customized code.

Automatically select an export type

Each of the export options have been assigned startup codes (e.g. -g1, -g2, -g3) to allow the software to start the Export Wizard with an Export Type already selected. For example, the following menu options are already setup and available within the TruckMate Task menu where the -g# has been set to the startup command line parameter for the executable name.

Export to Inter-Tax

-g1

Export to T-Chek

-g2

Export to PCMILER Fuel Tax

-g3

For example, …​\ExportWiz" -g3 for Export to PCMILER Fuel Tax as seen in the TruckMate Task Menu for Fuel Management.

image1

The startup code numbers correspond to the export type ID numbers so you can create your own MS Windows shortcuts to start the Export Wizard with some other export type already selected. For example, "…​\ExportWiz" -g8 would start with TCG/LTL/Traffic Costing Info already selected as the export type.

image2

If you run the basic Utilities > Export Wizard from the TruckMate Task menu, no ID number is supplied as a default so you will be prompted for the Export Type.

Running the Export Wizard as a service

Some of the Export Wizard export types can be run as a Windows service.

For example, the IDSC Fuel tax export type can be added and configured as a service called Export Wiz within the TMW Windows Service program. The Great Plains Accounting export type can be can be added and configured as a service called Export.

image3

Export Wizard Process Overview

The basic steps for using the Export Wizard program are:

  1. Select export type

  2. Select the export sub-types

  3. Set the date range filter

  4. Indicate the file directory location

  5. Create the export files

  6. Review the export log

  7. For FTP uploads:

  8. Select the export files to upload

  9. Select and configure the FTP site

  10. Review the FTP log

These basic steps are common to all the export types except where noted are described in more detail below:

Step 1 - Select Export Type

When using the Export Wizard to generate integration files, click Next on the Welcome window to start the procedure.

image4

Select the Export Type.

Note: You will have to clear the Type field if it is defaulting to some other export type in order to select the desired value from the complete list of available types.

image5

If you want to restrict which export types are available in the list, use the Edit Types button.

image6

Note: An additional Export Type 'XML' is also available but requires custom code. If your company is interested in XML format exports please contact a TMW Systems representative to find out more details.

To proceed with the Export Wizard process, you require a separate License Key for each of the available export types. If you do not have the required License Keys, a small warning window will appear and you will not be able to create the export files for this export type. Contact TruckMate Support to obtain the proper License Keys.

Step 2 - Select the export sub-types

Most export types are also associated with their own specific set of Sub-Types. Use the Check All and Uncheck All buttons to quickly select the sub-types, or select them individually with your mouse.

image7

Note: Some export types do not require the selection of sub-types. For example, the IDSC export type has no sub-types so it only requires a date range to be defined and the Optimum Solutions Export type requires you to select a Payroll Batch Number.

Step 3 - Set the date range filter

Enter values into the From and To date fields to provide a date limitation for your export files. Click the calendar button to select the months, year and date.

image8

Note: Some export types (e.g. Macropoint and Optimum Solutions Export) do not require you to indicate a date range.

Step 4 - Export file directory location

Use the ellipses image9 button to indicate the location of the file directory where you would like to have the generated Export files created and stored.

image10

Include Column Headings

Some export types (e.g. Optimum Solutions Export and T-Chek) offer a Include Column Headings checkbox to include the column headings as header text at the beginning of the export files to make it easier to identify the column contents.

image11

Step 5 - Create the export files

Once you have entered the file directory and click Next, it may take a few minutes to process the data. An "Exporting…​ please wait until done (when buttons are re-enabled)." message will be displayed and the bottom buttons will be disabled until the export process is complete.

image12

Note: If the process is taking too long and you wish to cancel, click the Abort button.

After you abort, you will not be able to proceed with the FTP portions of the process. To continue the export, click the Back button and regenerate the export files.

Once the export files have all been created, the buttons will be enabled and the export log file will be updated.

image13

Note: The Save, Print, and Clear buttons are all associated with the export log text file. They are used to save and display the results of the export process.

The View button allows you to select any of the export files in the export folder and review them for accuracy if you have the right program for viewing the file types. For example, MS Excel or Notepad for viewing CSV files.

Go to the file directory where the files were created if you want to see the resulting set of .CSV export files. The folder will contain a variety of different file types depending on the export type.

image14

Warning: If a file of the same name already exists in the folder selected to store the export files, it will be overridden without warning.

Step 6 - Review the export log

The Save, Print, and Clear buttons are all associated with the export log text file.

image15

Save

Saves the details of the creation log displayed in this window as a text file in the file directory. A standard Save As window will be displayed prompting you for a filename and location.

Print

Prints the creation log text to the default printer setup for this workstation.

Clear

Clears the creation log text.

Step 7 - FTP: Select the export files to upload

To send the files to an FTP site:

  1. Select the Use FTP to Send Exported Files option button from the toolbar at the top and click Next.

  2. If you are not sending the files to an FTP site, clear the Use FTP to Send Exported Files option button and click Next to complete the procedure.

    image16

  3. Select the files to upload and click Next.

    image17

Step 8 - FTP: Select and configure the FTP site

Once you have indicated which files to upload, the FTP Site window will appear.

  1. Enter the FTP site connection information.

    image18

    Host

    Enter the FTP Site’s Host address. This is usually in the format 'ftp://hostsitename.com' or 'ftp://123.45.6.7'.

    Port

    Enter the FTP Site’s port number if required.

    Encryption

    Select the FTP Site’s encryption mention from the drop-down list.

    User ID

    Enter the User ID you use to access this FTP Site.

    Password

    Enter the Password associated with the User ID that you use to access this FTP Site.

    Remote Directory

    Enter the Directory of the Host site in indicate where you would like to store your files. This location will be supplied to you by the FTP administrator.

    Rename Files to *.SENT

    Selecting this checkbox will rename all of your text files (currently with the file extension '.txt') to files bearing the extension '.sent', which tells your FTP site administrator that these files were uploaded through the Export Wizard. If you would like your files to remain as they are on your local system ('Dispatch14.txt' for example), clear this checkbox option.

  2. Use the Test FTP Connection button to verify the connection is working.

  3. Click Next to transmit the files to the FTP site.

Step 9 - FTP: Review the FTP log

Once the files have been sent to the FTP site, the FTP Log window will appear and display a summary of which files were sent up if all went successfully or an error message if there were problems.

  1. Review the FTP Log to verify that the files were successfully submitted.

  2. If there were any problems, use the information displayed in the log file to determine the fix, click the Back button, remedy the problem and try again.

    image19

  3. The Save, Print, and Clear buttons are all associated with the FTP log text file.

    Save

    Saves the details of the FTP log displayed in this window as a text file in the file directory. A standard Save As window will be displayed prompting you for a filename and location.

    Print

    Prints the FTP log text to the default printer setup for this workstation.

    Clear

    Clears the FTP log text.

  4. Click Finished to see the final "Export completed" message.

    image20

  5. Click Finished to end the procedure.

Exporting Great Plains Accounting (GP) data - Notes

Microsoft Dynamics GP (formerly known as Great Plains, further referred to here as GP) is Microsoft’s general-purpose enterprise-wide accounting software system. It is one of the largest, most flexible, and popular accounting packages on the market.

TruckMate has its own fully integrated Accounting Payable and Accounts Receivable modules, but, with some clients running some divisions with TMWSuite and others with TruckMate, and wanting combined financial information (in GP), a partial (one-way) integration to GP has been added to TruckMate.

The interface between GP and TruckMate is a one-way process: export from TruckMate and import into GP. For the GP side, clients will be provided with template .IMD files (import definitions) for the files exported by TruckMate. The default template file is called 'TruckMateIM.imd', and will be placed into the default TruckMate installation folder (C:\Program Files (x86)\TruckMate 20xx\) following installation. The files exported by TruckMate will be imported through GP’s Integration Manager. For the TruckMate side, it involves using the Export Wizard to export two types of data: reference lists and registers (transactional data).

Requirements

  • TruckMate License Key # 29004

Running the GP Export as a service

The Great Plains Accounting export type can be can be added and configured as a Windows service called Export within the TruckMate Service Host program. Go to Utilities > TruckMate Service Host to see more details regarding configuring this service.

image21

When using the Export Windows service, it is useful to know that the Filter Type information is displayed on the Export Setup window in the Group Sub-Type grid and the Advanced Settings - Details tab to identify the type of filtering designed for this export type. (Feature added in TM 19.4.)

This will tell you the type of data you need from the Filter Column field on the Details tab. For example, if the filter type is AuditNum, you will need the column from the source table that identifies the batch # it was posted to G/L with. Other Filter Types include None and DateRange.

Go to Utilities > TruckMate Service Host > and select Properties from the shortcut menu for the Export service to display the Export – Setup / Properties configuration window. Click Data Setup to display the Export Setup window used to define export groups and their sub-types and the options related to the data to be exported.

image40

Re-export (Great Plains Accounting only)

When exporting the Microsoft’s Great Plains Accounting system, you have the three options to choose from on the Re-Export window:

image22

New Export:

Will not include previously exported records in the current export. Drop-down boxes for 'Audit Number' or 'Pay Report Run' will only show those never exported.

Export All

Export all records (including previously exported records). All batch numbers will be available in the drop-downs.

Re-Export

Re-export only the records included in a previous export batch. Drop-down boxes for 'Audit Number' or 'Pay Report Run' will only show those previously exported.

Sub-types include:

image23

Reference

  • File types: clients, vendors, employees (CAN and US), punits, trailers, containers, equip, and GL account codes.

  • Clients, vendors, employees, and GL account codes are used to support register data.

  • Punits, trailers, containers, and equip are intended to allow quick fixed asset setup in GP.

  • Files will contain very minimal info (code, name or description, address, etc.).

  • GP import will be smart enough to do either an update or an insert.

  • However, you do not have to export the entire table every time - exportwiz will prompt you with a date range to filter reference tables by MODIFIED_DATE (set start date to something like 1/1/1900 to do a complete table export or use a recent date to only export new / updated records)

  • This is optional; you may instead choose to add new reference codes into GP manually.

  • You can export any combination of types (0 to 9) in a single step / export (along with 0 to 5 registers)

Registers

Select the Audit number from the various Register programs throughout TruckMate that you wish to export to the Great Plains accounting system. The Export Wizard currently supports exporting the data from the TruckMate Billing Register program and AP Invoice Register program into the Microsoft GP accounting system.

image24

  • You will be prompted for audit number (BR or IP) or report run (DP).

  • File types: Billing (BR), Interliner Payable (IP), Accounts Payable (AP) and Driver Pay (DP), settlement statements (vendor type, Canadian payroll type, and U.S. payroll type).

  • You can export any combination of types (0 to 6) in a single step / export (along with 0 to 9 reference exports).

  • You can only select one "batch" number (audit or report run) per type (per export):

  • For example, if you want to export 2 billing register batches (audit numbers), you must run the export twice.

A log table keeps track of who/when exports were run and which types and batch numbers were selected:

Billing Register Export

For the Billing Register export you will need to:

  1. Complete and approve Freight Bills in the Customer Service program.

  2. Run the Billing Register program (billbr.exe).

  3. Run the Export Wizard program for the given Billing Register audit number.

IP Accrual Register Export

For the IP Accrual Register export, you will need to:

  1. Complete and approve Freight Bills in the Customer Service program.

  2. Run the IP Accrual Register program (ipaccrualreg.exe).

  3. Run the Export Wizard program for the given IP Accrual audit number.

Billing Register Plus IP Accrual Export

This type of export combines the Billing Register export and the IP Accrual Register export (both described above) in one export type. Instead of exporting the Billing Register and the IP Accrual Register in two separate exports (which might be needed sometimes) you can combine the two in one export, which will create combined GL entries in GP (less entries) instead of multiple GL entries as a result of separate exports.

Before you use this option, make sure to satisfy the following conditions:

  • Keep the application configuration option Bill > CSERV.EXE - 'Auto Approve IP on FB Approval' set to 'True'. This will guarantee that the attached IP records get approved when the FB is approved.

  • Make sure users do not 'unapprove' and edit an IP record attached to a FB that has been approved and exported to GP (no changes are allowed on an approved IP, but a user may unapprove and then change an IP).

  • Make sure users do not add any IP record to a FB that has been approved and exported to GP.

For Billing Register Plus IP Accrual export you will need to:

  1. Complete and approve Freight Bills in the Customer Service program.

  2. Run the Billing Register program (billbr.exe).

  3. Run the IP Accrual Register program (ipaccrualreg.exe).

  4. Run the Export Wizard program for the given Billing Register audit number.

Note: If you run both the 'Billing Register Plus IP Accrual' export and the 'IP Accrual Register' export on the same FB’s, they will get double posted in GP. So, a company should decide to go with either the 'Billing Register Plus IP Accrual' export or with the two exports 'Billing Register' and 'IP Accrual Register' - and not switch back and forth. You can hide the one(s) you don’t want to use in exportwiz.exe (so users don’t accidentally run the wrong one(s)). This is done via SQL (set column EXPORT_TYPE.ACTIVE to False)

Interline Payables Register Export

For Interline Payables Audit Register, you will need to:

  1. Run the IP Accrual Register on Freight Bills with Interliners attached.

  2. Audit the IP records (using the Interline Payables Audit program (ipiipm.exe).

  3. Run the Interline Payables Audit Register (ipiipr.exe).

  4. Run the Export Wizard program for the given IP audit number.

Driver Payables Register Export

For the Driver Payables Register, you will need to:

  1. Create Contracts and Rules for Trip/Freight Bill based pay (and dispatch Freight Bills) in the Driver Pay Contracts program, and/or use the Fuel Card Data Import program to create Fuel Deductions, and/or create manual pay/deduction entries in the Driver Pay Audit program.

  2. Run the Driver Pay Register program (payreg.exe) and Driver Deduction Register program (dedreg.exe) (possibly many times).

  3. Run the Driver Statements program (paystmt.exe).

  4. Run the Export Wizard program for the given statement run (by report run number).

Driver Pay Notes:

  • Amounts will be summarized (totaled) by either GL account (for vendor type driver pay) or pay/ded code (for payroll type driver pay): Pay/ded descriptions will NOT come over into GP.

  • When you write a check in GP, you will have to include the settlement statement printed from TruckMate to show the detailed breakdown.

  • For payroll type driver pay, payroll deductions (income taxes etc as opposed to "regular" driver deductions) will be calculated in GP.

Files

  • A separate file is generated for each different type of export (up 14 files total).

  • For any given type, the same name is used every time (e.g. BR.csv).

  • Therefore, no sequential number or timestamp is used in the name.

  • Meaning you need to import into GP before exporting the next audit number from TruckMate.

  • This is to make importing into GP easier (so you don’t have to select/change filename each time).

  • All files are in comma-delimited format (so they can be viewed by either a text editor or Excel).

  • All rows within a given file have identical format:

  • Thus, header / totals info may be repeated on multiple lines

  • For example: BR.csv contains a row for every debit and credit in BILLGL - for every row, the bill-to client, bill number, invoice total, etc will all be the same.

Notes/Limitations

  • It is assumed that clients using GP will only be using TruckMate for operational purposes - i.e. cserv, dispatch, etc. Also, if a client is large enough to use GP, it is assumed that if they need R&M software, they will be using TruckMateT. So, that means that there is no export for: R&M, general payroll (as opposed to payroll type driverpay), GJ, general AP (as opposed to IP or vendor-based driverpay), AR adjustments, cash receipts, checks, fixed assets, etc. It is expected that clients will enter data for these modules directly into GP (or TruckMateT), by-passing TruckMate.

Although COD and Supplier registers are part of operations, these are little-used by TruckMate clients and (thus far) have not been implemented.

  • Since the data-flow is one-way, TruckMate will not have access to current AR balances; hence, checking to see if a client is near or over their credit limit must be done in GP, not TruckMate.

  • Exports have NOT been written for one-time setup / conversion from TruckMate Accounting to GP (AR and AP balance forwards, GL balance history, etc).

  • Driverpay settlement statements for drivers of type D=driver/direct-pay (as opposed to V=vendor or P=payroll) are NOT exported.

  • Driverpay and deduction codes are limited to 6 characters in GP and no import facility is available.

  • So if a longer code is created in TruckMate, it will get truncated to 6 on export (in payroll type DP export).

  • Auto-generated pay rows in TruckMate are not assigned a pay code; this is required in GP, so hard-coded values are used on export of payroll type DP:

    • AMIFSC = Paid by Mileage Fuel Surcharge

    • AMIGAL = Paid by Miles Per Gallon

    • AMI = Paid by Mileage

    • APKDR = Paid by Pickups/Drops

    • APRCNT = Paid by Percentage of Gross

    • APKACC = Pickup Acc Charge

    • ADRACC = Delivery Acc Charge

    • AFLMI = Flat pay based on mileage

    • AJOBQ = Paid by Job Quote

    • AMIN = Paid by Minimum

    • ASC = Paid by Surcharge

    • AUNITS = Paid by Units

    • AFLTR = Paid by Flat Trip

    • AMINTR = Paid by Min Trip

    • AIM = Paid by InGate/OutGate

    • AFLPT = Paid by Point to Point Flat Rate

    • AVOID = Voided check reversal

  • In TruckMate, you may create a pay or deduction record without filling in the code. Blank/missing codes are set to hard-coded catch-all codes on export of payroll type DP:

    • ATAXP = taxable driver pay

    • ANTAXP = non-taxable driver pay

    • ATAXD = taxable deductions

    • ANTAXD = non-taxable deductions

  • The above codes (at least, the automatic pay-types you use plus the 4 catch-alls), as well as any pay / deduction codes created manually in TruckMate, must be setup (created manually) in GP. The easiest way to avoid name collisions is to never create a manual code starting with letter A.

  • When creating manual pay and deduction codes, you should avoid using any of the above codes (unless you want your manual pay amounts to be lumped in with auto-pay amounts for payroll type export).

  • In the GL accounts export, the "type" is resolved to 1 of 48 standard types in GP (these are almost the same as the 50-odd types available in TruckMate).

  • In employee export, SIN’s / SSN’s will get truncated to 9 digits (these shouldn’t be longer than 9 anyway).

  • When DP is exported, status codes are NOT modified. So in payaudit.exe, you cannot distinguish which items have been "settled"/exported (since you won’t be creating the checks in TruckMate, the items will never go to "paid" status).

  • We currently only support export of "regular" driverpay (as opposed to the little-used TruckMate feature whereby DP can be recorded in the BILLGL table).

IDSC - Requirements and Notes

Requirements

  • TruckMate License Key # 29005

Notes

No sub-types need to be selected when exporting IDSC (Integrated Decision Support Corporation) fuel tax data.

Use the Export Wizard to manually export data from FT_FUEL Table (which has either been imported via the Card Data Import program or manually added via Fuel Tax Trip program) to the IDSC Expert Fuel module. The term manual in this case means that you will be prompted for Date Range values to perform the export. It is up to you to make sure that:

  • No records are exported twice, and

  • No records are missed (not exported)

Alternatively, you can do the export automatically using the TMW Service Host program. The service will auto-export records, stamping them so they are not re-exported.

image25

Note: The IDSC Export Type will create a file called Fuelact.dat (in directory you specify) that lists fuel purchases (rows from the FT_FUEL Table) for the date range you specify for later import into the IDSC Expert Fuel program.

Inter-Tax - Requirements and Notes

Requirements

  • TruckMate License Key # 29001

Notes

Sub-types include:

image26

MacroPoint

Requirements

  • TruckMate License Key # 29009

Notes

No sub-types or date range vales need to be selected when exporting MacroPoint data.

Optimum Solutions

Requirements

  • TruckMate License Key # 29007

Notes

The export type does not include sub-types or require a date range, only a Payroll batch number needs to be selected from a drop down list.

image27

Exporting PC*Miler data

Requirements

  • TruckMate License Key # 29003

  • Sub-types include:

image28

When using the Export Wizard to export to PC*Miler Fuel Tax, not all import types are required, and, although you may export the files from TruckMate all at once or in any order, the order of import into PC*Miler Fuel Tax is important. You may also decide to not import certain fields into PC*Miler Fuel Tax (most are optional).

To exclude a field, simply do not assign 'column' and 'length' to the field in the Import Layout Setup (in Tools > Import > Advanced Settings in PC*Miler Fuel Tax). For example, you may decide to not import TruckMate fuel tax trip numbers in Fuel Purchases (*.fp) so that PC*Miler Fuel Tax is forced to determine the trip number by matching to a (previously imported) trip by Date / Unit.

Power Units (PUNIT - *.unt) should be imported FIRST (otherwise you may get an error in PC*Miler Fuel Tax when importing fuel purchases or trips for a non-existent unit). If your active Power Unit list is fairly static, then it only needs to be exported/imported periodically (after changes in TruckMate). Of course, if your Power Unit list is very static, then you may decide to maintain it manually in PC*Miler Fuel Tax and never need to export/import.

Fuel Purchases (FT_FUEL - *.fp) should always be exported and should always be imported LAST (especially if you decide to not import the trip number).

For Trips, you have several choices- you should only chose one option:

If you are using the Fuel Tax program in TruckMate (regularly creating and auditing fuel tax trips from dispatch trips), then it is recommended that you export 'Origin-Dest (FT_TRIP)' and 'State Mileage (FT_TRIP_DETAIL)'. In this case, you should import 'FT_TRIP' before 'FT_TRIP_DETAIL' and you should include 'TripID' in setting up *.fp.

Otherwise, if you were using the 6.1 or 7.0 Fuel Tax Export program (FTEXPORT.EXE), then chose one of:

  • 'Origin-Dest (LEGSUM): Original' or

  • 'Trip (LOCHIST)' or

  • 'Origin-Dest (DRIVERPAY)'. Or, try 'Origin-Dest (LEGSUM)'- it is similar to the original, but it has more fields (see table below).

All four of these choices create a PC*Miler Fuel Tax trip for every TruckMate dispatch leg; the trip number will be of the form 'X.Y' or 'XSY' where 'X' is the TruckMate dispatch trip number and 'Y' is the TruckMate leg number. They all will take longer to import into PC*Miler Fuel Tax (both for trips, because it needs to generate the mileage breakdown by jurisdiction as it imports, and for fuel purchases, because it needs to determine the trip number as it imports). For any of these four options, DO NOT include 'TripID' in setting up *.fp (because a TruckMate fuel tax trip number will not match up to a TruckMate dispatch trip number / leg sequence).

The complete list of available fields in the setup of the import file layouts in PC*Miler Fuel Tax (Tools > Import > Advanced Settings) is as follows:

TRUCKMATE DATA PC*MILER FUEL TAX FIELD NAME STARTING COLUMN LENGTH

*Unit (PUNIT)- .unt

ID

1

10

LicenseNumber

12

14

VehicleID

27

18

ModelYear

46

4

AxleCount

51

4

FuelType

56

2

CGWeight

59

10

GWeight

70

10

UWeight

81

10

FleetID

92

10

FleetDate

103

8

_

_

_

Fuel Purchase (FT_FUEL)- *.fp

UnitID

1

10

Vendor

12

10

City/Zip

23

30

Jurisdiction

54

2

Date

57

9

Time

67

5

Volume

73

8

Cost

82

10

Invoice#

93

16

VolumeUnits

110

1

TripID

112

10

Taxable

123

5

TaxPaid

129

5

Bulk

135

10

_

_

_

Orig-Dest (FT_TRIP)- *.ods

Origin

1

40

Destination

42

40

UnitID

83

10

TripID

94

10

Start Date

105

8

End Date

114

8

Start Time

123

5

End Time

129

5

_

_

_

State Mileage (FT_TRIP_DETAIL)- *.mil

TripID

1

10

UnitID

11

10

Permit#

22

10

PermitCost

33

10

Jurisdiction

44

10

Date

55

8

Time

64

5

Highways

70

254

Loaded

325

1

Toll

327

10

NonToll

338

10

Taxable

349

1

_

_

_

Orig-Dest (LEGSUM): Original- *.ods ,

Trip (LOCHIST)- *.trp,

Orig-Dest (DRIVERPAY)- *.ods

UnitID

3

10

TripID

13

12

End Date

25

15

Origin

40

30

Destination

70

30

_

_

_

Orig-Dest (LEGSUM)- *.ods

TripID

1

10

UnitlD

12

10

Origin

23

30

Destination

54

30

Start Date

85

8

End Date

94

8

Start Time

103

5

End Time

109

5

T-Chek

Requirements

  • TruckMate License Key # 29002

Notes

Sub-types include:

image29

Exporting SMC3 (TCG) LTL/Traffic Costing Info data

Transportation Costing Group (TCG) is now Cost Intelligence System, a product of SMC3.

SMC3 provides transportation-costing software to Less Than Truckload (LTL) and Truckload (TL) transportation companies.

TMW Systems has created an integration between TruckMate and two of the SMC3 products (LTL/CIS and Traffic/CIS). The integration exports specific TruckMate information into external files that are used by these SMC3 (formerly TCG) software applications.

LTL/CIS

The LTL Cost Information System (CIS) is a comprehensive model customized for individual motor carriers providing LTL/TL service via a terminal network. CIS combines carrier traffic and financial information with carrier and/or industry performance standards to develop the cost of individual shipments. The system processes freight bills or user-built shipment description files to provide accurate, specific cost information for pricing, traffic analysis, profitability analysis and operations/productivity analysis.

Traffic/CIS

An extension of either LTL/CIS or TL/CIS, this system routinely costs all traffic in each accounting period by interfacing with both cost and customer profiles. A cost-based data warehouse is produced as well as standard monthly reports for account and freight terminal profitability. Users can also custom design reports to be run on any period or combination of periods in the cost-based database. The cost-based database can be used for analysis of virtually any traffic breakdown to enable users to "zero-in" on both profitable and unprofitable traffic segments. What-If scenarios can be applied to re-cost traffic segments in order to enhance decision making and shipper negotiations capabilities. The system can also be interfaced with onboard computers and/or bar code readers to allocate costs based on the actual circumstances of each shipment’s movement.

Disclaimer: Please confirm specific features with your vendor. TMW does not make any representations about the capabilities of third-party products.

Requirements

TruckMate License Key # 29008 is required to run the SMC3 TCG LTL/Traffic Costing Info export. For information on obtaining TruckMate license keys, contact a Trimble TruckMate representative.

Note: This solution works with Export Wizard and is not TMW Windows Service integrated.

All export files for the SMC3 TCG LTL/Traffic Costing Info integration are created as CSV files. Previously, some of the files were created as DAT or TXT files. If you are using the latest TruckMate versions and patch versions (e.g. TM 2018.3+) containing this change where the export files will all be created as CSV files, you will need to contact SMC3 and let them know. SMC3 will need to set a switch via a flag imbedded in the SMC3 software for each file and send you a modified version of the SMC3 software to enable the TruckMate / SMC3 integration to use the CSV files.

SMC3 TCG LTL/Traffic Costing Info - Notes

Sub-types include:

image30

All export files for the SMC3 TCG LTL/Traffic Costing Info integration are created as CSV files. Previously, some of the files were created as DAT or TXT files. If you are using the latest TruckMate versions and patch versions (e.g. TM 2018.3+) containing this change where the SMC3 export files are all CSV files, you will need to contact SMC3 and let them know. SMC3 will need to set a switch via a flag imbedded in the SMC3 software for each file and send you a modified version of the SMC3 software to enable the TruckMate / SMC3 TCG integration to use the CSV files.

User must avoid saving an export file opened in Excel to CSV if any of the fields start with '0'. For example, if GL Account code is all numeric and starts with '0' saving from Excel will drop the leading '0'.

If TruckMate is set to allow postings to prior GL periods, the export will not send those postings if the prior period has already been exported to SMC3 unless prior period can be re-exported.

For TruckMate users who are already using the TruckMate Export / SMC3 TCG LTL/Traffic Costing Info integration and customized some of the export file creation routines, you will need to contact TruckMate support in order to upgrade to the latest TruckMate version. A custom "stub" will need to be created to allow the latest version of TruckMate to use your customized code.

SMC3 cost model setup checklist

In order to ensure the SMC3 LTL and Traffic cost information systems have enough information for analysis and reporting purposes, the data requirements are set up in two phases. First a cost model is setup and then the costed traffic information.

The following tables describe the typical checklist for the SMC3 setup and identifies those parts that are supplied by data files coming from the TruckMate Export Wizard. The items marked as MANUAL required you to provide the data manually. Consult the SMC3 implantation documentation for more information.

PHASE 1 - Creation of a working cost model (LTL/CIS)

Description Filename Notes

A

General ledger expense data

GL.CSV

B

Traffic statistics

P&D hours and Dock hours not supported in TruckMate

C

Points list

POINTS.CSV

D

Terminal codes and locations

MANUAL - Provide SMC3 a list of all terminals (freight terminals, interline carrier’s terminals, cartage agent) including the Zone Code used to identify each in TruckMate.

E

Terminal features

MANUAL - For each company terminal, user is to provide SMC3 with the number of doors and "Y" if forklift only.

F

Linehaul dispatch data

DISPATCH.CSV

G

Cartage rates

MANUAL - Provide SMC3 with rates, terms of payment, and means by which freight is provided for any terminal or geographic area not served by company drivers and/or using a local cartage agent to pickup and deliver freight.

H

Load Plan

LOADPLAN.CSV

I

Extra Costs

MANUAL - Provide SMC3 a list of any additional costs of various accessorial charges.

J

Purchased linehaul

MANUAL - Provide SMC3 a list of vendor charges for any linehaul legs served specifically or exclusively by purchased transportation (as opposed to casual use for overflow), if DISPATCH.DAT is not in use or is in use but is not identifying purchased linehaul or is not including the payment to the vendor.

PHASE 2 - Costing freight on an ongoing basis (Traffic/CIS)

Description Filename Notes

A

Monthly shipments

B

Actual P&D trips

ALL.CSV

C

Customer names

CUSTNAM.CSV

D

Grouping for national accounts

E

Sales reps customers

F

Interline profile data

MANUAL - Provide SMC3 a list of major partner carriers and indicate for each how the freight is usually interchanged.

G

Manifest file for actual linehaul costing

MANIFEST.CSV

H

Revenue corrections and adjustments

PAYBACKS.DAT is not applicable to TruckMate. TruckMate will include interfaced (i.e. posted to AR) credits and rebills in PROBILL.CSV, ARTICLES.CSV, and ACCREV.CSV.

I

Actual claims experience

CLAIMS.CSV

J

Density by commodity

MANUAL - Provide SMC3 a list of density data by commodity code as well as by customer if available.

K

Handling units detail

MANUAL - HANDLING.DAT is not applicable to TruckMate. TruckMate will include density in PROBILL.CSV.

Export sub-types and export file descriptions

When using the Export Wizard to generate export files for LTL/Traffic Costing Info purposes, there are several sub-types options. Each export sub-type is associated with a specific export file.

The file names are fixed as recommended by SMC3. If a file of the same name exists in the folder selected to store the export files, it will be overridden without warning.

Cost Information

Actual Linehaul Cost (MANIFEST.CSV)

This file serves as a link between the DISPATCH.DAT and PROBILL.DAT records for Freight Bills on an interlined linehaul leg. The file will store the Manifest Number and the Freight Bill Number fields to establish the link. Cancelled freight bills and freight bills that have not been posted (NA) will be excluded from the file. The information in the file will be for transactions that took place within the specified date range. The following fields are included:

  • Manifest Number

  • Freight Bill Number

Actual P&D Trips (ALL.CSV)

This file will include one record for each shipment picked up or delivered on each P&D trip. TruckMate will export all freight bills on pickup and delivery trips having any trip status change occurring within the selected date range. Records will be in stop# sequence within ascending trip number. The following fields are included:

  • Date

  • Terminal Code

  • Driver ID

  • Manifest Number

  • Leave Terminal Time

  • Arrive First Stop Time

  • Leave Last Stop Time

  • Return Terminal Time

  • Pickup or Delivery

  • Probill Number

  • Pieces

  • Weight

  • Arrive Stop Time

  • Depart Stop Time

Linehaul Dispatch / Actual Cost (DISPATCH.CSV)

This file contains costing information for leg-based interlined linehaul trips. This file will show dispatch information including source and destination terminals and the sum of distances of all freight bills on a leg that fall within the selected date range. The file includes settlement amount (Interline Payable’s Base + Extra Charges (without fuel)) accumulated for all applicable freight bills). The Settlement Code / Purchase Type will be fixed to O (Optional). The file will also include a calculation of all fuel surcharges (if purchased) accumulated for all applicable freight bills. Cancelled freight bills and freight bills that have not been posted (NA) will be excluded from the file.

The dispatch, manifest and all (local P&D) files should include all dispatches, manifests and local trips pulled for a time frame starting two weeks prior to the selected period (for probill file) and two weeks after (or up to the current date, if less). Including completed trips only is fine.

The following fields are included:

  • Dispatch Number

  • Dispatch Date & Time

  • Truck ID for Tolls

  • Origin Terminal

  • Destination Terminal

  • Miles

  • Settlement Code

  • Settlement Amount

  • Fuel Surcharge

  • Trailer # 1 Manifest Number

  • Total Weight

  • Number of Shipments

Load Plan (LOADPLAN.CSV)

The load plan tells the system the primary operating legs traversed when going to and from all of your freight terminals, by providing the intermediate, or via, terminals as well as an indication, if available, of whether shipments are re-handled at each. TruckMate will export as a comma-separated file named LOADPLAN.CSV and contain the load plan as defined in Service Level Maintenance > Load Plan tab.

Intermediate terminals must be freight terminals only, not "meet and turn" relay points.

General Ledger Expense (GL.CSV)

This file will include GL transactions on accounts types beginning with the letter P such as Sales (PR001-PR003), Cost (PG001-PG005) and Expense (PE001-PE017). This file will only include transactions with dates that fall within the specified date range.

The following fields are included:

  • G/L Account Number

  • Expense Amount

  • G/L Account Description

Revenue and expense accounts are identified in the standard TruckMate general ledger by account types beginning with 'P'.

Customer Information

Customer Index (CUSTNAM.CSV)

This file will include all active customers. No date filter is applied to this file. The fields included are:

  • Customer Name

  • ID Field (i.e. City/State)

  • Customer Code

Customer Index Grouping (GROUPDAT.CSV)

This file will contain all active customers that are assigned to groups. If a customer is not assigned to customer group, it will not be listed in this file. The fields included are:

  • Grouping Code

  • Customer Code

Customer Index Grouping (GROUPNAM.CSV)

This file will contain a list of distinct customer groups. Distinct group numbers assigned to active customers will be listed in this file. The fields included are:

  • Group Name

  • ID Field (i.e. City/State)

  • Group Code

Customer Sales Rep Index (SALESDAT.CSV)

This file will contain all active customers that have sales agent / rep assigned to them. The fields included are:

  • Sales Rep Code

  • Customer Code

Sales Rep Index (SALESNAM.CSV)

This file will contain a list of distinct sales agents / representatives that are assigned to active customers. The fields included are:

  • Name (Last, First)

  • Sales Rep Code

Revenue Details

Source Shipment Data File (PROBILL.CSV)

This file will contain detailed information about all freight bills with pickup dates that fall within the selected date range.

  • The PROBILL file and the related detail files (ACCREV and ARTICLES) should be all shipments for whatever date range and date stamp that the user feels best fits your accounting period. It should include just shipments that moved in the period, with revenue as accurate as it is known to be at the time the user makes the request. Can include corrections if you are not sending PAYBACKS.

  • If your accounting recognizes revenue at pickup, then include freight bill based on pickup date. If your accounting recognizes revenue at delivery, then include the freight bill based on delivery date. If your accounting uses an invoice or other date, discuss with SMC3 to determine which date is more appropriate.

  • A shipment would only be pulled into one period.

TruckMate will include the following fields:

  • Pickup Date

  • Delivery Date

  • Probill Number

  • Shipper

  • Consignee

  • Bill-to

  • Rate-To

  • Origin Terminal

  • Pickup Unit or Manifest Number

  • Destination Number

  • Delivery Unit or Manifest Number

  • Origin Zip Code / Postal Code

  • Destination Zip Code / Postal Code

  • Actual Weight

  • Billed Weight

  • Pieces

  • Pallets

  • Packaging Code

  • Density

  • Type of Rate

  • Net Revenue

  • Discount

  • Accessorial Revenue

  • Fuel Surcharge

  • Intermediate Terminal 1-6

  • Dock Code - Origin

  • Dock Code - Intermediates

  • Dock Code - Destination

  • Tariff & Number

  • Prepaid = 1 / Collect = 2

  • Originating Carrier

  • Terminating Carrier

  • Interline Carrier Share

  • Service Days

Supplemental Articles (ARTICLES.CSV)

Articles are represented as Items in freight bill’s detail lines in TruckMate. This is an optional file but including it improves cost accuracy as well as better subsequent freight re-rating for shipments made up on multiple-class items. The commodity code field can be further linked to density information.

The PROBILL.DAT record Packaging Code and Shipping Units take precedence over the article detail (such as all articles on one pallet). As such, the shipment record should contain zeros if the shipment has articles with mixed packaging (both loose pieces AND pallets).

This file will contain information about Items on all freight bills that have an actual pick up date that falls within the selected date range. TruckMate will include the following fields:

  • Probill Number

  • Actual Weight of the Article

  • Shipping Units

  • Packaging

  • Density

  • Actual Classification

  • Rated Classification

  • Commodity Code

  • Description

Supplemental Accessorial Revenue (ACCREV.CSV)

This file will contain information about all accessorial charges (except Fuel Surcharges) assigned to all freight bills with billing dates that fall within the selected date range. All fuel surcharge accessorial records (Accessorial Code = FSC in Codes Maintenance Program) are excluded from this file. TruckMate populates the following fields:

  • Probill Number

  • Accessorial Charge

  • Accessorial Code

Miscellaneous

Actual Loss & Damage by Customer (CLAIMS.CSV)

This file will contain information of claims that are both settled (approved) and closed / posted (processed into AR or AP module). Cancelled freight bills and freight bills that have not been posted (NA) will be excluded from the file. The information in the file will be grouped according to freight bill’s 'Rate To Code' and whether the transaction is an inbound or an outbound. The file will contain:

  • Customer Code

  • Cumulative Revenue

  • Cumulative Claims Paid

  • Application (S = Shipper, C=Consignee, Bill To)

Points List (POINTS.CSV)

This file will include address details (zip code / postal code) of zones that are defined as terminal zones. TruckMate will include the following fields:

  • Zip / Postal Code

  • Terminal Code

  • City Name

  • SCAC Code
    (Only included if a primary partner is defined as both the Advance and Beyond carrier for the Zone Code and the SCAC code is defined for the carrier.)

  • State / Province

SMC3 utilizes Zip/Postal Codes not user’s Zone Codes. Thus, user is required to have Zip/Postal Code assigned to each Zone Code to be exported even if the Zone Code = Zip/Postal Code. POINTS.CSV is exporting the Zip/Postal Code field not the Zone Code.
Contact SMC3 if some service points are conditional (e.g. ZIP Code 12345 is served by terminal X if coming from the east, terminal Y if coming from the west).

Procedure to create and send SMC3 (TCG) export files

To export SMC3 TCG LTL/Traffic Costing Info:

  1. Go to the Utilities > Export Wizard.

    The Welcome to the TruckMate Export Wizard window is displayed.

    image31

  2. Click Next.

  3. Use the Select image32 button to set the export type to TCG LTL/Traffic Costing Info.

    image33

    Note: You will have to clear the filter field if it is defaulting to some other export type in order to select TCG LTL/Traffic Costing Info from the complete list of available types.

    image34

  4. Click Next.

  5. Select the sub-types to export and click Next.

    Each of the sub-types and the export files they create are described in more detail and grouped as:

  6. Select the date range to indicate which records to export and click Next.

    image36

  7. Indicate the file directory where the export files will be created.

    image37

    The file names are fixed as recommended by SMC3. If a file of the same name exists in the folder selected to store the export files, it will be overridden without warning.
  8. Click Next to start creating the export files.

    It may take a few minutes to process the data. An "Exporting…​ please wait until done (when buttons are re-enabled)." message will be displayed and the bottom buttons will be disabled until the export process is complete.

    image12

    Note: If the process is taking too long and you wish to cancel, click the Abort button.

    After you abort, you will not be able to proceed with the FTP portions of the process. To continue the export, click the Back button and regenerate the Export files.

  9. Once the export files have all been created, the buttons will be enabled and the export log file will be updated.

    image38

    Note: The Save, Print, and Clear buttons are all associated with the export log text file. They are used to save and display the results of the export process.

    The View button allows you to select any of the export files in the export folder and view them if you have the right program for viewing the file types. For example, MS Excel or Notepad for viewing CSV files.

    Go to the file directory where the files were created if you want to see the resulting set of .CSV export files.

    image39

  10. If you want to send the files to an FTP site, select the Use FTP to Send Exported Files option button from the toolbar at the top and click Next.

    image16

  11. If you are not sending the files to an FTP site, clear the Use FTP to Send Exported Files option button and click Next to complete the procedure.

  12. Select the files to upload and click Next. Use the Check All and Uncheck All buttons to quickly select or deselect all the files.

    image17

  13. Enter the FTP site connection information.

  14. Use the Test FTP Connection button to verify the connection is working.

  15. Select the Rename Files to *.SENT checkbox if you want the extension on the transmitted files to change from .CSV to .SENT after the files are sent.

    Note: The Rename Files to *.SENT checkbox does not apply when exporting SMC3 TCG export type files. Selecting this checkbox will change the extension on any transmitted files with a TXT extension from .TXT to .SENT after the files are sent. It does not apply to CSV files.

    image18

  16. Click Next to transmit the files to the FTP site.

  17. Review the FTP Log to verify that the files were successfully submitted.

    If there were any problems, use the information displayed in the log file to determine the fix, click the Back button, remedy the problem and try again.

    image19

    The Save, Print, and Clear buttons are all associated with the FTP log text file.

  18. Click Finished to see the final "Export completed" message.

    image20

  19. Click Finished to end the procedure.