Author Topic: Query an Invoice Paid Date and Amount  (Read 2241 times)

oagi1771

  • Member
  • *
  • Posts: 1
Query an Invoice Paid Date and Amount
« on: September 12, 2012, 10:26:16 AM »
I have a script that will pull an "IsPaid" from an invoice query.  I need to pull the Paid Date and Paid Amount as well. Does any one have a suggestion?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Query an Invoice Paid Date and Amount
« Reply #1 on: September 12, 2012, 12:07:48 PM »
Your InvoiceQuery Request will need to include...

PCQB_RqAddFieldWithValue( "IncludeLinkedTxns" ; "true" )

...and when the value of "IsPaid" is "true" you'll need to parse the Response for the ReceivePayment LinkedTxn with the most recent date. I don't recall whether the returned values might include CreditCardCharges or CreditMemos that were applied in separate LinkedTxn elements, or whether the application of any type of payment is identified as ReceivePayment.

Making this foolproof is easiest if you know how to apply XPATH filters, but is beyond the scope of a forum discussion in this context.

HTH!


Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

hudi

  • Member
  • *
  • Posts: 8
Re: Query an Invoice Paid Date and Amount
« Reply #2 on: August 03, 2014, 09:14:44 AM »
...and when the value of "IsPaid" is "true" you'll need to parse the Response for the ReceivePayment LinkedTxn with the most recent date.

Hi Jeoffry,

Can you please post an example of this query? Specifically, How do I parse the response to show me the date of the last payment. Even better, I never have more than one payment on an invoice and it's always for the full amount. So I would not need to filter them, simply ask for the date of the latest payment.

Thank you!

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Query an Invoice Paid Date and Amount
« Reply #3 on: August 03, 2014, 12:39:37 PM »
You'd add an XPATH parameter that describes the location of the value you want. Here's one such path...

"/InvoiceQueryRs/InvoiceRet/LinkedTxn[ TxnType = \"ReceivePayment\" ]/TxnDate/text()"

...to pull out a list of all TxnDate values for all Linked ReceivePayment records. Given your description, you'll have a single date value in QB DateType format. Use the PCQB_SFormatString( String ; qbType ) to convert the date to FMP format by inserting the parsed value as the String parameter and "DATE" as the qbType parameter.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

hudi

  • Member
  • *
  • Posts: 8
Re: Query an Invoice Paid Date and Amount
« Reply #4 on: August 04, 2014, 08:27:20 AM »
Geoffrey (spelled your name wrong last time, my mistake),

Thanks for the quick reply. The code seems simple enough. But, I've never used Xpath in the context of this plugin. Where does this code go? Is it a parameter in a PCQB function?

Thanks

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Query an Invoice Paid Date and Amount
« Reply #5 on: August 04, 2014, 11:53:27 AM »
I use the PCQB_Z... functions almost exclusively and hadn't noticed that they're the only ones that have an XPath parameter. The path I posted works when applied to the response to PCQB_ZExecute, but otherwise needs a slight amendment so the parser will have a valid starting point. Here's a complete argument to extract the value once you've completed the PCQB_RqExecute step...

PCQB_ZParseXML( PCQB_SGetXML( "Response" ; "" ) ; "" ; ".//InvoiceRet/LinkedTxn[ TxnType = \"ReceivePayment\" ]/TxnDate/text()" ; "" )

This assumes your Request will return only one Invoice node. If you use a broader request (something other than a single TxnID element) it will return a list of ReceivePayment dates for all returned Invoice nodes that have a related ReceivePayment transaction.

XPath is an extremely useful tool, but has a learning curve that discourages many who won't use it frequently.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852