Author Topic: Trying to pull date invoice was paid.  (Read 2673 times)


  • Member
  • *
  • Posts: 1
Trying to pull date invoice was paid.
« on: January 28, 2010, 09:15:13 AM »
Hi Everyone,

I am trying to pull the date an invoice was paid from Quickbooks to FileMaker.
Anyone know how to do this?
I keep hitting End Of the QB File with no results.

Here is what I have maybe someone can tell me where I'm going wrong.

Set Field [Invoice::FMBCResult; PCQB_RqNew("ReceivePaymentQuery";"")]
Set Field [Invoice::FMBCResult; PCQB_RqAddFieldWithValue("AppliedToTxnList::TxnID";Invoice::QBTxnID)]

Set Field [Invoice::FMBCResult; PCQB_BeginSession(Invoice::QB_FileName)]
Set Field [Invoice::FMBCResult; PCQB_RqExecute]

Set Field [Invoice::FMBCResult; PCQB_RsOpenFirstRecord]
Set Field [Invoice::QB_PaidDate; PCQB_RsGetFirstFieldValue("TxnDate")]

Set Field [Invoice::FMBCResult; PCQB_PCQB_EndSession]

Thanks in Advance,



  • God
  • Member
  • *****
  • Posts: 83
Re: Trying to pull date invoice was paid.
« Reply #1 on: February 04, 2010, 05:05:42 PM »
Hi Mike,
It is not possible with the QBSDK (the plug-in) to query Received Payments based on the invoice to which they are applied.  You would only be able to query for received payments, then parse the response for the AppliedToTxnID::TxnID to see which invoices have been paid by the payment - this does not guarantee that the invoice is paid in full, only that the invoice has had a payment appliet to it.

If you know the TxnID of a particular invoice you can query for it, then parse the response for the IsPaid field...
PCQB_RqNew( "InvoiceQuery" )
PCQB_RqAddFieldWithValue( "TxnID" ; TheInvoiceTxnID )
PCQB_RsGetFirstFieldValue( "IsPaid" )

If you dont know the TxnID of the Invoice you can query for all paid invoices, or from paid invoices that have been modified after a specific date...
PCQB_RqNew( "InvoiceQuery" )
//omit the next line if you want ALL paid invoices - keep it to retrieve invoices modified after SomeTimeStamp
PCQB_RqAddFieldWithValue( "ModifiedDateRangeFilter::FromModifiedDate" ; SomeTimeStamp )
PCQB_RqAddFieldWithValue( "PaidStatus" ; "PaidOnly" )
 PCQB_RsGetFirstFieldValue( "TxnID" )
End Loop