Resource: QuickBooks Invoice Payments

This article covers the Invoices: Payments Resource in detail. If you haven’t yet learned about Resources in general and how to get the most out of them, we suggest you check out our Resources support article first.

What Is an Invoice Payment?

An invoice payment in QuickBooks Online records money a customer paid you and applies it to one or more of that customer’s open invoices. It is the same thing you create with Receive Payment in QuickBooks.

The Invoices: Payments Resource lets you download, create, update and delete these payments from Excel or Google Sheets. It is useful when you have a batch of payments to record, for example from a bank export or a payment processor report, and don’t want to enter them one at a time. This resource is in beta.

Filters for Downloading Payments

When you want to download a group of payments to your worksheet, use the filters on the left side of the Resource template. The filters are cumulative, so if you make multiple selections you get only the payments that match all of them.

• Customer: download only payments from one customer
• Start Date / End Date: download payments received in a date range

Downloading a few payments first is the easiest way to see how your own data is laid out before you upload anything.

Payment Data Structure

Each payment is one row for the payment itself, with one line for each invoice it pays. The first row holds the fields for the whole payment:

• Date: the date the payment was received
• Customer: the customer who paid, picked from the dropdown
• Number: the payment’s reference number, such as a check number (optional)
• Payment Method: for example Check or Credit Card, picked from the dropdown (optional)
• Deposit To Account: the account the money went into, picked from the dropdown (optional)
• Memo: a note on the payment (optional)
• Total Amount: the full amount received

Each line of the payment, including the first row, holds one invoice it pays:

• Txn Type: Invoice
• Txn ID: QuickBooks’ internal ID for the invoice (see below)
• Amount: how much of this payment goes to that invoice
• Doc Number: the invoice number. This column is filled in when you download, so you can see which invoice each line belongs to. It is not used on upload.

For example, a $1,500 payment that pays invoice 1001 in full ($1,000) and part of invoice 1002 ($500) is two rows. The first row has the Date, Customer, Total Amount of 1,500, and the line for invoice 1001. The second row has only the line for invoice 1002. Leave the payment fields empty on the second row so Amalgam reads both rows as one payment.

Total Amount should equal the sum of the line amounts. If it is more, QuickBooks keeps the difference as an unapplied credit on the customer.

Finding the Txn ID for an Invoice

The Txn ID is not the invoice number you see in QuickBooks. It is the ID QuickBooks uses internally. To find it:

1. Download the invoices you are applying payments to with the QuickBooks Invoices Resource.
2. Unhide the hidden columns with the + button at the top of the sheet and find the ID column.
3. Copy each invoice’s ID into the Txn ID column of your payment rows, or look it up with a formula that matches on the invoice number.

Reference Fields

Customer, Payment Method and Deposit To Account refer to data that already exists in QuickBooks, so Amalgam gives you a dropdown list for each. If you add a new customer or account in QuickBooks, use the refresh button on the sidebar to update the lists.

Some hidden columns, like Customer ID, are filled in by formulas from your dropdown choices. We caution against changing them unless you know what you’re doing.