Author Topic: Pulling Invoice Number from Payments  (Read 3044 times)

Will

  • Member
  • *
  • Posts: 19
Pulling Invoice Number from Payments
« on: February 07, 2010, 02:53:35 PM »
I want to pull down all the payments made to an job and U think this is doable with the ReceivePaymentQuery based on a specific Customer:Job.


Loop
   Exit Loop If.....
    Set Field [Utility.QBResult = "End"....
    Set Field [G.PaymentDate; Value:PCQB_RsGetFirstFieldValue("TxnDate")]
    Set Field [G.PaymentRef; Value:PCQB_RsGetFirstFieldValue("RefNumber")]
    Set Field [G.PaymentMethod; Value:PCQB_RsGetFirstFieldValue("PaymentMethod:FullName")]
    Set Field [G.InvoiceNumber; Value:PCQB_RsGetFirstFieldValue("AppliedToTxnRet:RefNumber")]
    Set Field [G.PaymentAmountr; Value:PCQB_RsGetFirstFieldValue("AppliedToTxnRet:Amount")]
    ...

The first three fields, G.PaymentDate, G.PaymentRef and G.PaymentMethod are populating correctly. The problem, however, is that the remaining fields, G.InvoiceNumber and G.Amount are not.

I've tried adding "ReceivePaymentReg:AppliedToTxnRet:RefNumber" and that didn't work. Per a topic on this board about ReceivePaymentsAdd, I also tried adding the filter  PCQB_RqAddFieldWithValue( "AccountFilter:FullName";"Accounts Receivable") but that only caused a error

I must be doing something wrong, but I can't figure out what it is. Any help would be hugely appreciated.


- Will


Will

  • Member
  • *
  • Posts: 19
Re: Pulling Invoice Number from Payments
« Reply #1 on: February 07, 2010, 03:32:44 PM »
Found the problem... needed to include the requirement:  IncludeLineItems

A good Sunday afternoon squandered. Shame on me.

- W

Chris

  • God
  • Member
  • *****
  • Posts: 83
Re: Pulling Invoice Number from Payments
« Reply #2 on: February 10, 2010, 09:47:19 AM »
Hi Will,

Note that the "AppliedToTxn" aggregate may include more than one transaction - the payment may be applied to more than a single invoice.  I suggest modifying your script to account for this...

...
   Set Field [G.PaymentMethod; Value:PCQB_RsGetFirstFieldValue("PaymentMethod:FullName")]
   #add a loop to iterate over the AppliedToTxnRet records
   Set Variable[ $x ; PCQB_RsOpenFirstRelatedRecord( "AppliedToTxnRet" )
   If[ $x = 0 ]
    Loop
     Exit Loop If[ $x <> 0 ]
     #record which txns to which the txn is applied, the amt applied, etc.
     #note that the cursor is on the "AppliedToTxnRet" node so a query for its
     #values do not need to be prepended with "AppliedToTxnRet::"
     Set Variable[ $amt ; PCQB_RsGetFirstFieldValue( "Amount") ]
     ...
     #open the next AppliedToTxnRet aggregate
     Set Variable[ $x ; PCQB_RsOpenNextRelatedRecord ]
    End Loop
    #close the related records and return to the payment record
    Set Variable[ $x ; PCQB_RsCloseRelatedRecord ]
  End If
  ....



Chris

Will

  • Member
  • *
  • Posts: 19
Re: Pulling Invoice Number from Payments
« Reply #3 on: February 10, 2010, 10:16:43 AM »
Thanks for the reply Chris. I'll give it a shot and let you know the result.