How to import multi-lined transactions into QuickBooks

QuickBooks Desktop (Pro, Premier or Enterprise) > Transaction Pro Importer

Transaction Pro Importer for QuickBooks will group multiple lines in your import file onto one transaction (eg check, invoice etc.) based upon a common value that is mapped to the QuickBooks field, RefNumber, on the Transaction Pro Importer mapping screen.  See a sample of the Transaction Pro Importer mapping screen below.

 

There are a few exceptions to this rule which are detailed below.

  • Purchase Order:  Instead of using the RefNumber field to group multiple lines onto one transaction, Transaction Pro Importer looks at the QuickBooks field, PO Number
  • Receive Payment:  Transaction Pro Importer 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:  Transaction Pro Importer by default treats each line as a separate transaction.

Below is a sample sales receipt import file displayed within the File Contents screen.  Take a look a the column titled RefNumber.  Since the first two lines have the same RefNumber one sales receipt will be created by Transaction Pro Importer for Anne’s Bakery that has two lines on it.

Other Considerations

(1) Transaction Pro 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 preimport as Transaction Pro Importer for QuickBooks groups transactions with RefNumbers that follow sequentially in the file.  Since the RefNumber 1 does not follow sequentially in this invoice import file two separate invoices will be created with the RefNumber 1.

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 Transaction Pro Importer 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 Transaction Pro Importer that the entire import file is one journal entry.  You also should select the option to let QuickBooks auto assign journal entry numbers.

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 Transaction Pro Importer for QuickBooks and would like to try importing a multi lined transaction you can find our more and download a free demo here.

Add Feedback