In this blog, I describe my progression to working with Foxpro and connecting it to Quickbooks.

My Background for this:

20 years of programming in Basic, FoxPro, HTML, ASP and one previous program connecting data in QuickBooks.  No previous experience in an object oriented programming techniques.  I have previously worked for two different companies using the older SBT and Accpac VisionPoint software and had created the exact same scanning functionality. 

For this project, my programming choice is to use Foxpro to connect to Quickbooks because Foxpro is my most comfortable language and it also provides me access to the desktop screen and to the incredible DBF database access tools.  Let me say that for a VB programmer, access to the screen is all through a form.  For me this is difficult.  I have not lived there.  Accessing a screen in Foxpro is really easy for me.  Using a Browse to look at data and manipulate it is really easy,  I have no idea how to do something similar in VB.  For a Visual Foxpro programmer who lives in screens, things are probably very similar but I work VFP mostly in the non-visual way.

Resources I found:

First you must download and install the QuickBooks SDK (I used Version 13)  This includes the OSR, On Screen Reference, The SDK Programmers Guide and numerous example files.  There was also a video produced that described how to set up Visual Basic.  For Visual Foxpro, no similar support exists so I will describe how.

Purpose for writing this:

After getting used to using VB to do the access, I wrote several programs to do specific functions.  None had any real screen input by the user.  I did have an application I wrote in VFP to scan items against a QB invoice, capture a batch number and save it to the QB invoice.  In my first attempt, I wrote the get invoice detail in VB and write it back to QB in VB but the core of the software was VFP.  This worked but was pretty cumbersome.  It kind of worked like this.  Run VFP,  Enter the invoice number,  Run getinv.exe.  Put VFP into a holding pattern until the getinv.exe completed its task (I had to have getinv.exe create a text file with the result before allowing VFP to continue),  Do some scanning in VFP,  when complete, run postbatch.exe, again wait for this to complete.  This worked but was very messy.  Instead, rewrite the two Exe's in VFP, no passing data back and forth.

I am still using QBFC to create the XML that does the requests.

I have elected to show the VFP in one screen and the VB in another side by side to show the differences in the languages and different techniques.  If you don't really care about the differences, just look at the VFP side.

