How to Import Bills and Item Receipts
Two new import options have been added to the current Total Office Manager import system called “Import Bills” and “Import Item Receipts”. This system will import all the required information to create a fully completed bill or item receipt. These transactions will be the same as if they were entered by hand.
It can be dangerous to import financial transactions. Be very careful with who you allow to do this work. Make sure that you are checking the imported data and that you are doing bank and credit card reconciliations monthly. Carefully check your import files to make sure the data looks correct. No matter what, you should be backing up your database each night. Be sure to check that this is actually happening. Many of our users think they have backups when they do not.
During the import process, imported data will be validated. The validation will make certain that required fields are present, record names are correct (ex: Do referenced departments or customers exist in the database?), and that the formatting is correct. If there are problems with the import file, a series of messages will be displayed. This information will include a description of the issue and a line number, allowing the user to easily fix their data. The user will be required to make sure the data file is completely correct. The import process will not fix incorrect data but it will work to reject it.
When an invoice item is imported, and it does not already exist, that item will be added on the fly. Once the information is imported, the “Register” will be rebuilt so that all related financial records will be updated. If an item already exists, that item will not be changed.
Audit Trail and Security Label
A record of the import will be added to the audit trail. This will include standard information. The Details field will indicate that the record was added due to an import. The Security Label, at bottom of the form, will include the user name and date that the transaction was imported.
Record the Source
Your database will contain details of the import process. This will be important for troubleshooting purposes.
Once the import has completed, a report will be generated. Be sure to print or save this report as a PDF for future reference.
If the file is missing item chart of accounts, markup, or tax code, TOM’s default accounts will be used.
Import File Field Information
|Field Name||Data Format||Notes|
|Header||This is the main area (at the top) of the bill.|
|Vendor*||Text||Vendor Name (must be an exact match to TOM)|
|Date*||MM/DD/YYYY||The date of the bill. This will be the date the financials are affected.|
|Due Date||MM/DD/YYYY||This is usually driven by the terms of the sale. Leave it blank to auto calculate against the terms you have set up. If there is no due date and no terms, the due date will be thirty days past the date of the bill. Note: Item Receipts do not have a due date.|
|Terms||Text||The name of the Terms (must be an exact match to TOM)|
|Reference #||Text||The Reference field from the Bills form.|
|Memo||Text||The Memo field from the Bills form.|
|To Be Printed||“Yes” or “No”||This will check the “To Be Printed” box.|
|Items Tab||This is the “Items” tab on the Bills form.|
|Item Type*||Use Exact Name||Serialized, Inventory, or Non-Inventory. Required if the item will be added|
|Purchased for Job Checkbox||“Yes” or “No”||If the item is purchased for a specific customer. This is an option for Non-Inventory types.|
|Item #*||Alphanumeric||Your company’s Item part number. Required if the Items tab is used. If the item does not exist, it will be added.|
|Item # Alias||Alphanumeric||This is the vendor’s part number. You part # might be different.|
|Warehouse||Text||Warehouse Name (must be an exact match to TOM)|
|Item Qty*||Numbers||How many of the items are you buying? Serialized items must always be 1.|
|Unit of Measure||Text||Unit of Measure. Indicates how to measure the item qty.|
|Item Estimated Cost||#.##||The price you are likely to pay.|
|Item Cost*||#.##||The price you are paying for the item (Direct Cost).|
|Item Retail Price||#.##||The price you are selling the item for.|
|Item Purchase Description||Text||The description of the item used for the buying process (vendor sees it).|
|Item Sales Description||Text||The description of the item used for the sales process (the customer sees it).|
|Item Dept Alias||Text||Must match TOM’s Department Alias field exactly.|
|Item Notes||Text||These are notes that can be attached to the item (found under Menu button).|
|Transaction Item Notes||Text||These are notes that can be added to each line item (Items tab).|
|Item Serial Number*||Alphanumeric||Required if a Serialized item is being added. One serial number per line item. No duplicates are allowed.|
|Item Income Account*||Alphanumeric||This is the COA number from TOM. Required if adding an item of any type. If none is found, the default will be used from Preferences | Items | Company Preferences.|
|Item COGS/Expense Account*||Alphanumeric||This is the COA number from TOM. Required if adding an item of any type. If none is found, the default will be used from Preferences | Items | Company Preferences.|
|Item Asset Account*||Alphanumeric||This is the COA number from TOM. Required if adding an item of any type. If none is found, the default will be used from Preferences | Items | Company Preferences.|
|Item Customer ID||The “ID” field from the Customer:job list. This must match an existing record in TOM.|
|Item Tax Code||The item’s tax code (if any). This must match an existing record in TOM.|
|Item Markup||The default markup to use. This must match an existing Price Level or Markup Table name in TOM. Tip: Don’t use names such as “50%”. It is better to use a name such as 50 Percent. The import process can have problems with the percent sign.|
|Expenses Tab||This is the “Expenses” tab on the Bills form.|
|Expense Account #*||COA Number. Required if the Expense tab is used.|
|Expense Amount*||The total amount of the expense for this line item.|
|Expense Memo||This is the line item memo field (Expenses tab) and describes the expense.|
|Expense Department Alias||The Department’s Alias (must be an exact match to TOM)|
|Expense Customer ID||The “ID” field from the Customer:job list. This must match an existing record in TOM.|
|Dollar or Percent||Text||Enter “Dollar” or “Percent”. If blank, it will assume you mean “Dollar”.|
|*A Required field. Some of these fields may only be required if other fields are used.|
Notes About the Import
- The import file may be MS Excel or delimited text. The import file should have headers. We recommend that your headers use the exact same field name as found in the table under the “Field Name” column. When they are the same, the import form will automatically map the file fields to the available fields in the software. The fields in your import file can be in any order.
- You have the option of importing this file as a Bill or an Item Receipt.
- We have built an Excel template that you can use for importing. It contains all of the headers for each available field. This file is called “Bills Import Template.xls”. It can be used for both Bills or Item Receipts. Bills Import Template Download
- If the Vendor Name and Transition Date fields are the same, each line item will be combined into one transaction.
- Payment due date, discount payment due date, and the discount amount will be filled in after the transaction is imported and saved. We just need to know the “Terms” of the sale to do this. Terms are set up in the software. The name of your Terms will need to exactly match what is in the import file.
- The vendor’s contact information will be derived from the vendor record that has already been saved in the database. This is also the case for customer information, job information, addresses, and any other contact info.
- If a job name is present, the Reimbursable field will be checked automatically.
- Total Office Manager allows you to duplicate the name of a Price Level and Markup Table. If a duplicate name exists, the import will use the name from the Price Level List. We recommend that you fix any duplicate Price Level and Markup names.
- When naming price levels and markups in Total Office Manager, don’t use names such as “50%”. It is better to use a name such as 50 Percent. The import process can have problems with the percent sign.
- The imported records will be regular, normal, fully editable transactions (as if they were entered manually).