Rental Conversion
This page lists information on converting from another rental system to the TrackAbout Rental Module.
Spreadsheet Template for all Rental Conversion Files
This section is all you really need. If you need more information, please consult the Data Loading section below.
Below is a link to the Rental Conversion Template that will help you format the data that will need to be imported into Trackabout.
The tabs in this template are numbered according to the order in which the data needs to be entered and loaded by TrackAbout.
Google Doc spreadsheet: Rental Data Collection Template
Use this spreadsheet in conjunction with the Rental Conversion Tasks google spreadsheet provided by your TrackAbout Support representative.
The information in the Data Loading section of this page (below) groups the information from the Rental Conversion Tasks google spreadsheet by who will be entering / loading the information. (The google spreadsheet is more practical in that is lists the tasks in the order they need to be completed.)
The information in the Data File Formats section of this page (below) gives detailed, technical information about the data which needs to be entered into the Rental Data Collection Template. However, the file should be self-explanatory.
Data Loading
Initial Data Load in Preparation for Rental Comparison
This data is entered manually by you:
- Locations (edit in website as needed)
- Rental Tax Categories (i.e., Cylinder Rent, Equipment Rent, Cylinder Agreement, Hazmat Fees, etc.)
- Rental Class Groups - Gives an extra layer of classification. It is OK for there to be only one Rental Class Group for each Rental Billing Period Type.
- Rental Classes (Rental Tax Categories, Rental Class Groups, Rental Billing Period Types, and Asset Classifications must be entered first. The fact that rental rates can be assigned to all Asset Types in a Rental Class should be considered when creating Rental Classes.)
- Edit Asset Types (Asset Classifications and Rental Classes must be entered first.)
- Enter "Description on Rental Bill"
- Mark Asset Types as Exchange if needed
- Standard Rates (Rental Classes and Rental Methods need to be established before entering Standard Rates.)
- Rate Brackets (Rental Classes and Rental Methods need to be established before entering Rate Brackets.)
- Bracketed Rates (Rate Brackets need to be established before entering Bracketed Rates.)
- Flat Fees (Rental Tax Categories and Customers must be entered before Flat Fees.)
Needed for Database Entry
This is data which TrackAbout needs from you to be able to enter information either directly into the database or via Support bulk-load tools:
- Rental Payment Terms (See Excel template)
- Rental Tax Regions (See Excel template. Rental Tax Categories must be entered first.)
- Rental Methods - Legacy Codes are needed (See Excel template. Rental Tax Categories must be entered first.)
- Rental Billing Period Types - Cylinder Rental and Equipment Rental are the most common. (Each Rental Billing Period Type will have its own rental run.)
- Asset Agreement Types - Accounting Codes and options are needed (See Excel template. Rental Tax Categories must be entered first.)
- Asset Agreements (See Excel template. Asset Agreement Types and Rental Classes must be entered first.)
This data will need to be entered directly into the database by TrackAbout Support:
- Rental Payment Terms (entries into this table need accounting codes) - this can be Net15, Net30, etc., or Net15, Cash Sale, C.O.D., etc.
- Rental Methods - add Rental Tax Categories and Legacy Codes (via Console tool)
- Rental Tax Statuses - Confirm that the already-existing defaults are good. (See Excel template.)
- Rental Billing Period Types (i.e., Cylinder Rent, Equipment Rental, etc. Each Rental Billing Period Type will have its own rental run.)
- Asset Agreement Types - Leases, Customer Owned, etc.
This data is bulk loaded:
- Item Info (Asset Classifications) - by TrackAbout (edit in website as needed)
- Rental Tax Regions - by you (Tax Categories must be entered before Tax Regions if any Tax Category is tax exempt for any Tax Region.)
- Customers - format set up by TrackAbout; import by you (Locations, Payment Terms, Rental Tax Regions, Rental Tax Statuses and Rate Brackets need to be entered into the system before the Customer information can be uploaded.)
- Customer-Specific Rental Rates - by TrackAbout (See Excel template. Rental Classes, Customers and Rental Methods need to be entered before the import of Customer Rates)
- Asset Agreements - by TrackAbout (Asset Agreement Types, Asset Types, and Customers must be entered into the db before Asset Agreements can be loaded.)
- Beginning Balances (per customer) - by TrackAbout using either 'Right Size Customer Balances' for non-Exchange types OR 'Bulk Load Billing Period Exchange Balances' for Exchange types (See Excel template. Customers must be entered first)
This data might be needed during Rental Comparison:
- Balances - only needs to be loaded after initial load if there is a problem.
This data is sent by you during rental comparison and also after Go Live:
- Customers - in case of changes in Name, Address, Tax Region, etc. (Can be loaded by you from the website, as often as they want but least daily after Go Live. Can also set up a webservice, although an API will soon be in place. This integration is automatic after going live with TECSYS)
- Product Codes - entered manually or new ones can come over via API (If done via API, asset classification must first be established in TrackAbout with AssetTypeName = PCodeName)
- Asset Agreements (Eventually can be brought over in an API. Until that's done, we can bulk load at the end of every month.)
- Transactions (Ships and Returns) for the month - needed for pre-Go-Live comparisons AND for Verifications (if using - compares tracking data to invoiced data) AND if Rental is being run without Core Tracking. Can be imported by you from the website. Also we can set it up so that TrackAbout picks them up from an ftp site; however, soon there will be an API. If using TECSYS, then this data is only needed for the conversion. This integration is automatic after going live with TECSYS. (See Excel template.)
This data is sent by you for Rental Comparisons:
- Legacy Rental Invoices - can be loaded by TrackAbout or by you. (See Excel template.)
This data is exported from TrackAbout and imported into your existing system (This is automatic if using TECSYS.)
- Rental Invoice Accounts Receivables (AR) Info
- Rental Invoice General Ledger (GL) Info. (This is generally just be a few lines from the Rental Billing Period Dashboard's totals.You can just key this in manually each month into your existing system.)
At the end of each month the rental bills also need to be printed. This can be done in one of two ways:
- TrackAbout creates PDFs for the rental bills. You can download these pdfs and print them locally.
- TrackAbout creates an xml (or other electronic format) file for use by your printing software or your vendors printing software.
- This integration file is already created for integration with SourceHOV Solutions in the US
- This integration file is already created for integration with SourceHOV Solutions in the US
Data File Formats
Customer Rental Rates
This is a list of all special or contract rental rates for customers.
File Loading: This file is loaded by TrackAbout support.
Field | Example | Format | Notes |
---|---|---|---|
Customer Number | 123456 | Text, Max 30 char | This is the full customer number. It can contain numbers, letters and special characters |
Asset Type | AC100 | Text, Max 400 char | This can be whatever is used to organize rental. This is typically a size or part number. Like "HP" for High Pressure or "AR300" for the part number AR300. |
Rental Method | D | Text, Max 100 char | This can be configured to accept any native codes from the existing rental system, however these are the standard values:
|
Rate | 0.20 | Numeric. Supports 3 points after the decimal | If this rate is 20 cents a day then this field would be 0.20. It's fine, but not necessary to have the extra zeros in this rate like the one before the . or after the 2 in this case. It's also ok to have a dollar sign in here ($) but it is not needed. |
Review/Expiration Date | 01/01/2015 | Date in MM/DD/YYYY format | This is a date at which these rates will be set to be reviewed. Note that in TrackAbout the rates never really 'expire' but a date is stored after which someone should review the rates. If you do not wish to use this feature then set the date to 12/31/2050 |
Notes | Rates negotiated by Steve | Text, Max 2000 chars | Any special notes or comments on this rate. Leave blank if not used |
Customer Balances
This file contains the current asset balances/counts for customers. Typically this is the data for the start/end of a month.
File Loading: This file is loaded by TrackAbout support.
Field | Example | Format | Notes |
---|---|---|---|
Customer Number | 123456 | Text, Max 30 char | This is the full customer number. It can contain numbers, letters and special characters. |
Product Code | AC100 | Text, Max 52 char | This is commonly called the Item Number, Part Number, Stock Number or Gas Code in some systems. If this code is broken into two parts, both parts are needed here. |
Current Balance | 5 | Integer | Number of assets of this type with this customer. Negative balances are ok for Exchange type assets. |
Indicate in the name of the file the date that this file is accurate. This is typically the start or end of a month. So a good name for this file would be CylinderBalances_EndofFeb2010.txt
If a customer has multiple Product Codes, then repeat the Customer Number on each line. So a simple example might look like:
123456 AC100 5 123456 OX100 3 129999 AC100 2 131212 NI300 2 131212 OX100 1
Asset Agreements (Leases, Customer Owned, etc.)
This is for information on Leases or Cylinder Agreements with customers. These are more generically called Asset Agreements in TrackAbout. This is for any agreement where the customer doesn't pay rental on a given kind of asset for a given amount of time.
File Loading: This file is loaded by TrackAbout support.
Field | Example | Format | Notes |
---|---|---|---|
Asset Agreement Type | LEASE | Text | This is the AccountingCode from the AssetAgreementTypes DB table. If all you have are leases, then put the word "LEASE" in this column. If you have more types of agreements, contact TrackAbout Support to set this up. |
Customer Number | 123456 | Text, Max 30 char | This is the full customer number. It can contain numbers, letters and special characters |
Agreement Number | 123456-1 | Text, Max 50 char | A unique number to identify this agreement. If no number is availible, leave this field blank. |
Original Start Date | 06/01/2005 | Date, MM/DD/YYYY | If this agreement has been renewed and you still have the original start date, we'll take that here. If it is not know, then leave this field blank. |
Start Date | 01/01/2009 | Date, MM/DD/YYYY | Current start date of the agreement. |
End Date | 12/31/2009 | Date, MM/DD/YYYY | Current end date of the agreement. |
Renewal Term in Months | 12 | Integer | If this agreement is renewed, this is the amount of time it will renew for. So it should be 12 for a 1 year lease. |
Auto-Renew | 1 | One character. 1= Yes, autorenew. 0=No, do not autorenew. | Today this is just a flag that is shown to the user on a renewal report. When the agreement is nearing expiration a user will see this agreement compared to the customer's current balance. One of the fields is this "AutoRenew" field.
If you don't have this concept in your system, then just set all agreements to either 0 or 1 depending on whether or not you allow renewal without talking to the customer. |
Notes | Call John Doe with any questions OR Agreement loaded on xx/xx/2015 | Text, Max 2000 char | This is for any notes stored with this agreement. |
Asset Type | AC100 | Text, Max 400 char | An indicator for what kind of asset(s) this line covers. This is typically a size or part number. Like "HP" for High Pressure or "AR300" for the part number AR300. |
Quantity | 2 | Integer | If this line covers 2 assets then the value here is a 2. If the agreement has many lines, this is just the quantity for this one line. |
Rate Per Asset | 50.0 | Number with 2 places to the right of decimal | How much it costs PER ASSET for this line. If this line covers 2 cylinders for a year at $50 each for a total of $100 then the value in this field will be 50 |
Customers
This file provides a list of customers, their addresses, tax info and bill delivery options. Note that this is NOT needed for conversions to Tecsys.
File Loading: You can import this file from this location:
- TEST: https://test.trackabout.com/clt/endOfMonth/importHoldersFile.aspx
- PRODUCTION: https://www.trackabout.com/clt/endOfMonth/importHoldersFile.aspx
Field | Example | Format | Notes |
---|---|---|---|
Customer Number | 123456 | Text, Max 30 char | This is the full customer number. It can contain numbers, letters and special characters |
Parent Customer Number | 123000 | Text, Max 30 char | If this line is for a department (aka ship-to, jobsite or child account), then fill in the number of the parent (aka sold-to, bill-to or main account) account in this field.
If this is NOT a department, then leave this field blank. |
Customer Name | ALPHA BETA INC. | Text, Max 150 char | The name of the customer. If this is a jobsite, this should be the name of the jobsite. |
Location Number | 3 | Text, Max 12 char | Number for your primary location (aka branch or store or warehouse) that services this customer.
This is also the location that will get credit for rental revenue. |
Shipping Address 1 (Shipping Name) | ALPHA BETA INC. | Text, Max 50 char | The first line of the shipping address block as printed on rental bills. It is usually the same as the name of the customer. |
Shipping Address 2 | 101 MAIN ST | Text, Max 50 char | Second line of the address block as printed on the rental bill. It is usually the first line of shipping street address. |
Shipping Address 3 | MAINT DEPT | Text, Max 50 char | Third line of the address block as printed on the rental bill. It is usually the second line of shipping street address. |
Shipping City | CHICAGO | Text, Max 50 char | City of shipping street address |
Shipping State | IL | Text, Max 50 char | State or Province of shipping street address |
Shipping Zip/Postal Code | 60601 | Text, Max 20 char | Zip or postal code of shipping street address. Include the full "+4" postal code if you have it, but it is not necessary |
Shipping Country | USA | Text, Max 50 char | Country of shipping street address. Leave blank if not necessary. |
Billing Address 1 (Billing Name) | ALPHA BETA INC. | Text, Max 50 char | The first line of the billing address block as printed on rental bills. It is usually the same as the name of the customer. |
Billing Address 2 | 101 MAIN ST | Text, Max 50 char | Second line of the address block as printed on the rental bill. It is usually the first line of billing street address. |
Billing Address 3 | MAINT DEPT | Text, Max 50 char | Third line of the address block as printed on the rental bill. It is usually the second line of shipping street address. |
Billing City | CHICAGO | Text, Max 50 char | City of billing street address |
Billing State | IL | Text, Max 50 char | State or Province of billing street address |
Billing Zip/Postal Code | 60601 | Text, Max 20 char | Zip or postal code of billing street address. Include the full "+4" postal code if you have it, but it is not necessary |
Billing Country | USA | Text, Max 50 char | Country of billing street address. Leave blank if not necessary. |
Tax Region | IL20 | Text, Max 25 char | Tax region or code for this customer. This is based on where the customer is located and dictates which taxes this customer is subject to. |
Is Tax Exempt | N | Y = Tax Exempt, N = Not Tax Exempt | Flag to indicate whether or not this customer is tax exempt. |
Rental Purchase Order Number | 2010G45 | Text, Max 100 char | This customers purchase order (PO) number that is to be used for rental bills. |
Territory or Salesman | North Chicago | Text, Max 50 chars | There is a slot on the rental bill format for "Territory". This is commonly the name of the branch that services this customer, or a region this customer is in, or the initials of the sales person responsible for this account. The idea is that a customer could reference this when calling with questions to get routed to the right person. |
Fax Number for Rental Bill Delivery | 555-555-1212 | Text, Max 200 char | A fax number where rental bills for this customer will be faxed. Check with TrackAbout Support to be sure you can use this feature. This typically requires integration with SourceCorp or whatever company you are using for bill printing and delivery. |
Emails for Rental Bill Delivery | test1@company.com, test2@company.com | Text, Max 400 char | Comma separated list of email addresses that should be sent copies of rental bills. |
Send via US Mail | Y | Y = Yes, Mail. N=No, Do not Mail. | Indicates whether or not to mail a paper copy of the bills for this customer to the Bill-To address. |
Rental Rate Bracket | 7 | Text, Max 100 char | If this customer is in a rate bracket (rather than just using customer specific rates and/or standard rates) then this is the code for that bracket. For instance brackets might be used if different geographic regions had different default rental rates or if larger customers are put into rate brackets rather than managed with special customer rates.
Leave blank if not used. |
If not specified, customers are assigned to the SYSTEM tax region during creation. Rental bills are put on a hold status automatically if the corresponding customer is still assigned to the SYSTEM region during bill generation.
Rental Invoice List
This file contains the dollar amounts from the rental invoices created in a rental run from your current rental system. There is one line per rental invoice.
This information is used to compare the rental numbers in TrackAbout with the rental numbers from your business system. This is only needed as part of the conversion and is not needed after the conversion.
- You can upload this informatoin without TrackAbout's help.
- This file is uploaded from this page:
- Click the "View/Edit" link
- Then "Import New Legacy Rental Run"
Field | Example | Format | Notes |
---|---|---|---|
Customer Number | 123456 | Text, Max 30 char | This is the full customer number. It can contain numbers, letters and special characters. |
Rental Invoice Number | R32323 | Text | This is the invoice number on the rental invoice |
Pre Tax Total | 100.00 | Number | Pre-Tax dollar amount of the invoice |
Tax | 6.50 | Number | Dollar amount of tax on the invoice |
Grand Total | 106.50 | Number | Grand total of invoic. This is the amount the customer pays. It should equal the Pre-Tax + Tax values. |
Notes | Text | This is typically left blank. It is a place to include any notes on this invoice if it would be helpful in explaining why it might not match the TrackAbout total. |
Tax Regions
This file contains information from your business system concerning Tax Regions.
- The file can be uploaded to your website, and there is an example file format to assist you in preparing the file.
Field | Example | Format | Notes |
---|---|---|---|
Tax Region Code | TaxRegionCode1 | Text, Max 25 char | This is the tax region from your business system. It commonly looks something like TXAB01 |
Tax Rate Between States | 3.50 | Decimal, Max 9 char before the decimal, and Max 5 char after decimal | |
Tax Rate Within State | 2.75 | Decimal, Max 9 char before the decimal, and Max 5 char after decimal | |
Accounting Code | Code1 | Text, Max 100 char | The code used to send the data back to Accounts Receivable. This field is NOT needed if you are using TECSYS. |
Tax Exempt Categories | TaxExemptCategory1,TaxExemptCategory2,TaxExemptCategory3 | A comma-separated list of tax exempt categories |
Deliveries and Returns
This file contains a list of all of the asset transactions (delivers and returns to customers) that took place in a period of time.
If a delivery has multiple items on it then it will have multiple lines in this file with some of the information repeated from line to line.
- You can upload this informatoin without TrackAbout's help.
- This file is uploaded from this page:
Field | Example | Format | Notes |
---|---|---|---|
Delivery Ticket number | 555555 | Text, Max 25 char | This is the number associated with this delivery. This is sometimes an order number, shipment number, picking number or invoice number. Use whichever number your people most commonly use when thinking about this delivery. |
Customer Number | 123456 | Text, Max 30 char | This is the full customer number. It can contain numbers, letters and special characters |
Customer Name | ALPHA BETA INC. | Text, Max 150 char | The name of the customer. If this is a jobsite, this should be the name of the jobsite. |
Location Number | 3 | Text, Max 12 char | Number for your location (aka branch or store or warehouse) that made this delivery/return. |
Customer Purchase Order (PO) Number | 2010GASA100 | Text, 100 char | This is the customer's PO number that you use to get paid by that customer.
Leave blank if not set or not needed in this integration. |
Ship Date | 01/01/2010 | Date in MM/DD/YYYY format | The date this delivery hit the rental system. For rental comparisons we want whatever date (order date, ship date, invoice date) that your current system uses for rental. |
Product Code | AC100 | Text, Max 52 char | This is commonly called the Item Number, Part Number, Stock Number or Gas Code in some systems. If this code is broken into two parts, both parts are needed here. |
Product Code Description | ACETYLENE SIZE 100 | Text, Max 1000 char | This is the description of the product code.
This field is optional. Leave it blank to not use this field. |
Delivered Quantity | 2 | Integer | Number of this product code delivered or shipped on this line.
0 is an ok value. Negative values are ok too. Contact TrackAbout do determine how these will be handled. |
Returned Quantity | 2 | Integer | Number of this product code returned on this line.
0 is an ok value. Negative values are ok too. Contact TrackAbout do determine how these will be handled. |
Item Info - Asset Type
This file will provide specific information about your assets.
Field | Example | Format | Notes |
---|---|---|---|
Category | Industrial | Text, Max 25 char | This is the general category of the item. For a gas distributor this list is typically: Industrial, Medical, Spec Gas, Rental Equipment, Bulk Tanks, Tube Trailers |
Group | Oxygen | Text, Max 30 char | This is normally the gas. For a gas distributor this list is typically: Oxygen, Argon, Nitrogen. There is normally a generic "Mix" group used for all mixes. |
Asset Type/Product Code | OX-20 | Text, Max 50 char | This is the item number used in TECSYS or whatever your ERP system is. |
Product Description | INDUSTRIAL OXYGEN, SIZE 20 | Text, Max 200 char | This is the text description of the product code.For a gas |
Description on Rental Bill | |||
Rental Class Group | |||
Rental Class | Industrial Cylinders | Text, Max 100 char | This is the way that rental is organized. For instance there is commonly a rental class call "Industrial" or "High Pressure" for most kinds of gas cylinders. There is also often another one called "Liquids".
So this might be HP (for High Pressure) or LQ (for Liquids) |
Replacement Price | 200.00 | Decimal, Max 18 char before the decimal, Max 2 char after the decimal | This is the amount a customer will be charged should the asset be lost or irreparably damaged. |
Tax Category | Cylinder Rent | Text, Max 50 char | We'll normally assume that all items are in the same tax category, but let us know if any are in a different tax category. For gas distributors typically most items are in a "Cylinder Rent" tax category. Sometimes some other items are in a "Bulk Tank Rent" or "Delivery Charge" tax category. |
Any Other Fields | We'll take any other fields that you might have like size, weight, vendor, etc. We will probably not use these fields, but they can be helpful to better understand the data. |