Karl Irvin creates several great QuickBooks utility programs. His Data Transfer Utility is generally accepted to be the best tool for transferring data from one QuickBooks company to another. This week I want to talk about a new special purpose tool, Invoice To Bill.
Here’s the situation. You have one company that is using QuickBooks, and they are selling things to you. This is an invoice in their copy of QuickBooks, but it needs to be entered as a bill in YOUR copy of QuickBooks. If you are working with someone that is sending you invoices repeatedly, it would be handy to be able to not have to rekey this information.
You may be able to handle this with a set of transaction import/export tools, but that requires some translation. Export the bill to a file, transform it to an invoice, import it in the other company. That is tedious and a bit complicated to do.
That is where Invoice to Bill comes into play. You can work with your vendor to cooperate on this – they can provide you with an export file that has the invoices, and you can map these invoices into bills in your own company file.
Let’s take a quick look at how this works – note that this is not a tutorial so I’m not going into great detail.
Two Software Products
Invoice To Bill is actually two programs. The “Seller” version ($49.00 for a license at this time) is used by the company that is using QuickBooks to create Invoices. The “Buyer” version ($129.00 for a license at this time) is used by the company that is using QuickBooks to import invoices (from the “Seller”) into Bills.
Why do you have to do all of this? A couple of reasons.
- QuickBooks has no built in feature to transfer transactions from one QuickBooks company file to another. There is an import feature (not that reliable, it uses the IIF file format) but no export feature.
- Invoices are structured differently than Bills. There are similarities, but if you are using some other import/export tool, or trying to write a program to handle this with one of the programming interfaces, invoices aren’t the same as a bill. There needs to be some translation of one transaction to the other.
- The “Seller” will most likely have different names for things than the “Buyer”. If I’m buying a part from you, it may be called a “widget” in your company file’s item list, but I might call it a “cog” in my own company file. You may consider the item to be an inventory part in your company file, but I might treat it as a non-inventory part or even an expense in my company file.
That is the strength of the Invoice To Bill program – it will handle the export and import steps, the translation from invoice to bill, and the mapping of the items in the invoice to either items or expenses in the bill.
Creating the Invoice (the “Seller”)
The “Seller” will create an invoice in QuickBooks. Here is a simple example:
The Seller will run the Seller utility to send invoices to customers. You start at the top and click each of the buttons in turn, going down the page. Note that SOME of these steps are setup steps that you won’t have to repeat each time.
Step 1 opens the connection to the Seller’s local QuickBooks file.
Step 2 is usually skipped if you are just working with one QuickBooks file.
Step 3 is something you don’t have to do each time – it is going to copy your QuickBooks customer list into this program for efficient processing. You only have to do that the first time, or if you add customers later.
Step 4 lets you specify a location for the export file to be created, for each customer you are working with. Each customer gets a separate export file. In my example below I’m just mapping the two customers to a folder on my local computer – I would then email the file that is created to my client. However, the program is set up to let you use a “shared” file service such as DropBox (for example). You can set up a shared location between the buyer and seller, and enter the password for that location. Then you don’t have to email files back and forth. I like this feature.
Step 5 has you select a date range for invoices to export – and adds them to the list of invoices on the screen.
Step 6 will extract the selected invoices into the local program database.
Step 7 will create an export file in the location that you specified. You either send this file to the buyer or it is placed in the shared folder and you notify the buyer it is there.
Step 8 is very useful – you can get a report that lists the transactions that were sent.
It is important that this file be transferred in a secure manner as the export file is an unencrypted text file. It can be altered or viewed if it goes through the hands of someone who can’t be trusted.
Importing Invoices as Bills (the “Buyer”)
The “Buyer” is the person who is receiving the invoices and importing them into QuickBooks as Bills. The process that the Buyer follows is very similar to what the Seller performed.
As with any import function I recommend that you make a backup copy of your QuickBooks company file before you perform the import. There isn’t a simple way to “back out” an import if the process has some problem. I don’t anticipate any problems here, but I’m always cautious with any import. The import tool is using the reliable QuickBooks SDK method of interfacing with the company file, so it is very unlikely that there will be any file corruption or damage. It’s just that you can’t “roll back” a batch of imports, you would have to delete the transactions manually one-by-one if something wasn’t done correctly.
Step 1 opens the QuickBooks company file to work with on your local computer
Step 2 is only used if you are working with multiple QuickBooks company files on your side.
Step 3 is used to import your own account, item and vendor lists into the transfer program. You usually only have to do this one time to set things up, or later if you have some changes to these lists. The program will do some error checking at this time – one requirement is that you cannot have an item in the item list that has the same name as an account in the chart of accounts. I had “Freight” in both lists, and this error message was displayed:
Step 4 is used to set up the folders where you will find the import file. This can be a local folder if the import file is emailed to you, or you can select the shared “cloud” based folder like DropBox, as described above.
Step 5 lets you delete invoices from the list that you might not want to import for some reason.
Step 6 lets you set up a cross reference for vendors and the detail lines of the invoice. You need to associate the “Seller” account with a vendor in your QuickBooks file. You also need to associate the items in the detail section of the invoice to either items or accounts for your bill. This is a powerful feature and it looks like the program is covering all of the important issues. Each of the fields is a dropdown list of values that were imported from your QuickBooks company file.
Step 7 will perform the actual import into your company file. Note that the import file is deleted when the import is completed. That prevents you from accidentally importing the transactions twice – but you don’t have a fallback position if the import had a problem and you want to start over. If you are concerned about this you might want to make a backup copy of the import file before doing the import.
Step 8 lets you print an audit report that shows the transactions that were imported.
Let’s take a look at the bill that was created from the sample invoice that I started with:
You can see that the proper vendor was selected, that the memo line was updated, that each of the items shows (I mapped everything as an item, rather than as expenses, in this example), and that the quantity is correct. It is important to note that the sales tax is allocated to each of the items, which is the typical way that you would want to handle sales tax. The program takes the total tax (in a US version of QuickBooks), sums up the items that have a sales tax code of “Tax” to get an “allocation base”, then calculates the tax for each item as “(item amount / allocation base ) X tax amount”.
There is a preference setting in the program that allows you to turn this off and instead have the sales tax post to a particular account in your chart of accounts (but not to an item).
I’ve written a number of cross-company transaction programs myself as custom products. I’m very impressed with Karl Irvin’s Invoice To Bill utility. The price can’t be beat, and from what I can see he has handled all of the important and tricky mapping issues quite well.
The user interface isn’t very slick, but it follows the same format that Karl uses in all of his other utilities. Not a lot of “dazzle” here, just basic hard-working and efficient software that gets the job done. I would have liked some documentation – all you have is a brief description of the program on the web site as well as a few short videos. I’ve used several of Karl’s programs before and so I understand how you have to set up a QuickBooks add-on, and I am familiar with how his user interface works (you have to pay attention to that “status” line!), but a new user might find it a bit confusing at first. However, once it is set up, it runs smoothly.
This is a special purpose tool designed to do just this one task – import invoices from one QuickBooks company file as bills in another QuickBooks company file. It does exactly what it was designed to do!
For more information on this product go to http://www.q2q.us/itboverview.htm.
As I mentioned before, Karl Irvin’s main claim to fame has been his excellent Data Transfer Utility. For more information on this product see Doug Sleeter’s article, as well as my own Practical QuickBooks article. Both of these were written awhile ago, I hope to have an updated article in the next few months. The Data Transfer Utility was an Awesome Add-on winner in 2004.