To setup VFP to work on Quickbooks:
First, I wanted to install the intellisense file for Quickbooks.  I do not know how to do this in VFP6 but in VFP8, (Possibly 7 but I don't have this version), open Foxpro, Tools, Intellisense Manager, Types, Type Library
Look for a file QBFC13 1.0 Type Library. if you don't see it, there is a reload button.  If you still don't find it, make sure QBFC13.dll is in the windows system32 folder.  It should have installed when you installed the SDK.

Here is the difference.  In VB,  Intellisense works in the editor.  In VFP, it works in the command box and the trace window, Watch but not in PRG's.  If you figure out how to make it work, let me know.

The next thing you will need is a file called qbfc.h  This contains a long list of variables with values in them.  It is specific to the version.  If you are accessing an old version of QB, you might need to use older versions of this file.   Doug Hennig said it well, the way to create the qbfc.h is to go to Tools, Object Browser.  Open, COM Librarys, Find the qbrc13 1.0 type library.  If you don't find it, reload, Open the item,  back in the Classes & Members screen, you will see the library, Open the +,  in the command box type modify command qbfc.h
Now drag the Constants word into the program file.  When you release the mouse, it will populate the file.

It will look like this:

#DEFINE omSingleUser 0
#DEFINE omMultiUser 1
#DEFINE omDontCare 2
#DEFINE roeStop 0
#DEFINE roeContinue 1
#DEFINE roeRollback 2

Now you are all set.  Save the qbfc.h file in the program directory,  You will need to call it from your program.

So here we go:

#include qbfc.h
lcqbsdkver="QBFC13.QBSessionManager" && change to the SDK version you are using
SessionManager = CREATEOBJECT(lcqbsdkver)

SessionManager.OpenConnection2("","Name of Program", ctLocalQBD)
*Take a look down the qbfc.h file and see if you can find the ctlocalqbd.  Now you see what that file does for you 

SessionManager.BeginSession("", omdontcare)

supportedversion = qbfclatestversion(sessionmanager)
*Rem request #1 to determine last close date
msgreq1 = sessionmanager.createmsgsetrequest("US", VAL(supportedversion), 0)


Dim SessionManager As New QBSessionManager
SessionManager.OpenConnection2 "", "Name of Program", ctLocalQBD



SessionManager.BeginSession "", omDontCare
Dim msgReq1 As IMsgSetRequest
Set msgReq1 = GetLatestMsgSetRequest(SessionManager)

As you can see, they are fairly similar.  You might notice that VB calls a function GetLatestMsgSetRequest.
VB actually opens the messageset in the function but names it here.  I can't do that in VFP so I had to change it.  Below is the two functions.  You may need to tweek it if you are using an obscure version of QB.  My project works on QB14.

FUNCTION qbfclatestversion
PARAMETER strxmlversions
strxmlversions = sessionmanager.qbxmlversionsforsession
lastvers = 0
FOR i = 1 TO ALEN(strxmlversions)
  VERS = VAL(strxmlversions(i))
  IF (VERS > lastvers)
    lastvers = VERS
    qbfclatestversion = strxmlversions(i)
RETURN qbfclatestversion
Public Function GetLatestMsgSetRequest(SessionManager As QBSessionManager) As IMsgSetRequest
Dim supportedVersion As String
supportedVersion = QBFCLatestVersion(SessionManager)
If (supportedVersion >= "13.0") Then
  Set GetLatestMsgSetRequest  = SessionManager.CreateMsgSetRequest("US", 13, 0)
ElseIf (supportedVersion >= "12.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 12, 0)
ElseIf (supportedVersion >= "11.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 11, 0)
ElseIf (supportedVersion >= "10.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 10, 0)
ElseIf (supportedVersion >= "9.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 9, 0)
ElseIf (supportedVersion >= "8.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 8, 0)
ElseIf (supportedVersion >= "7.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 7, 0)
ElseIf (supportedVersion >= "6.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 6, 0)
ElseIf (supportedVersion >= "5.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 5, 0)
ElseIf (supportedVersion >= "4.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 4, 0)
ElseIf (supportedVersion >= "3.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 3, 0)
ElseIf (supportedVersion >= "2.0") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 2, 0)
ElseIf (supportedVersion = "1.1") Then
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 1, 1)
  MsgBox "You are apparently running QuickBooks 2002 Release 1, we strongly recommend
  that you use QuickBooks' online update feature to obtain the latest fixes and enhancements", vbExclamation
  Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 1, 0)
End If
End Function

Function QBFCLatestVersion(SessionManager As QBSessionManager) As String
Dim strXMLVersions() As String
strXMLVersions = SessionManager.QBXMLVersionsForSession

Dim i As Long
Dim vers As Double
Dim LastVers As Double
LastVers = 0
For i = LBound(strXMLVersions) To UBound(strXMLVersions)
  vers = Val(strXMLVersions(i))
  If (vers > LastVers) Then
    LastVers = vers
    QBFCLatestVersion = strXMLVersions(i)
  End If
Next i
End Function

This will get you to open a connection and setting your first request.  I will now go through the rest of the get invoice program to highlight differences in syntax between the two

requestmsgset.ATTRIBUTES.onerror = roecontinue
* construct the request to get the one invoice, its line items and custom fields

InvoiceQuery = requestmsgset.AppendInvoiceQueryRq
InvoiceQuery.OwnerIDList.Add ("0")

InvoiceQuery.orinvoicequery.invoicefilter.orrefnumberfilter. ;
** 0 - Starts With, 1 - Contains, 2 - Ends With
InvoiceQuery.orinvoicequery.invoicefilter.orrefnumberfilter. ;
responsemsgset = SessionManager.DoRequests(requestmsgset)
response2 = responsemsgset.ResponseList.GetAt(0)
IF ISNULL(Response2.Detail)
  =MESSAGEBOX("No Detail Available for invoice " + minvno)
InvoiceRetList = response2.Detail
InvoiceRet = InvoiceRetList.GetAt(0)
transid = InvoiceRet.txnid.getvalue
editseq = InvoiceRet.editsequence.getvalue
invoicelineretlist = invoiceret.orinvoicelineretlist
CREATE TABLE (mfile) (ITEM c(40), qtyshp N(12,2),batchrq c(1),PARENT c(40),scanned N(12,2),invno c(8),BATCH c(10),scancnt N(12,2),scanitem c(15))
mtxnlineid = " "
FOR i = 0 TO InvoiceRet.orinvoicelineretlist.count - 1
  IF NOT ISNULL(invoicelineretlist.GetAt(i).invoicelineret.itemref)
    IF ":" $ mfullname
      mparent = SUBSTR(mfullname,1,AT(":",mfullname)-1)
    REPLACE ITEM WITH mfullname,PARENT WITH mparent
    IF NOT ISNULL(invoicelineretlist.getat(i).invoicelineret.quantity)
      REPLACE qtyshp WITH invoicelineretlist.getat(i).invoicelineret.quantity.getvalue
    IF NOT ISNULL(invoicelineretlist.getat(i).invoicelineret.dataextretlist)
      FOR k = 0 TO invoicelineretlist.getat(i).invoicelineret.dataextretlist.count - 1
        IF invoicelineretlist.getat(i).invoicelineret.dataextretlist.getat(k).dataextname.getvalue="Batch #"
          REPLACE batchrq WITH invoicelineretlist.getat(i).invoicelineret.dataextretlist.getat(k).dataextvalue.getvalue
        REPLACE invno WITH minvno

* Relinquish the communication channel with QuickBooks
requestmsgset.Attributes.OnError = ENRqOnError.roeContinue

Dim InvoiceQuery As IInvoiceQuery
Set InvoiceQuery = requestmsgset.AppendInvoiceQueryRq
InvoiceQuery.IncludeLineItems.SetValue True
InvoiceQuery.OwnerIDList.Add ("0")

InvoiceQuery.orinvoicequery.invoicefilter.orrefnumberfilter. ;
refnumberfilter.matchcriterion.setvalue (CONTAINS)
Dim responsemsgset As IMsgSetResponse
Set responsemsgset = SessionManager.DoRequests(requestmsgset)
Dim Response2 As IResponse
Set Response2 = responsemsgset.ResponseList.GetAt(0)
If Response2.Detail Is Nothing Then
  MsgBox "No Detail Available for this invoice" & minvno
  GoTo unsucc
  Dim InvoiceRetList As IInvoiceRetList
  Set InvoiceRetList = Response2.Detail
  transid - invoiceret.txnid.getvalue
  editseq = invoiceret.editsequence.getvalue
  Open minvno & ".txt" For Output As #1
  For i = 0 to invoiceret.orinvoicelineretlist.count-1
    IF NOT (invoicelineretlist.getat(i).invoicelineret.itemref is nothing)
      IF ":" $ mfullname
        mparent = SUBSTR(mfullname,1,AT(":",mfullname)-1)
      END IF
      IF NOT (invoicelineretlist.getat(i).invoicelineret.quantity is nothing) then
        mquant = invoicelineretlist.getat(i).invoicelineret.quantity.getvalue
      END IF
      IF (invoicelineretlist.getat(i).invoicelineret.dataextretlist is nothing) then
        FOR k = 0 TO invoicelineretlist.getat(i).invoicelineret.dataextretlist.COUNT - 1
          IF invoicelineretlist.getat(i).invoicelineret.dataextretlist.getat(k).dataextname.getvalue="Batch #" then
          END IF
      END IF
      Write #1,  mfullname,mquant,,batchrq,parent
    END IF
  close #1
* Relinquish the communication channel with QuickBooks

Several differences.  In VFP there is no need to define variables.  So unless it is an array, you can skip that.
Syntax differences, If statements must have a "Then" in VB,  Not VFP,  Endif, VFP,  End If in VB.
Nulls are different in VB it is "Is Nothing". In VFP, use the ISNULL function
The filter criterion in VB "Contains", in VFP, it needs a number.
In VFP, there cannot be a space after the GETAT(0) in VB,  GETAT (0) is OK
Array handling is different.  Although I did not use any array stuff here, some major differences become apparent
In VB, Array assignments start at Zero,  in VFP, they start at 1.  Even when you request data, the first record is GETAT(0)  But in VFP you would place it in mvar(1).  The next really big difference is when you redimension an array.
In VB, it is redim preserve marray(5,newvalue),  in VFP it is simply dimension marray(5,newvalue) except when you add rows to VB, you do not increase the first dimension so you must do it to the second.  In VFP, you do it to the first so the whole array structure is opposite.  marray(newvalue,5).

There are several other differences I noticed between VFP and VB.  VFP handles DBF's  Indexing, Sorting, replacing, browsing.  All that stuff is great.  With VB, you have to write data to a file in an old arcane way:
  Open "temp.txt" For Output As #1
  Write #1,  sku, batch, quant
  Close #1
Once written, you can't do anything with it other than read it.  No sorting, etc.  Not so with VFP
On the downside to VFP,  Intellisense doesn't seem to work in the PRG.  All of the existing support in the sdk manual is in VB.  While you are developing, you can trace through the program as it runs and actually make changes to it without restarting.  In the watch window in VB, you can drill down to the data.  In VFP, it doesn't really work,  It just says Object.  And in VB the code formats better because of intellisense.

In conclusion, if you need database operations or VFP is more confortable, go for it.  If VB is comfortable, use it.

Craig Sobel