Pulling Linked Txns

[trebor97]

I think I did this  a while ago but forget how to pull LinkedTxns with Invoices.  I keep getting an error " no current record". However I know there is a payment because the linked lIne items is for 4670 and BalanceRemainig is 460.  I can slo see it in the transaction history in QB. 

I have a test scrip only pulling one invoice at a time. 

Anybody have an example fo pulling the LikedTxns? 

Thnx

[PCIPal]

Hi trebor,

 

When performing your InvoiceQuery, make sure to specify the "IncludeLinkedTxns" property in the query request with a value of "True". Example:

Set Variable [ $result ; Value:PCQB_RqAddFieldWithValue( "IncludeLinkedTxns" ; True )]

When you execute the query, QuickBooks will gather any transactions linked to the invoice(s) you are querying for, and they will show up in the "LinkedTxn" element in the response. From there, you can iterate over the "LinkedTxn" related records and gather the TxnIDs and their types, and proceed onwards to querying for and pulling down those other transactions with their own individual queries. For example, if you include linked transactions for an invoice, you would get any received payments or credit memos that are applied to that invoice. You could then move onward in a later process and use those transaction IDs to query for the details of each received payment and credit memo that was linked.

 

Thanks!

- CT

[trebor97]

I think I am going to try a different approach.....

I am going to query the received payments based on RefNumber. That way I am only getting the payments I need.  I was unable to cycle (iterate) through the linked Txns when pulling Invoices. 

Going to play with this  a bit more.  If I really get stuck I will post my script that was not parsing the linkedTxns. It parsed the line items fine, but not the linkedTxns. 

THanks

[Geoffrey Gerhard]

In case you revisit this issue, it's worth remembering that the LinkedTxn node is essentially a related record that may have multiple occurrences.

You'd use PCQB_RsOpenFirstRelatedRecord ( "LinkedTxn" ) to get the values of TxnID, TxnType, etc. from the first repetition, and then PCQB_RsOpenNextRelatedRecord to get values for each subsequent rep.

If a Customer makes multiple payments against the same Invoice, it would show multiple LinkedTxn nodes with a TxnType of "ReceivePayment".

Each LinkedTxn node for ReceivePayment and CreditMemo contains what most identify as the most relevant details of that payment: Date, RefNumber, Amount.

Note that you will need to go to the ReceivePayment source record if you need to differentiate between the components of the LinkedTxn node's Amount value, which is the sum of PaymentAmount and DiscountAmount. ( If you applied a CreditMemo in that same ReceivePayment, the CreditMemo will be in its own LinkedTxn node. )

Hope this helps!

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

[trebor97]

So I have my Invoice pull script grabbing the LinkedTxns. I have all the data I need, I think. 

If I wanted to put the invoice back (into another file , yes all the customers, items, classes etc, are the same: invoices all go back in fine), how do I add back the linked transaction? Do I need to put it back as it is, a payment (ReceivePaymentAdd)? I do not see any "add linked txns" in the osr for invoiceadd. 

I guess I have to add it back using the RecievePaymentAdd.  However I think I need a bit more info than the Linked TXN gives me.  I have a script that pulls the payment info (RevcievedPaymentQuery) based on the Ref number I got from the linked transactions.  Then I can use that to get all the details on the payment record to put it back in. 

Am i barking up the right tree here.  

[Geoffrey Gerhard]

I'm not entirely clear on what you're trying to accomplish, but my initial reading suggests that you'd use the ReceivePaymentAddRq using the new QB file's Invoice(s) TxnID within each AppliedToTxnAdd node.

The ReceivePaymentQueryRs should contain all other values needed to complete each AppliedToTxnAdd node in the ReceivePaymentAddRq, as long as the ReceivePaymentQueryRs includes the IncludeLineItems element with a "true" value.

HTH!

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

[trebor97]

Ouch, Applying payments is not as straight forward as adding in invoices....... definitely need some more help on automating the ReceivePaymentAdd. 

After I put the Invoice into QB, I need to pull the TxnID of that Invoice (Ok not terribly complicated, done that before), then use that TxnID in the RecievePaymentAdd --> AppliedToTxnAdd as the TxnID (no macro?? or macro?? kinda a little confused on that) and add amount. 

I have never had to work with paymentadd's before, breaking new ground. 

-- Project
move all open invoices (outstanding invoice balances) from old QB file to new one.....why I am not 100% sure..... but if they want to do this..... 

