Working With Quickbooks. QBFC
Posted: Fri Feb 12, 2021 7:12 pm
Years ago I successfully used FWH and xHarbour to build a link between my application and Quickbooks. To do this I had to use the following code to initialize QB connectivity setup:
I have a QBFC class within my program ( originally built for xHarbour ) that interfaces with the QBFC .dll in Windows installed using a program in the SDK provided by Intuit. I haven't used the interface since moving to Harbour with FWH and the Microsoft Visual Studio, but have retained it in all of my builds and have not encountered any errors.
In the first part of the code I use(d) cGetFile( ) to return the name of the .qbw file, but now Windows wants to open the file, and it can't because it must be done when the file is actually open by QuickBooks. I actually just need the selected file name which I can store. However, then when I try to initialize I get an error trying to start the connection.
SO HERE IS MY QUESTION. Is anyone else working with Quickbooks integration, and using the QBFC API ? I want to get this working again, and intend to expand the functionality.
Here is the complete QBFC Class written for QuickBooks ...
Some of this code is specific to my application, but the key here is the connectivity.
Any, and all, input will be greatly appreciated.
For those who might suggestive alternatives for accounting, my application does provide a fully integrated accounting system written with FWH over years of refinement. However, some of my clients insist on using QB because that is what their accountants want. Also, I'm looking to release a subset of my application that would incorporte QB rather than my own package.
Code: Select all
FUNCTION QBInit
LOCAL cQBFile := " "
LOCAL nHandle, oConfig, oQb, oSetupResponse
IF MsgNoYes( "Do you wish to setup the Quickbooks Link ?")
// Perform a browse for the QB Data file
cQBFile := cGetFile( "QuickBooks|*.QBW", "Select your Quick Books Company file")
IF cQBFile > " "
// Place file name in saved configuration file for future use
oConfig := tConfig():New( )
oConfig:sysus16 := cQBFile
oConfig:save( )
oConfig:close( )
// Now make sure QB is open and ready ...
MsgInfo( "Please make sure Quickbooks is started, and the company file is open on this computer" )
// Connect( cDataFile, nMode, cAppID, cAppName, nType ) nType: 1 = Local 2 = Remote (RDS)
oQB := QBFC():Connect( cQBFile, , , , 1 )
// Perform the setup
oSetupResponse := oQB:InitialSetup( )
//
MsgAlert( "System Data Initialization complete" )
ELSE
// MsgAlert( "No file was selected")
ENDIF
ENDIF
RETURN NIL
In the first part of the code I use(d) cGetFile( ) to return the name of the .qbw file, but now Windows wants to open the file, and it can't because it must be done when the file is actually open by QuickBooks. I actually just need the selected file name which I can store. However, then when I try to initialize I get an error trying to start the connection.
SO HERE IS MY QUESTION. Is anyone else working with Quickbooks integration, and using the QBFC API ? I want to get this working again, and intend to expand the functionality.
Here is the complete QBFC Class written for QuickBooks ...
Code: Select all
CLASS QBFC
CLASSDATA oSessionManager
CLASSDATA bAppErrHandler INIT ErrorBlock( {|e| QBFCErrHandler(e) } )
DATA cAppID INIT "001"
DATA cAppName INIT "ASW9"
DATA cDataFile INIT ""
DATA nMode INIT omDontCare
METHOD Connect( cDataFile, nMode, cAppId, cAppName )
METHOD Disconnect()
METHOD CustomerAdd( acFields, acValues )
METHOD CustomerFind( cFullName )
METHOD CustomerMod( cListID, acFields, axValues )
METHOD InvoiceAdd( aiFields, aiValues, aliFields, aliValues )
METHOD PaymentAdd( aiFields, aiValues)
METHOD InitialSetup( )
END CLASS
Function QBFCErrHandler( e )
LOCAL oQB
//TraceLog( e )
IF e:CanSubstitute
IF ValType( e:SubSystem ) == "C" .AND. e:SubSystem == "BASE"
IF ValType( e:SubCode ) == "N" .AND. e:SubCode == 1004
IF ValType( e:Operation ) == "C" .AND. e:Operation == "GETVALUE"
//TraceLog( "Intercepted" )
Return NIL
ENDIF
ENDIF
ENDIF
ENDIF
oQB := QBFC()
Return Eval( oQB:bAppErrHandler, e )
METHOD Connect( cDataFile, nMode, cAppID, cAppName, nType ) CLASS QBFC
IF cAppId != NIL
QSelf():cAppID := cAppID
ENDIF
IF cAppName != NIL
QSelf():cAppName := cAppName
ENDIF
IF cDataFile != NIL
QSelf():cDataFile := cDataFile
ENDIF
IF nMode != NIL
QSelf():nMode := nMode
ENDIF
IF QSelf():oSessionManager == NIL
// Create the session manager object
QSelf():oSessionManager := win_oleCreateObject( "QBFC14.QBSessionManager" )
QSelf():oSessionManager:OpenConnection2( QSelf():cAppID, QSelf():cAppName, nType )
QSelf():oSessionManager:BeginSession( QSelf():cDataFile, QSelf():nMode )
ENDIF
Return QSelf()
METHOD Disconnect() CLASS QBFC
IF QSelf():oSessionManager != NIL
QSelf():oSessionManager:EndSession()
QSelf():oSessionManager:CloseConnection()
QSelf():oSessionManager := NIL
ENDIF
Return QSelf()
METHOD CustomerAdd( acFields, axValues ) CLASS QBFC
LOCAL oRequestMsgSet, oCustomerAdd, oResponseMsgSet, oResponse, oCustomerRet
LOCAL cField, nIndex := 1
LOCAL nStart, nAt, oParent, cParent
IF QSelf():oSessionManager == NIL
QSelf():Connect()
ENDIF
// Create the message set request object (XML version 1.1)
oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 5, 0 )
// Initialize the request's attributes
oRequestMsgSet:Attributes:OnError := roeContinue
// Add the request to the message set request object
oCustomerAdd := oRequestMsgSet:AppendCustomerAddRq
FOR EACH cField IN acFields
nStart := 1
oParent := oCustomerAdd
WHILE ( nAt := At( ":", cField )) // , nStart ) ) > 0
cParent := SubStr( cField, nStart, nAt - nStart )
nStart := nAt + 1
oParent := __ObjSendMsg( oParent, cParent )
END
__ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( axValues[nIndex++] )
NEXT
// Perform the request
oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )
// The response list contains only one response, which corresponds to our single request.
oResponse := oResponseMsgSet:ResponseList:GetAt(0)
IF oResponse:StatusCode != 0
MessageBox( , "Add Customer failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
Return NIL
ENDIF
//The response detail for Add is an ICustomerRet.
oCustomerRet := oResponse:Detail
Return oCustomerRet
METHOD CustomerFind( cFullName ) CLASS QBFC
LOCAL oRequestMsgSet, oCustomerFind, oResponseMsgSet, oResponse, oCustomerRet
LOCAL cField, nIndex := 1
IF QSelf():oSessionManager == NIL
QSelf():Connect()
ENDIF
// Create the message set request object (XML version 1.1 )
oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 5,0 )
// Initialize the request's attributes
oRequestMsgSet:Attributes:OnError := roeContinue
// Add the request to the message set request object
oCustomerFind := oRequestMsgSet:AppendCustomerQueryRq
oCustomerFind:ORCustomerListQuery:FullNameList:Add( cFullName )
// Perform the request
oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )
// The response list contains only one response, which corresponds to our single request.
oResponse := oResponseMsgSet:ResponseList:GetAt(0)
IF oResponse:StatusCode != 0
// MessageBox( ,"Find Customer failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
Return NIL
ENDIF
//First and only ICustomerRet.
oCustomerRet := oResponse:Detail:GetAt(0)
Return oCustomerRet
METHOD CustomerMod( cListID, nEditSequence, acFields, axValues ) CLASS QBFC
LOCAL oRequestMsgSet, oCustomerMod, oResponseMsgSet, oResponse, oCustomerRet
LOCAL cField, nIndex := 1
LOCAL nStart, nAt, oParent, cParent
IF QSelf():oSessionManager == NIL
QSelf():Connect()
ENDIF
// Create the message set request object (XML version 1.1)
oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 5, 0 )
// Initialize the request's attributes
oRequestMsgSet:Attributes:OnError := roeContinue
// Add the request to the message set request object
oCustomerMod := oRequestMsgSet:AppendCustomerModRq
oCustomerMod:ListID:SetValue( cListID )
oCustomerMod:EditSequence:SetValue( nEditSequence )
FOR EACH cField IN acFields
nStart := 1
oParent := oCustomerMod
WHILE ( nAt := At( ":", cField )) //, nStart ) ) > 0
cParent := SubStr( cField, nStart, nAt - nStart )
nStart := nAt + 1
oParent := __ObjSendMsg( oParent, cParent )
END
__ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( axValues[nIndex++] )
NEXT
// Perform the request
oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )
// The response list contains only one response, which corresponds to our single request.
oResponse := oResponseMsgSet:ResponseList:GetAt(0)
IF oResponse:StatusCode != 0
MessageBox( ,"Modify Customer failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
Return NIL
ENDIF
//The response detail for Mod requests is ICustomerRet.
oCustomerRet := oResponse:Detail
Return oCustomerRet
METHOD InvoiceAdd( acFields, axValues, aliFields, aliValues ) CLASS QBFC
LOCAL oRequestMsgSet, oInvoiceAdd, oResponseMsgSet, oResponse, oInvoiceRet
LOCAL cField, nIndex := 1, n2Index := 1
LOCAL nStart, nAt, oParent, cParent, ax, ;
aLisFields, aLisValues, oItemAdd
IF QSelf():oSessionManager == NIL
QSelf():Connect()
ENDIF
// Create the message set request object (XML version 1.1)
oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 5, 0 )
// Initialize the request's attributes
oRequestMsgSet:Attributes:OnError := roeContinue
// Add the request to the message set request object
oInvoiceAdd := oRequestMsgSet:AppendInvoiceAddRq
FOR EACH cField IN acFields
nStart := 1
oParent := oInvoiceAdd
WHILE ( nAt := At( ":", cField ) ) //, nStart ) ) > 0
cParent := SubStr( cField, nStart, nAt - nStart )
nStart := nAt + 1
oParent := __ObjSendMsg( oParent, cParent )
END
__ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( axValues[nIndex++] )
NEXT
// We need to loop for each of the items we are adding. Lets do an array inside an array
FOR ax := 1 TO LEN( aliFields )
// First parse the sub array
alisFields := aliFields[ax]
alisValues := aliValues[ax]
n2Index := 1
// Add the request to the message set request object
oItemAdd := oInvoiceAdd:ORInvoiceLineAddList:Append:InvoiceLineAdd
FOR EACH cField IN alisFields
nStart := 1
oParent := oItemAdd
WHILE ( nAt := At( ":", cField )) //, nStart ) ) > 0
cParent := SubStr( cField, nStart, nAt - nStart )
nStart := nAt + 1
oParent := __ObjSendMsg( oParent, cParent )
END
__ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( alisValues[n2Index++] )
NEXT
NEXT
// Perform the request
oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )
// The response list contains only one response, which corresponds to our single request.
oResponse := oResponseMsgSet:ResponseList:GetAt(0)
IF oResponse:StatusCode != 0
MessageBox(, "Add Invoice failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
Return NIL
ENDIF
//The response detail for Add is an ICustomerRet.
oInvoiceRet := oResponse:Detail
Return oInvoiceRet
METHOD PaymentAdd( acFields, axValues, cInvId, nPayTot ) CLASS QBFC
LOCAL oRequestMsgSet, oPaymentAdd, oResponseMsgSet, oResponse, oPaymentRet
LOCAL cField, nIndex := 1
LOCAL nStart, nAt, oParent, cParent, appliedToTxnAdd2, oLedgerAdd, ;
oTaxAdd, oSvcAdd,oItemAdd
IF QSelf():oSessionManager == NIL
QSelf():Connect()
ENDIF
// Create the message set request object (XML version 1.1)
oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 5, 0 )
// Initialize the request's attributes
oRequestMsgSet:Attributes:OnError := roeContinue
// Add the request to the message set request object
oPaymentAdd := oRequestMsgSet:AppendReceivePaymentAddRq
FOR EACH cField IN acFields
nStart := 1
oParent := oPaymentAdd
WHILE ( nAt := At( ":", cField )) //, nStart ) ) > 0
cParent := SubStr( cField, nStart, nAt - nStart )
nStart := nAt + 1
oParent := __ObjSendMsg( oParent, cParent )
END
__ObjSendMsg( oParent, SubStr( cField, nStart ) ):SetValue( axValues[nIndex++] )
NEXT
// Lets apply the payment
appliedToTxnAdd2 = oPaymentAdd:ORApplyPayment:AppliedToTxnAddList:Append
appliedToTxnAdd2:TxnID:SetValue( cInvID )
appliedToTxnAdd2:PaymentAmount:SetValue( nPayTot )
// Perform the request
oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )
// The response list contains only one response, which corresponds to our single request.
oResponse := oResponseMsgSet:ResponseList:GetAt(0)
IF oResponse:StatusCode != 0
MessageBox( ,"Add Payment failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
Return NIL
ENDIF
//The response detail for Add is an ICustomerRet.
oPaymentRet := oResponse:Detail
Return oPaymentRet
// Setup verifies or adds Ledger Accounts, Tax Codes, non-inventory items, service items
METHOD InitialSetup( ) CLASS QBFC
LOCAL oRequestMsgSet, oResponseMsgSet, oResponse, oSetupRet, oPayMethodAdd, ;
oTaxAdd, oSvcAdd, oItemAdd, oLedgerAdd
IF QSelf():oSessionManager == NIL
QSelf():Connect()
ENDIF
// Create the message set request object (XML version 1.1)
oRequestMsgSet := QSelf():oSessionManager:CreateMsgSetRequest( "US", 5, 0 )
// Initialize the request's attributes
oRequestMsgSet:Attributes:OnError := roeContinue
// LEDGER
oLedgerAdd := oRequestMsgSet:AppendAccountAddRq
oLedgerAdd:Name:SetValue( "Sales" )
oLedgerAdd:AccountType:SetValue( 8 )
oLedgerAdd := oRequestMsgSet:AppendAccountAddRq
oLedgerAdd:Name:SetValue( "Services" )
oLedgerAdd:AccountType:SetValue( 8 )
oLedgerAdd := oRequestMsgSet:AppendAccountAddRq
oLedgerAdd:Name:SetValue( "Parts" )
oLedgerAdd:AccountType:SetValue( 8 )
oLedgerAdd:ParentRef:FullName:SetValue( "Sales" )
oLedgerAdd := oRequestMsgSet:AppendAccountAddRq
oLedgerAdd:Name:SetValue( "Labor" )
oLedgerAdd:AccountType:SetValue( 8 )
oLedgerAdd:ParentRef:FullName:SetValue( "Sales" )
oLedgerAdd := oRequestMsgSet:AppendAccountAddRq
oLedgerAdd:Name:SetValue( "Sublet" )
oLedgerAdd:AccountType:SetValue( 8 )
oLedgerAdd:ParentRef:FullName:SetValue( "Sales" )
// PARTS SALES
// Add the request to the message set request object
oItemAdd := oRequestMsgSet:AppendItemNonInventoryAddRq
// Add non-inventory items list
oItemAdd:Name:SetValue( "Parts" )
oItemAdd:SalesTaxCodeRef:FullName:SetValue( "TAX" )
oItemAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Parts" )
oItemAdd := oRequestMsgSet:AppendItemNonInventoryAddRq
oItemAdd:Name:SetValue( "Parts NT" )
oItemAdd:SalesTaxCodeRef:FullName:SetValue( "NON" )
oItemAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Parts" )
oItemAdd := oRequestMsgSet:AppendItemNonInventoryAddRq
oItemAdd:Name:SetValue( "Shop Supplies" )
oItemAdd:SalesTaxCodeRef:FullName:SetValue( "TAX" )
oItemAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Parts" )
oItemAdd := oRequestMsgSet:AppendItemNonInventoryAddRq
oItemAdd:Name:SetValue( "Shop Supplies NT" )
oItemAdd:SalesTaxCodeRef:FullName:SetValue( "NON" )
oItemAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Parts" )
// LABOR
oSvcAdd := oRequestMsgSet:AppendItemServiceAddRq
oSvcAdd:Name:SetValue( "Labor" )
oSvcAdd:SalesTaxCodeRef:FullName:SetValue( "TAX" )
oSvcAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Labor" )
oSvcAdd := oRequestMsgSet:AppendItemServiceAddRq
oSvcAdd:Name:SetValue( "Labor NT" )
oSvcAdd:SalesTaxCodeRef:FullName:SetValue( "NON" )
oSvcAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Labor" )
oSvcAdd := oRequestMsgSet:AppendItemServiceAddRq
oSvcAdd:Name:SetValue( "Sublet" )
oSvcAdd:SalesTaxCodeRef:FullName:SetValue( "TAX" )
oSvcAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Sublet" )
oSvcAdd := oRequestMsgSet:AppendItemServiceAddRq
oSvcAdd:Name:SetValue( "Sublet NT" )
oSvcAdd:SalesTaxCodeRef:FullName:SetValue( "NON" )
oSvcAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Sales:Sublet" )
oSvcAdd := oRequestMsgSet:AppendItemServiceAddRq
oSvcAdd:Name:SetValue( "HW Disposal" )
oSvcAdd:SalesTaxCodeRef:FullName:SetValue( "NON" )
oSvcAdd:ORSalesPurchase:SalesOrPurchase:AccountRef:FullName:SetValue( "Services" )
oTaxAdd := oRequestMsgSet:AppendItemSalesTaxAddRq
oTaxAdd:Name:SetValue( "FET" )
oTaxAdd := oRequestMsgSet:AppendItemSalesTaxAddRq
oTaxAdd:Name:SetValue( "Exempt" )
oTaxAdd := oRequestMsgSet:AppendItemSalesTaxAddRq
oTaxAdd:Name:SetValue( "Local Tax" )
// Add a payment method for Credit Cards as Other Credit Card
oPayMethodAdd := oRequestMsgSet:AppendPaymentMethodAddRq
oPayMethodAdd:Name:SetValue( "Credit Card" )
// Perform the request
oResponseMsgSet := QSelf():oSessionManager:DoRequests( oRequestMsgSet )
// The response list contains only one response, which corresponds to our single request.
oResponse := oResponseMsgSet:ResponseList:GetAt(0)
IF oResponse:StatusCode != 0
MessageBox(, "Add List Items failed!;Status: Code = " + CStr( oResponse:StatusCode ) + ", Severity = " + oResponse:StatusSeverity + ", Message = " + oResponse:statusMessage )
Return NIL
ENDIF
//The response detail for Add is an ICustomerRet.
oSetupRet := oResponse:Detail
Return oSetupRet
EXIT Procedure QBDisconnect()
LOCAL oQB := QBFC()
oQB:Disconnect()
ReturnSome of this code is specific to my application, but the key here is the connectivity.
Any, and all, input will be greatly appreciated.
For those who might suggestive alternatives for accounting, my application does provide a fully integrated accounting system written with FWH over years of refinement. However, some of my clients insist on using QB because that is what their accountants want. Also, I'm looking to release a subset of my application that would incorporte QB rather than my own package.