Modify an invoice with line items

[siroos12]

Hi,

 

Another issue for me 😀. I wonder if anyone can help:

Below is the script I use to add an invoice to QB, It adds the invoice if it is not already in QB with no problems.  Now after modifying that invoice in FM,I need to set the mode to MOD and push modified data to QB (Either data of invoice itself or its line items). But, My problem is that I do not have any "EditSequence" for line items. How do I capture "EditSequence" of each line item and store it so I can use them when modifying an invoice line item?

#Check if order exists on QB
Set Variable [ $$Result; Value:PCQB_RqNew( "InvoiceQuery" ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "RefNumber" ; ORDERS::OrderNumber) ]
Set Variable [ $$Result; Value:PCQB_RqExecute ]
Set Variable [ $$Status; Value:PCQB_SGetStatus ]
If [ $$Result = 0 ]
#Order exists on QB
Set Variable [ $$Result; Value:PCQB_RqNew("InvoiceMod" ; "" ) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "TxnID" ; ORDERS::_ID_QBTxnID) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "EditSequence" ; ORDERS::QBEditSequence) ]
Else
#Order does not exists on QB so, push it to QB
Set Variable [ $$Result; Value:PCQB_RqNew("InvoiceAdd" ; "") ]
End If

Set Variable [ $$Result; Value:"List ID result: " & PCQB_RqAddFieldWithValue( "CustomerRef::ListID" ; CUSTOMERS::
_ID_QBCustomerListID) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "TxnDate Result: " & PCQB_RqAddFieldWithValue( "TxnDate"; Get(CurrentDate) ) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "RefNumber Result: " & PCQB_RqAddFieldWithValue( "RefNumber" ; ORDERS::
OrderNumber ) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "PONumber Result: " & PCQB_RqAddFieldWithValue( "PONumber"; ORDERS::
PONumber ) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "DueDate Result: " & PCQB_RqAddFieldWithValue( "DueDate"; Get(CurrentDate) + 30) ]
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "SalesRepRef::FullName"; Orders_USERS||_ID_USER::Initials ) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "SalesTaxRef Result: " & PCQB_RqAddFieldWithValue( "ItemSalesTaxRef::FullName"; "
Sales Tax" )]
#Prep Line Items (Request)
Set Variable [ $orderId; Value:ORDERS::_ID_ORDER ]
Go to Layout [ “OrderItemBlank” (ORDERITEMS) ]
Enter Find Mode [ ]
Set Field [ ORDERITEMS::IDOrder; $orderId ]
Perform Find [ ]
Go to Record/Request/Page [ First ]
Loop

Set Variable [ $$Result; Value:"InvoiceLineAdd Result: " & PCQB_RqAddRelatedRecord( "InvoiceLineAdd" ; "" ) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "ItemName Result: " & PCQB_RqAddFieldWithValue( "ItemRef::FullName" ;
ORDERITEMS::Category) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "Desc Result: " & PCQB_RqAddFieldWithValue( "Desc" ; ORDERITEMS::
Description ) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "Qty Result: " & PCQB_RqAddFieldWithValue( "Quantity" ; ORDERITEMS::
Quantity ) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "Rate Result: " & PCQB_RqAddFieldWithValue( "Rate" ; ORDERITEMS::Price ) ]
#The tax code for this particular order line
Set Variable [ $$Result; Value:PCQB_RqAddFieldWithValue( "SalesTaxCodeRef::FullName" ;
Case (
not IsEmpty(ORDERITEMS::TaxCode) ; ORDERITEMS::TaxCode ;
not IsEmpty (orderItems_orders_CUSTOMERS::SalesTaxCode) ; orderItems_orders_CUSTOMERS::SalesTaxCode ;
not IsEmpty(orderItems_CATALOGS::SalesTaxCode) ;orderItems_CATALOGS::SalesTaxCode ;
"TAX" )
) ]
Set Variable [ $$Result; Value:$$Result & ¶ & "Close Result: " & PCQB_RqCloseRelatedRecord ]

Go to Record/Request/Page
[ Next; Exit after last ]
End Loop
#Execute
Set Variable [ $$Result; Value: PCQB_RqExecute ]
Set Variable [ $$Status; Value:PCQB_SGetStatus ]
 

[PCIPal]

Hi Siroos,

 

When processing the response of creating an invoice in QuickBooks, the response document has specific fields at the line item level called the "TxnLineID", or the transaction line item's identifier. Using that field in the InvoiceMod request will allow you specify "I want to update this particular line item in the Invoice to have this information within the InvoiceLineMod related record". Naturally, you'd only specify the TxnLineID for InvoiceLineMod; InvoiceLineAdd does not have that field available.

If you aren't picky about maintaining existing data between Modifications, and just want to say "Whatever I send in this invoice mod request is the end-all be-all of data", you can specify a TxnLineID of "-1" for each invoice line item that you push in the InvoiceMod request. That will tell QuickBooks to drop any other existing line items and replace them with any and all line items that you specify in the InvoiceMod request.

Generally speaking, however, when it comes to modifying invoices, sometimes it is best to leave existing invoices as they are and perform a "Void and Replace" workflow instead; void the existing invoice, and push the replacement invoice anew. But it's really up to you as a developer, working in tandem with your customer's business rules, to determine how best to handle such situations.

 

Hope this helps!

- Chris Turner

[siroos12]

Thanks Chris,

 

How and when  I can retrieve TxnLineID? Is it generated after the PCQB_RqExecute command or before it? And how do I get it for each line item? 

 

On the other hand, If I specify a TxnLineID of "-1" for each invoice line item, What happens if I have more line items on modified FM invoice than what already exists on QB?

 

Sorry if it is a silly question but this is my first time working with QB and FM Connector.

[PCIPal]

Hi Siroos,

 

You would pull the TxnLineID for each transaction lineitem record after you push your Invoice the first time, after the PCQB_RqExecute. Simply open the line item record with PCQB_RsOpenFirstRelatedRecord( "InvoiceLineRet" ), get the field value with PCQB_RsGetFirstFieldValue( "TxnLineID" ), and store it in the related record for the invoice line. Then, when you are pushing the Invoice modification, loop over the invoice items and call "PCQB_RqAddFieldWithValue( "TxnLineID" ; <<TxnLineID Field Name>> )" after each call to PCQB_RqAddRelatedRecord( "InvoiceLineMod" ).

If you have more line items in the modified invoice than what's already in QuickBooks, then pushing the modification should add those extra line items to the invoice, replacing what's already there. So if you started with 4 line items, but in the Mod request you specified 5 line items with TxnLineID's of -1, then it will update the invoice to have 5 line items.

[siroos12]

Awesome, Thanks a million.