Author Topic: Invoice Query failure  (Read 1567 times)

mikekurm@wienerminder.com

  • Member
  • *
  • Posts: 13
Invoice Query failure
« on: July 21, 2012, 09:53:41 AM »
The below script keeps failing at

Set Variable [ $Result; Value:PCQB_RsOpenFirstRelatedRecord ( "InvoiceLineRet" ) ]

Productive Computing was contracted to design this for our company a year ago. I have a call into them. Did something change with formating.

QuickBooks: InvoicePull
Allow User Abort [ Off ]
Set Error Capture [ Off ]
Commit Records/Requests
[ No dialog ]
If [ Get ( LastError ) ]
Exit Script [ ]
End If
Set Error Capture [ On ]
If [ PCQB_Version( "Short" ) = "?" ]
Show Custom Dialog [ Title: "Warning"; Message: "FM Books Connector not installed on this machine."; Buttons: “OK” ]
Exit Script [ ]
End If
Show Custom Dialog [ Title: "Warning"; Message: "All invoices will be imported from QuickBooks. This could take awhile. Continue?"; Buttons:
“Cancel”, “OK” ]
If [ Get ( LastMessageChoice ) = 1 //Cancel ]
Exit Script [ ]
End If
#Begin Session
Set Variable [ $Result; Value:PCQB_EndSession ]
If [ PCQB_BeginSession ( "" ; "" ; "Y" ) ≠ 0 ]
Set Variable [ $Error; Value:PCQB_SGetStatus ]
Set Variable [ $Result; Value:PCQB_EndSession ]
Show Custom Dialog [ Message: "Unable to begin a session with QuickBooks." & ¶ & $Error; Buttons: “OK” ]
Exit Script [ ]
End If
Freeze Window
Set Variable [ $Result; Value:PCQB_RqNew ( "InvoiceQuery" ) ]
If [ PCQB_RqExecute ≠ 0 ]
Set Variable [ $Error; Value:$Error & PCQB_SGetStatus & ¶ & ¶ ]
Else
Set Variable [ $Result; Value:PCQB_RsOpenFirstRecord ]
Loop
Go to Layout [ original layout ]
Exit Loop If [ $Result ≠ 0 ]
Set Variable [ $Invoice_ID_QuickBooks; Value:PCQB_RsGetFirstFieldValue ( "TxnID" ) ]
Set Variable [ $Company_Name; Value:PCQB_RsGetFirstFieldValue ( "CustomerRef::FullName" ) ]
Set Variable [ $AcctDate; Value:PCQB_RsGetFirstFieldValue ( "TxnDate" ) ]
Set Variable [ $AcctID; Value:PCQB_RsGetFirstFieldValue ( "RefNumber" ) ]
Set Variable [ $Person_Name; Value:PCQB_RsGetFirstFieldValue ( "BillAddress::Addr2" ) ]
// Set Variable [ $Person_Name; Value:PCQB_RsGetFirstFieldValue ( "ShipAddress::Addr2" ) ]
##find company/vendor
Go to Layout [ “ContID1” (ContID1) ]
Enter Find Mode [ ]
Set Field [ ContID1::CompanyName_QB; "==" & $Company_Name ]
Perform Find [ ]
If [ Get ( LastError ) ]
July 21, 2012 12:23:04 ACCTING_ - InvoicePull -1-
QuickBooks: InvoicePull
Set Variable [ $Error; Value:$Error & "Vendor \"" & $Company_Name & "\" not found, record skipped." & ¶ & ¶ ]
Else
Set Variable [ $Company_ID; Value:ContID1::_company_ID ]
##find individual
Go to Layout [ “ContPersID1” (ContPersID1) ]
Enter Find Mode [ ]
Set Field [ ContPersID1::ConNameFirst_LastQB; "=" & LeftWords($Person_Name;2) ]
Set Field [ ContPersID1::_company_ID; "==" & $Company_ID ]
Perform Find [ ]
If [ Get ( LastError ) ]
Set Variable [ $Error; Value:$Error & "Contact \"" & $Person_Name & "\" for company \"" & $Company_Name & "\" not
found, record skipped." & ¶ & ¶ ]
Else
Set Variable [ $Person_ID; Value:ContPersID1::_CP_ID1 ]
Go to Layout [ original layout ]
##find/create PO
Enter Find Mode [ ]
Set Field [ ACCTING_::__QB_TxnID; "==" & $Invoice_ID_QuickBooks ]
Perform Find [ ]
If [ Get ( LastError ) ]
##NEW Trans
New Record/Request
##NEW Trans
Set Field [ ACCTING_::__QB_TxnID; $Invoice_ID_QuickBooks ]
End If
##PO fields
Set Field [ ACCTING_::ActType; "Invoice" ]
Set Field [ ACCTING_::ContID2; $Company_ID ]
Set Field [ ACCTING_::CP_ID2; $Person_ID ]
Set Field [ ACCTING_::AcctDate; $AcctDate ]
Set Field [ ACCTING_::AcctID; $AcctID ]
##line items
#clear any existing
Go to Related Record [ From table: “p1_Items”; Using layout: “p1_Items” (p1_Items) ]
[ Show only related records ]
If [ not Get ( LastError ) ]
Delete All Records
[ No dialog ]
Go to Layout [ original layout ]
End If
Set Variable [ $Result; Value:PCQB_RsOpenFirstRelatedRecord ( "InvoiceLineRet" ) ]
If [ $Result = 0 ]
Go to Layout [ “p1_Items” (p1_Items) ]
Loop
Exit Loop If [ $Result ≠ 0 ]
##NEW Item
New Record/Request
July 21, 2012 12:23:04 ACCTING_ - InvoicePull -2-
QuickBooks: InvoicePull
##NEW Item
Set Field [ p1_Items::__prt_1ProdID_; PCQB_RsGetFirstFieldValue ( "ItemRef::FullName" ) ]
Set Field [ p1_Items::__prt_3DescMain; PCQB_RsGetFirstFieldValue ( "Desc" ) ]
Set Field [ p1_Items::Qty_FORCALCS; PCQB_RsGetFirstFieldValue ( "Quantity" ) ]
Set Field [ p1_Items::__prt_2Qty_Required; PCQB_RsGetFirstFieldValue ( "Quantity" ) ]
Set Field [ p1_Items::__prt_2Qty_Ship_Recd; PCQB_RsGetFirstFieldValue ( "Quantity" ) ]
Set Field [ p1_Items::P2_3_SellEa; PCQB_RsGetFirstFieldValue ( "Rate" ) ]
Set Variable [ $Result; Value:PCQB_RsOpenNextRelatedRecord ]
End Loop
Set Variable [ $Result; Value:PCQB_RsCloseRelatedRecord ]
End If
End If
End If
Set Variable [ $Result; Value:PCQB_RsOpenNextRecord ]
End Loop
End If
If [ not IsEmpty ( $Error ) ]
Show Custom Dialog [ Title: "Error(s)"; Message: $Error; Buttons: “OK” ]
End If
July 21, 2012 12:23:04 ACCTING_ - InvoicePull -3-

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Invoice Query failure
« Reply #1 on: July 21, 2012, 02:07:38 PM »
There are no InvoiceLineRet elements unless you add this...

PCQB_RqAddFieldWithValue( "IncludeLineItems" ; "true" )

...after...

Set Variable [ $Result; Value:PCQB_RqNew ( "InvoiceQuery" ) ]

...and before...

If [ PCQB_RqExecute ≠ 0 ]

HTH!


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