User:Rmccown

Note: Since I don't have permissions for creating new pages, I'm putting info here first.

TPI Exports and Their Quirks

Companies.xlsx

May come with two tabs of data: one that appears to have collapsed/multiple rows per customer, a second called "Flat Data"
There may be duplicates, depending on how the customer entered their data. Strangely, you may see multiple almost identical rows with important information spread between the multiple entries.
In the example referenced for this wiki entry, the company file included vendors and customers as indicated in the `Company Type` column with the following types: Customer, E-Commerce, Other, Service Provider, Supplier

Myinventory-xxx.xlsx

`PartID` appears to be the unique ID from TPI
Make, Model, Manufacturer, and additional information fields are spread out across 50+ columns for every part type which will make later steps a bit more difficult
Some cells in the original exports may have carriage returns which can make import into SQLyog behave unexpectedly
Despite having an `On Hand` column, this information seems to conflict with another file called something like 'inventory_deleted.xlsx' that will need to be resolved later

Inventory_deleted.xlsx

This file appears to show a list of inventory items that have been deleted, sold, or "adjusted"
A column labeled `Transaction` may be the most useful and has the following options for each entry: Add Inventory, Invoice, Stock Adjsutment, Credit Note, Purcahse Order, Item Receipt, Build Order, Sales Receipt

Trucks_xxx.xlsx

`TruckID` appears to be the unique ID from TPI
As with the inventory export, some of the cells have extra lines
The only reference between this and the inventory table is the vehicle's `Lot Number` and in most cases has a similar VIN. Unfortunately, there's no checks for duplicates or spelling so the data doesn't match up in all cases

AR-AgingReport-xxx.csv

This file had the most obvious information for company names and balances
Due to the number of duplicates in the 'companies' spreadsheet, the files don't pair up very well and may have to be entered manually.

Before importing the raw csv or xlsx sheet from TPI into SQLyog, it may be worth running the =CLEAN() function in Excel to remove line breaks
The allowable field length for TPI data doesn't match up well to Enterprise so longer fields like description or notes will either need the CLEAN function or most likely a LEFT() in SQLyog queries

TPI Category IDs will need to be mapped to the inventorytypeid used in Enterprise and by HTP.net
An example of such a file can be found in the ATS(AZ) case file called 'tpi_partmapping_EE'

Tip
The ATI conversion case file has an sql script that will convert TPI part types to ITrack part types