Modify an existing payment on QB from FM

[siroos12]

Hi all, I am trying to modify an existing payment on QB from FM with no luck. Here is part of my script which sets the mode to ADD or MOD based on query ran on payments on QB. So the script would check if the payment exists on QB, then sets the mode to MOD if not sets it to ADD:

#Check if payment exists on QB
Set Variable [ $$Result; Value:PCQB_RqNew( "ReceivePaymentQuery" ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; PAYMENTS::_ID_QBTxnID)]
Set Variable [ $$Result; Value:PCQB_RqExecute ]
Set Variable [ $$Status; Value:PCQB_SGetStatus ]
If [ $$Result = 0 ]
#Payment exists on QB
Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentMod" ; "") ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID" ; PAYMENTS::_ID_QBTxnID)]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; PAYMENTS::QBEditSequence)]

Else

#Payment does not exists on QB so, push it to QB
Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentAdd" ; "") ]
End If

Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "CustomerRef::ListID"; CUSTOMERS::
_ID_QBCustomerListID ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnDate"; PAYMENTS::Date )]
If [ PAYMENTS::PaymentType = "Check"]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::CheckNumber)]
Else If [ PAYMENTS::PaymentType = "Credit" ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::AuthNumber)]
End If
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TotalAmount" ; PAYMENTS::Amount )]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "PaymentMethodRef::FullName"; If(PAYMENTS::
PaymentType = "Credit" ; PAYMENTS::CardType ; PAYMENTS::PaymentType))]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "DepositToAccountRef::FullName"; Case ( PAYMENTS::
PaymentType = "Cash" ; "Cash in register" ; PAYMENTS::PaymentType = "Check" ; "Checks for deposit" ;
PAYMENTS::PaymentType = "Credit" ; "CC Deposits" ) )]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::TxnID"; payments_ORDERS::
_ID_QBTxnID) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::PaymentAmount"; PAYMENTS::Amount)]
#Execute
Set Variable [ $$Result; Value: PCQB_RqExecute ]
Set Variable [ $$Status; Value:PCQB_SGetStatus ]
 

 

This script adds the payment successfully into QB on the first attempt when there is no such a payment on QB. But if users modify the payment record and try to push the modified payment to QB, they get no error while that Payment on QB remains the same as its initial state.

Any advise would be appreciated.

 

Edited July 31, 2018 by siroos12

[PCIPal]

Hi Siroos,

When you are performing your initial query for the payment in question, in the event that PCQB_RqExecute returns 0, you should open the first response record and pull the EditSequence property. That way, when you start constructing your ReceivePaymentMod request, you pass in the EditSequence where it belongs, which ensures that you are referencing the latest and most up-to-date version of the payment that you are modifying. If you don't use that edit sequence, and instead use whatever edit sequence is stored in your FileMaker solution, that is not guaranteed to be the latest edition, and thus QuickBooks will not accept the modification request.

Hope this helps!

- Chris Turner

[siroos12]

Chris, Thanks for quick response. Indeed,I do capture EditSequence properly and use it when editing a record. My problem was setting "AppliedToTxnAdd::TxnID" and "AppliedToTxnAdd::PaymentAmount" while modifying an existing record. Those are not supported propertied for MOD mode.

After modifying the script as below, it works like a charm:

#Check if payment exists on QB
Set Variable [ $$Result; Value:PCQB_RqNew( "ReceivePaymentQuery" ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; PAYMENTS::_ID_QBTxnID)]
Set Variable [ $$Result; Value:PCQB_RqExecute ]
Set Variable [ $$Status; Value:PCQB_SGetStatus ]
If [ $$Result = 0 ]
#Payment exists on QB
Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentMod" ; "") ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID" ; PAYMENTS::_ID_QBTxnID)]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; PAYMENTS::QBEditSequence)]

Else

#Payment does not exists on QB so, push it to QB
Set Variable [ $$Result; Value:PCQB_RqNew("ReceivePaymentAdd" ; "") ]

Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::TxnID"; payments_ORDERS::
_ID_QBTxnID) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "AppliedToTxnAdd::PaymentAmount"; PAYMENTS::Amount)]
End If

Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "CustomerRef::ListID"; CUSTOMERS::
_ID_QBCustomerListID ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnDate"; PAYMENTS::Date )]
If [ PAYMENTS::PaymentType = "Check"]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::CheckNumber)]
Else If [ PAYMENTS::PaymentType = "Credit" ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; PAYMENTS::AuthNumber)]
End If
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TotalAmount" ; PAYMENTS::Amount )]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "PaymentMethodRef::FullName"; If(PAYMENTS::
PaymentType = "Credit" ; PAYMENTS::CardType ; PAYMENTS::PaymentType))]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "DepositToAccountRef::FullName"; Case ( PAYMENTS::
PaymentType = "Cash" ; "Cash in register" ; PAYMENTS::PaymentType = "Check" ; "Checks for deposit" ;
PAYMENTS::PaymentType = "Credit" ; "CC Deposits" ) )]

