A question we often get asked is if it is possible to import a transaction (eg. check, invoice, bill etc.) that has more than one line on it to QuickBooks with Transaction Pro Importer (“TPI”) for QuickBooks. The answer is yes and we provide for a lot of flexibility in how you data can be structured in your import file.
The simplest and most common import file structure is that each line of the transaction has some soft of repeating transaction id and each line of the same transaction follow sequentially in the import file. Map this column that contains the common transaction id to the QuickBooks field, RefNumber, on the TPI mapping screen.
There are a few exceptions to this rule which are listed below:
- Bank Statement: Each line is treated as a unique transaction.
- Credit Card Statement: Each line is treated as a unique transaction.
- Purchase Order: Instead of using the RefNumber field to group multiple lines onto one transaction, TPI looks at the QuickBooks field, PO Number.
- Receive Payment: TPI by default treats each line as a separate transaction. If you have multiple lined receive payment transactions in your import file please review this blog post.
- Time Tracking: Since TPI is creating a single time entry transaction each line is treated as a unique transaction.
The example below displays the import file data on the file contents screen within TPI for a sales receipt import. Lines 5 through 9 all display the same value 102 in the RefNumber column so a sales receipt will be created for Main St Bank with four lines on it.
(1) TPI only looks at the RefNumber column to determine where a transactions stops and starts and posts that transaction to the name (customer, vendor, employee or other) that is on the first line of the grouping that has the same RefNumber. So in the sample import file below one invoice would be created with four lines under the Customer, Anne’s Bakery, as this is the customer that appears on the first line of the same sequence of RefNumbers.
(2) In addition make sure that your file is sorted by the RefNumber pre-import as Transaction Pro Importer for QuickBooks groups transactions with RefNumbers that follow sequentially in the file. Since the RefNumber 100 does not follow sequentially in this invoice import file two separate invoices will be created with the RefNumber 100.
Some creative ways to use the QuickBooks field, RefNumber
(1) What if my import file does not have a field that contains a transaction number? Perhaps when the customer changes in your import file that is when a new transaction starts. Then you could map the import file column that has customer name in it to the Quickbooks field, RefNumber. You will also need to select the option on the basic options tab within Transaction Pro Importer to truncate import field data and to let QuickBooks auto assign the transaction number.
(2) What if each line is a separate transaction but I do not have a transaction number column in my import file? You can simply add a column called RefNumber and then just increment each row by 1 (eg, 1,2,3 etc.). You could also reuse this numbering sequence in future import files. You should also select the option on the basic options tab within TPI to let QuickBooks auto assign the transaction number.
(3) What if the entire import file is one transaction? Lets say that your payroll company sends you a file each pay period that has one journal entry for the entire payroll but they do not include any sort of a journal entry number. In this case you could probably map the QuickBooks field, RefNumber to the date column in the import file. Another option would be to type in a static value next to the QuickBooks field, RefNumber which would then tell TPI that the entire import file is one journal entry. You also should select the option to let QuickBooks auto assign journal entry numbers.
Other Advanced Options
What if my file format does not meet any of the above requirements? Don’t worry we have a lot of advanced options to work with almost any sort of import file. Please read more below:
On the Advanced Options Tab
Enable columns as rows for bills/checks: If your amounts are all across one line turn on this option. Read more here.
On the e-Commerce Tab
Under Import File Structure select the second option: Separate columns for each item sold. Read more here.
If you have any questions or helpful tips on the use of the QuickBooks field, RefNumber, within Transaction Pro Importer for QuickBooks please post a comment below. If you do not already have TPI and would like to try importing a multi lined transaction you can find our more and request a free demo here.