So far all is working as I need, except the payments.  Once that is done, then I can move invoices and their payments, which should tie out to the old AR detail report (invoices only:: other stuff will be done manually). So far my tests have all been working out on sub sets of the data (one customer at a time), except I had to enter payments manually when they appeared.  Want to avoid that.  If there are no payments, all sends over perfectly, to the penny......just need to compensate for those records (unknown how many) that have payments on them.  It is only $5+ million in invoices to transfer and balance to the penny....... arg.....so far i am only off by payment amounts.  

 

[Geoffrey Gerhard]

Migrating data from one QB Company File to another is not trivial, with lots of potential traps for those new to QB.

Potential pitfalls include: Account Balances, Inventory Counts/Values, Bank Balances, Receivables, Payables, Credit Memos, Bill Credits...

The order in which you populate the new file is relevant, and especially so if there's Inventory Items on open Invoices or Bills. Vendors and Items need to exist before you can recreate the Open Bills, Bills need to be created to adjust the Inventory Counts/Values prior to Invoicing, as do Customers ( and Customer:Jobs, if relevant ), and Invoices need to exist before you can add ReceivePayments.

And ReceivePayments that apply to multiple Invoices may need to be adjusted to reflect Invoices that were paid in full ( and therefore not on your list of Open Invoices. )

As I said, it's not trivial. If you need help, I've been doing this for years and would be happy to consult. I have experience and a tool for this, and for combining multiple QB Company Files into a single new Company File.

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

[trebor97]

Yup I had to bring over the customers, and a bunch of lists (Items etc). It is all working except the payments on the invoices.......that is todays task. 

If I can get the payment to apply to the invoice, then i think I am good to go because it is all balancing out (once the payments are taken into consideration). 

I am pulling the new Invocie TxnID after inserting the invoices into the new system, so I have that for the Payments insert. 

I m somewhat at a los as to how to use the "AppliedToTxnAdd". 

??
PCQB_RqAddRelatedRecord("AppliedToTxnAdd")
PCQB_RqAddFieldWithValue("TxnID"; [field with value in it from new File invoice add])
PCQB_RqAddFieldWithValue("PaymentAmount"; [field with value in it])
PCQB_RqCloseRelatedRecord

Close?  going to give it a go and see what the errors are........ knowing full well there will be.....

I am so close I can taste it.  All is good in the new file except the open invoices balances. Once I get those over, the new file is good to go, according to their accountant. 

 

[Geoffrey Gerhard]

The AppliedToTxnAdd steps cited above look right, as long as there's no CreditMemo AppliedAmount or DiscountAmount applied to the targeted Invoice. And any ReceivePayment record that applies to more than one Invoice will need to Loop through those steps....

Good luck!

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

[trebor97]

Uh oh.... you said it... "...... as long as there's no Credit Memo....." I have to apply a payment, or a credit, against an invoice. I am not sure which is the way to go.  ApplyCreditMemo or RecievePaymentAdd? 

The Linked transaction from the old Qb is a recievePayment.  So I have been trying to use RecievePaymentAdd, but it always is failing on the linked transaction TxnID. 

I am adding a related record (AppliedToTxnAdd) and using the TxnID I pulled back when the system added the invoice. But it fails claiming the ID is unknown. 

Any ideas is much appreciated.....getting frustrated as all hell.........otherwise adding back invoices is working to the penny, as long as there are no payments, which of course in reality there are. 

#Prep for Inser Paymnent
Set Field [ Invoices::qResult ; PCQB_RqNew( "ReceivePaymentAdd"; ) ]
Set Field [ Invoices::qResult ; PCQB_RqAddFieldWithValue( "CustomerRef::FullName"; Invoices::Customer Ref Full Name; ) ]
Set Field [ Invoices::qResult ;
PCQB_RqAddFieldWithValue( "ARAccountRef:FullName";RecievedPayments::ARAccountRef FullName; ) &
PCQB_RqAddFieldWithValue( "TxnDate";RecievedPayments::TxnDate; ) &
PCQB_RqAddFieldWithValue( "RefNumber"; RecievedPayments::Ref Number; ) &
PCQB_RqAddFieldWithValue( "PaymentMethodRef:FullName"; RecievedPayments::PaymentMethodRef Full Name; ) &
PCQB_RqAddFieldWithValue( "Memo"; RecievedPayments::Memo; ) ]
Set Field [ Invoices::qResult ; PCQB_RqAddRelatedRecord( "AppliedToTxnAdd"; ) ]
Set Field [ Invoices::qResult ; PCQB_RqAddFieldWithValue( "TxnID" ; Invoices::TXNID_New_File ) ]
Set Field [ Invoices::qResult ; PCQB_RqAddFieldWithValue( "PaymentAmount" ; -1*RecievedPayments::k ) ]
Set Field [ Invoices::qResult ; PCQB_RqCloseRelatedRecord ]
Set Field [ Invoices::qResult ; PCQB_SGetStatus ]
#Create Add Payment(Begin session, execute, close session)
Set Field [ Invoices::qResult ; PCQB_SGetStatus ]
Set Field [ Invoices::qResult ; PCQB_BeginSession( ""; "" ) ]
Set Field [ Invoices::qResult ; PCQB_RqExecute ]
Set Field [ Invoices::qResult ; PCQB_SGetStatus ]
Set Field [ Invoices::qResult ; PCQB_EndSession ]
Set Field [ Invoices::qResult ; PCQB_SGetStatus ]

 

 

 

 

 

