Associate bank records with imported journal entry accounts receivable.

kvngrffn · ‎October 01, 2020

We use an external system to generate our invoices. The accounts receivable balance is brought into QBKs along with all the other systems' financial data feed via a journal entry. This means the A/R balance comes as one bulk entry for each period, and is associated with a single QBK's "customer".

Our bank accounts are linked to QBKs which automatically pulls in all the bank deposits. However, because the deposits do match any entries from the journal entry, they are not automatically associated with the customer record and are charged against the A/R balance.

The problem this creates is that the "customer" records shows all the entries as 'overdue'. Example of "customer" record:

09/30/2020

Deposit

09/30/20 ACH SETTLEMENT CMOL

09/30/2020

$-6,480.43

Overdue

09/28/2020

Deposit

09/28/20 PAYABLES CITY ACH CREDIT

09/28/2020

$-6,819.49

Overdue

09/23/2020

Deposit

09/23/20 ACH SETTLEMENT CMOL

09/23/2020

$-6,048.63

Overdue

09/21/2020

Deposit

09/21/20 PAYABLES CITY ACH CREDIT

09/21/2020

$-11,870.55

Overdue

09/20/2020

Journal Entry

42

DU--Accounts Receivable

09/20/2020

$16,864.55

Overdue

How do we associate the deposits from the bank records with the "invoices" that show on the client records without improperly adjusting the A/R balance with a double entry?

Nick_M · ‎October 01, 2020

Hi there kvngrffn.

Thanks for dropping by the Community, I;m happy to provide some feedback on this. The best thing you can do in this situation is connect the invoices to the journal entry that is created by the third party, you can do so following these steps:

Select + New.
Select Receive Payment.
Under Receive Payment, select the customer.
Make no entry on the form.
Under Outstanding Transactions, select the invoice.
Under Credits, select the journal entry to be applied as a credit.
(Note: The Credits section only appears if there is an unapplied customer credit. Make sure to record the journal entry correctly.)
Select Save and close or Save and new.

However, this is dependent on how those journal entries were set up. Therefor I would recommend reaching out to your accountant prior to following said steps just to make sure everything is set up as it should be. Thank you for your time and have a lovely evening.

View solution in original post

Nick_M · ‎October 01, 2020

Hi there kvngrffn.

Thanks for dropping by the Community, I;m happy to provide some feedback on this. The best thing you can do in this situation is connect the invoices to the journal entry that is created by the third party, you can do so following these steps:

Select + New.
Select Receive Payment.
Under Receive Payment, select the customer.
Make no entry on the form.
Under Outstanding Transactions, select the invoice.
Under Credits, select the journal entry to be applied as a credit.
(Note: The Credits section only appears if there is an unapplied customer credit. Make sure to record the journal entry correctly.)
Select Save and close or Save and new.

However, this is dependent on how those journal entries were set up. Therefor I would recommend reaching out to your accountant prior to following said steps just to make sure everything is set up as it should be. Thank you for your time and have a lovely evening.

Fiat Lux - ASIA · ‎October 01, 2020

@kvngrffn

You may have another option to record your data into QBO. Export your Invoice and Receive Payment data out to XLS and utilize TPImporter to import them in bulk. It would be easier to track any discrepancy later if required.

https:// transactionpro.grsm.io/qbo

The templates should be like these:

Invoice

RefNumber

Customer

TxnDate

DueDate

ShipDate

ShipMethodName

TrackingNum

SalesTerm

Location

Class

BillAddrLine1

BillAddrLine2

BillAddrLine3

BillAddrLine4

BillAddrCity

BillAddrState

BillAddrPostalCode

BillAddrCountry

ShipAddrLine1

ShipAddrLine2

ShipAddrLine3

ShipAddrLine4

ShipAddrCity

ShipAddrState

ShipAddrPostalCode

ShipAddrCountry

PrivateNote

Msg

BillEmail

BillEmailCc

BillEmailBcc

Currency

ExchangeRate

Deposit

ToBePrinted

ToBeEmailed

AllowIPNPayment

AllowOnlineCreditCardPayment

AllowOnlineACHPayment

ShipAmt

ShipItem

DiscountAmt

DiscountRate

TaxRate

TaxAmt

DiscountTaxable

LineServiceDate

LineItem

LineDesc

LineQty

LineUnitPrice

LineAmount

LineClass

LineTaxable

101

Janice Johnson

01/10/2020

01/12/2020

Ground

Net 30

110 Main Street

Suite 2000

Dallas

TX

99875

110 Main Street

Suite 2000

Dallas

TX

99875

Special Handling

Thank you for your order!

0

N

Y

N

Y

12

Shipping

0.02

Y

Leather Case

2

10

TAX

101

Janice Johnson

01/10/2020

01/12/2020

Ground

Net 30

110 Main Street

Suite 2000

Dallas

TX

99875

110 Main Street

Suite 2000

Dallas

TX

99875

Special Handling

Thank you for your order!

0

N

Y

N

Y

12

Shipping

0.02

Y

Gift Cards

2

5

TAX

102

Leonard Walker Inc

01/10/2020

Net 30

9 Winding Road

Winchester

NY

12345

9 Winding Road

Winchester

NY

12345

Thank you for your order!

0

Y

N

Y

Handhelds

Portable Game Player

1

100

TAX

Receive Payment

RefNumber	TxnDate	PaymentRefNumber	Customer	PaymentMethod	DepositToAccount	InvoiceApplyTo	LineAmount	PrivateNote	Currency	ExchangeRate
1	28/11/2020	50245	Janice Johnson	Check	Checking	101	100.00
2	28/11/2020	4908	Leonard Walker Inc	Check	Checking	102	138.78
3	28/11/2020	4908	Lisa Smith	Check	Checking	103	126.50

Topics

Training

Community

Resources

Associate bank records with imported journal entry accounts receivable.