Page 1 of 1

Working With Quickbooks. QBFC

Posted: Fri Feb 12, 2021 7:12 pm
by TimStone
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:

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
 
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 ...

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()
Return

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.

Re: Working With Quickbooks. QBFC

Posted: Fri Feb 12, 2021 8:29 pm
by Enrico Maria Giordano
TimStone wrote:

Code: Select all

cQBFile := cGetFile( "QuickBooks|*.QBW", "Select your Quick Books Company file")
Try this:

Code: Select all

cQBFile := cGetFile( "QuickBooks|*.QBW", "Select your Quick Books Company file", , , .T.)
EMG

Re: Working With Quickbooks. QBFC

Posted: Fri Feb 12, 2021 8:36 pm
by Enrico Maria Giordano
Or even better:

Code: Select all

#define OFN_NOTESTFILECREATE 0x00010000
cQBFile := cGetFile( "QuickBooks|*.QBW", "Select your Quick Books Company file", , , , , OFN_NOTESTFILECREATE )
EMG

Re: Working With Quickbooks. QBFC

Posted: Fri Feb 12, 2021 11:38 pm
by TimStone
Thank you ... I will work with that.

I found an interesting problem when I hardcoded the path into the program for testing. This may prove helpful to others who read this thread.

When Quickbooks sets up a company, it puts it by default in the PUBLIC\Public Documents\Intuit\Quickbooks\ Company Files folder. My application kept saying it could not open the file.

I finally created a C:\QBW folder and shared it to everyone.
I then created a new QB company file and saved it ( manually ) to that folder.
Then my code worked again to establish the proper authorization and acces to QB.

I have more to work on, but that will likely workout with some close looks.