[Geoffrey Gerhard]

The error likely speaks for itself. Consider these steps to track down the problem:

1) Confirm that the related Invoices::TXNID_New_File record has a value is in the field.

2) Make sure there's only one value, and that it's an Invoice TxnID.

3) Confirm there's a corresponding Invoice in QB.

4) Try doing an InvoiceQueryRq using the RefNumber displayed in QB, and see whether the TxnID in the Response matches the stored value in the FMP record.

You also mentioned Credit Memos. In my experience, they're usually applied within the framework of a ReceivePayment. Your script does not show any steps that apply a Credit Memo's values to an Invoice.

As noted in an earlier reply, moving data from one file to another can be pretty complicated.

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

Edited July 18, 2018 by Geoffrey Gerhard

[trebor97]

OK I figured it out, I was using the wrong RefNumber.  I had it pointing to the RefNumber of the RecievedPayment, not the invoice.  Once I changed that it went in fine, applied to the invoice and balance to the penny.  

I had this working with a single invoice and one payment but when there were multiple payments or multipole invoices or a combination of both it was choking up.  After looking into it I noticed I also had a bad relation, resulting in pulling the wrong info. 

That is why the ID kept kicking out, it was not related to that invoice in the destination QB file. 

I am cooking with gas now, all working perfectly, now to automate it. It is all manual, I need to set it up so a non-programmer can use it.  Series of step through buttons. 

It is funny, I let this lay overnight and came at it with "fresh" eyes this morning and nailed it.  Somehting to be said about moderate level of anxiety. 

My very first FMPro solution with PCComputings Plug in was to take a spreadsheet of POs with Payments, match them to invoice numbers then upload the payments to the system en-mass.  So I had successfully done recievepaymentadd before, just had to refresh my memory. 

I am good now, at least with this issue........

[trebor97]

I was thinking of starting a different topic but this relates to the LinkedTxn's.

When I pull the Linked TXN's one of the records created is a CreditMemo, only thing is there are only 9 of them.  

Should I be confident that if it pulled 2788 invoices with invoice items that I got all the linked TXN's?  It pulled 385 Payment Linked Txn's. 

I only ask because it seems low.  However I have not run into a single instance where the balances are not tying out to the penny after applying the credits to the specific customers and invoices.  All 9 of them, everyone else is tying out perfectly with only the payments applied.  So maybe there were only 9 credits I needed to pull. 

Strikes me as low, because I am pulling open invoices as far back as 2014, but nothing I have seen indicates I should have more credits. 

Apparently there are customer credits to be applied (about 30-35 of them) but no invoices to go against. So I am going to have them do those manually.  These would be customer credits for future work, not to be applied to anything current.  These I don't have to worry about, it is just the credits linked to the Invoice.  No tonly that, they are on the AR Detail report I started with, so I have a list of them. 

So, should I be confident I pulled all the CreditMemos? I am gaining confidence I did it right based on how everything is balancing out to the penny. 

 

 

[Geoffrey Gerhard]

Not quite clear on what you're looking for. You could do a CreditMemoQueryRq that limits the Response to TxnID nodes using the IncludeRetElement, and then apply the FMP PatternCount function to the result. The pattern count of "<TxnID" occurrences will tell you how many total CreditMemos are in QB.

Is your goal to pull all CreditMemos, to pull all CreditMemos applied to one or more Invoices, or something else?

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

[trebor97]

WellI think I have answered my own question working through this all day. 

I applied the balances (invoices, payments and any credit memos) to the 9 customers who I had pulled the credits through the linked Transactions for.  Then I pushed all the rest of the data to the new QB File.  Everything balances to the penny, so YES, when I pulled the 2788 invoices the linked transactions that came with them are correct.  There were only 9 credit memos that applied to that sub set of invoices i was pulling. 

 I guess that was what I was after, I thought there would be a lot more Credit Memos. There weren't, i did it right.

I was just insecure about what I had pulled, but it looks like it is OK.