#Execute
Set Variable [ $$Result; Value: PCQB_RqExecute ]
Set Variable [ $$Status; Value:PCQB_SGetStatus ]

[PCIPal]

Hi Siroos,

That works for creating new Received Payment records in QuickBooks, certainly. However, you should also be able to add transactions to an existing received payment, so long as the payment still has available unassigned funds.  You would use the aggregate link field "AppliedToTxnMod" instead of "AppliedToTxnAdd".  There is an example in Intuit's QuickBoosk Desktop SDK docs page that is similar to this situation (although the docs are out-dated, it should still be valid): https://developer.intuit.com/docs/01_quickbooks_desktop/2_build/30_tutorials/44_appendix_f_overpayments_and_refunds

Hope this helps!

- Chris Turner

[siroos12]

Chris,

I have another situation. I have an invoice in FM which is pushed to QB successfully. After that, a payment has been made for that invoice and it is recorded in FM but not pushed to QB yet. A user made a mistake and manually added the payment to QB. 

The next user comes in and tries to push the payment to QB while it is already added to QB manually.

We need to query that payment before pushing it to QB to make sure that it does not exists in QB. If the payment was added to QB using the plugin, we would have a Reference number and TxnID for it so, we could easily search based on those. But, as it is manually added to QB, we are stocked.

 

Any Idea of how we can find that payment in QB without having a Reference number or TxnID? Can we use a combination of invoice reference number and the payment amount? If yes, how do we do that?

Edited September 14, 2018 by Lee Smith
Removed entire quote as not needed.

[Geoffrey Gerhard]

In your scenario, is the manually entered QB payment applied to the Invoice?

If so, you can do an InvoiceQueryRq for the TxnID and IncludeLinkedTxns = true. The Response will include all ReceivePayment records applied to the Invoice. The LinkedTxn node includes the Type, Date, RefNumber, and Amount values, which may be sufficient to identify the manually entered ReceivePayment. You could also parse out any/all TxnID values for linked ReceivePayment records, and--assuming you're storing the ReceivePayment TxnID in FMP, perform a find for each. NOTE: I don't recall offhand if the LInkedTxn node's Amount value is for the ReceivePayment TotalAmount, or the Amount Applied to the Invoice, but I think it's the latter.

If the ReceivePayment is not applied when recorded manually in QB, a combination of ( ModifiedDateRangeFilter or TxnDateRangeFilter ) and EntityFilter::ListID might help if you're looking for a ReceivePayment for a particular Customer.

And if you're just looking to find any manually entered ReceivePayment records within a date range, use the ModifiedDateRangeFilter. While it may include some older ReceivePayment records, it will definitely include all those added within the specified range. As suggested above, as long as you're storing the TxnID in your FMP Payments table you can run FMP finds to identify any TxnID that is not a Payment::TxnID field value, and query for/use the ReceivePayment TotalAmount value to find the match among FMP with an empty Payment::TxnID field.

HTH!

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

[siroos12]

Hi Geoffrey,

I am trying to  do an InvoiceQueryRq for the TxnID and IncludeLinkedTxns = true and it freezes and then FileMaker crashes. 

any ideas?

Edited September 14, 2018 by Lee Smith
Removed entire quote as not needed.

[Geoffrey Gerhard]

The process can appear "frozen" when the TxnID is empty and the QB Company file has a lot of Invoices. No clue why it would freeze and crash if the elements and their values are valid ( and not empty. ) Something like this...

Set Variable [ $$Result; Value:PCQB_RqNew( "InvoiceQuery" ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; INVOICES::_ID_QBTxnID)]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "IncludeLinkedTxns" ; "true" )]
Set Variable [ $$Result; Value: PCQB_RqExecute ]

...should produce an almost instantaneous result.

BTW: In the early phases of writing and testing any QueryRq, you can save yourself a lot of wait time from a badly formed Request by applying one or more IncludeRetElement filters. They dramatically reduce the amount of data returned, and in the event that your Request is returning every record ( as happens when you're querying a transaction table and TxnID is empty/unspecified ) the difference in speed can be staggering. For your purposes, I recommend this...

Set Variable [ $$Result; Value:PCQB_RqNew( "InvoiceQuery" ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID"; INVOICES::_ID_QBTxnID)]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "IncludeLinkedTxns" ; "true" )]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "LinkedTxn" )]
Set Variable [ $$Result; Value: PCQB_RqExecute ]

...to winnow the data QB gathers and returns to just the LinkedTxn data for the target Invoice.

HTH!

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

[siroos12]

Awesome advise.will apply that to my script. And yes, the TxnID was empty. my bad!

Edited September 14, 2018 by Lee Smith
Removed entire quote as not needed.