Productive Computing, Inc. Support Forum

Plug-ins => FM Books Connector => Topic started by: miafrate on January 28, 2010, 09:15:13 AM

Title: Trying to pull date invoice was paid.
Post by: miafrate 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,

Mike.
Title: Re: Trying to pull date invoice was paid.
Post by: Chris 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_RqExecute
PCQB_RsOpenFirstRecord
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_RqExecute
PCQB_RsOpenFirstRecord
Loop
 PCQB_RsGetFirstFieldValue( "TxnID" )
 ...
 PCQB_RsOpenNextRecord
 ...
End Loop

Chris