Import Item Receipts, Bills, and Vendor Credits
The Total Office Manager import system includes two great import options called “Import Bills/Credits” and “Import Item Receipts”. This system will import all the required information to create a fully completed bill, vendor credit, or item receipt. These transactions will be the same as if they were entered by hand. This import will affect your financials and balances.
Import Warning
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 (or sooner). 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 database file backups when they do not. You must set up SQL Server to create a backup file.
Data Validation
During the import process, imported data will be validated. The validation will try to 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. The validation system may not catch all errors. Please be sure to very carefully check your work.
Item Import
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 be updated to the same extent as an Item Import. That means certain fields will be updated an certain fields will be unchanged (such as Item Type).
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.
Import Details Saved
Your database will contain details of the import process. This will be important for troubleshooting purposes.
Report
Once the import has completed, a report will be generated. Be sure to print or save this report as a PDF for future reference. That report is not saved and must be closed before you may close the import wizard form.
Default Accounts
If the file is missing item chart of accounts, markup, or tax code, TOM’s default accounts will be used.
Step-By-Step
- From the main menu, click File | Company Utilities | Import
- You will be required to select an import file. This can be most delimited file types as well as Excel. We recommend that you use our Excel template. It contains all of the import fields that we allow and the correct headers.
- You will see a list of possible imports. Select either Bill/Credit or Item Receipt and click next (you can also double-click)
- If you are using our import template, most or all of the work has been done for you on this screen. You may need to review the fields contained in your import and match them with the available import fields. Use the Sample Data to see what information is in your import file and match that information with the correct field in TOM. Click Next.
- Select an Item Type to use if that information is not available. This will save you from receiving an error message. Do the same thing for the Vendor. We generally recommend that you check the Clip Strings to Length. This will remove extra spaces from the end of all strings. This work is optional. Click Next.
- Please click the “I understand . . .” checkbox and Next. We display this warning simply to remind users that importing information can cause issues. We just want you to be careful.
- Click the Import button. You will see one last “Are You Sure?” reminder.
- If the import has p[problems, you will see an error message. This message typically includes information about the problem including row and column number. You can fix the file and click the Import button again (without going back through the wizard).
- A report will open. Please print and or save that report if needed. That report is not saved automatically and will not be available inside Total Office Manager.
- There is a Save Log button at the top right of the form. It allows you to save information displayed in the progress box. This is optional.
- Click Finish or repeat the process if needed.
Tips
- You can save your import settings. There is a Save button at the top of the import wizard form.
- You can also open your saved import settings using the Load button at the top import wizard form.
Import File Field Information
| Field Name | Data Format | Notes |
| Header | This is the main area (at the top) of the Bill/Credit form. | |
| Vendor* | Text | Vendor Name (must be an exact match already in Total Office Manager). The import utility allows you to select one Vendor to use in case this field is blank. |
| Bill/Credit | “Bill” or “Credit” | Used to distinguish between a bill or vendor credit. Do not use negative numbers for a credit. If blank, we assume it is a bill. |
| 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/Credit form. | |
| Item Type* | Use Exact Name | You may use the following: Serialized, Inventory, Non-Inventory Part, Other Charge, or Service. This is required if the item will be added. The import utility allows you to select one Item Type to use in case this field is blank. Your entry must exactly match the options we have shown you. Capitalization should also be exact. |
| Purchased for Job Checkbox | “Yes” or “No” | If the item is purchased for a specific customer. This is an option for Non-Inventory Part, Service, and Other Charge item types. If Yes, you must have a Cost of Goods Sold account for this item. |
| 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 number is added to the Item Alias form for that vendor. This is typically 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’s Abbreviation field. Up to ten numbers and or letters. No spaces. Indicates how to measure (multiply out) the item qty. This must be exact. |
| Item Estimated Cost | #.## | The price you are likely to pay. This can be changed within the item. |
| 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 | Numeric
(auto assigned integer) |
This is the “ID” field from the Customer:Job list. This must exactly match an existing record in TOM. You are not using the Customer name. Note: You may need to add the “ID” field to the Customer:Job list to see this number. This is the internal ID number assigned to the customer by the software. From the Custome:Job list, click the Customer:Job menu > Form Preferences > Show Column. Check the “ID” box. |
| 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. | |
| Item Work Order Number | You must use a valid work order number for an existing work order for the customer selected. If the WO number has been duplicated, a number will be selected randomly. | |
| Expenses Tab | This is the “Expenses” tab on the Bills/Credit form. This is not part of the Item Receipt 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. | |
| Expense Work Order Number | You must use a valid work order number for an existing work order for the customer selected. If the WO number has been duplicated, a number will be selected randomly. | |
| 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 in MS Excel, MS Access, or delimited text format. 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. If the headers are not identical, you will be given the opportunity to map these headings during the import.
- The fields in your import file may be in any order.
- We have built an MS Excel template that you may use for importing. It contains all of the headers for each available field. This file is called “Timesheet Import Template.xls”. 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).
