Troubleshooting - How can we select Invoices for payment from the BillToPay table using QODBC

Problem Description:

In the BillToPay Table - How can we select invoices for payment using QODBC?

We would like to have the Checkmark Selected for the respective invoice.

Solution:

You will have to insert BillPaymentCheckLine, marking the Bill as Paid.

First, You need to query the BillToPay table for Bill you want to pay.

For Example:

SELECT * FROM BillToPay where BillToPayRefNumber='7893'



Bill in QuickBooks UI.

It would be best if you used the query below to mark the Bill as Paid.

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('90000-933272656', 'C0000-933270541', '20000-933270541', '7893', 'DF4-933785047', 1250)



Related Data Description

PayeeEntityRefListID = '90000-933272656' (Required) VendorRefListID from Bill table.

APAccountRefListID = 'C0000-933270541' (Required) APAccountRefListID from Bill table.

BankAccountRefListID = '20000-933270541' (Required)

RefNumber = '7893' (RefNumber of Bill)

AppliedToTxnTxnID = 'DF4-933785047' (TxnID from Bill Table)

AppliedToTxnPaymentAmount = 1250 (AmountDue from Bill Table)

Bill paid in QuickBooks.

Apply Vendor Credit to the Bill using Bill Payment

Assuming you already have a VendorCredit.

Get the TxnID of the Vendor Credit.

Select * from VendorCredit where VendorRefFullName='YourVendorFullName'

Use the following SQL statement to apply Vendor Credit.

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID,
APAccountRefListID,
BankAccountRefListID,
RefNumber,
AppliedToTxnTxnID,
AppliedToTxnPaymentAmount,
AppliedToTxnSetCreditCreditTxnID,
AppliedToTxnSetCreditAppliedAmount
) VALUES ('90000-933272656',
'80000020-1730714268',
'20000-933270541',
'7893',
'8DA-1763656441',
21,
'8F0-1764072149',
21)

Related Data Description

PayeeEntityRefListID = '90000-933272656' (Required) VendorRefListID from Bill table.

APAccountRefListID = '80000020-1730714268' (Required) APAccountRefListID from Bill table.

BankAccountRefListID = '20000-933270541' (Required)

RefNumber = '7893' (RefNumber of Bill)

AppliedToTxnTxnID = 'DF4-933785047' (TxnID from Bill Table)

AppliedToTxnPaymentAmount = 21 (AmountDue from Bill Table)

AppliedToTxnSetCreditCreditTxnID= '8F0-1764072149' (TxnID from VendorCredit Table)

AppliedToTxnSetCreditAppliedAmount= 21 (Amount you want to adjust towards Vendor Credit available from VendorCredit Table)