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. Craigs@sobelsoftware.com
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:
VFP | VB |
#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) |
Dim SessionManager As New QBSessionManager
SessionManager.BeginSession "", omDontCare |
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) ENDIF NEXT 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) Else 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.IncludeLineItems.SetValue(.T.) InvoiceQuery.OwnerIDList.Add ("0") InvoiceQuery.orinvoicequery.invoicefilter.orrefnumberfilter. ; refnumberfilter.matchcriterion.setvalue(1) ** 0 - Starts With, 1 - Contains, 2 - Ends With InvoiceQuery.orinvoicequery.invoicefilter.orrefnumberfilter. ; refnumberfilter.refnumber.setvalue(minvno) responsemsgset = SessionManager.DoRequests(requestmsgset) response2 = responsemsgset.ResponseList.GetAt(0) IF ISNULL(Response2.Detail) =MESSAGEBOX("No Detail Available for invoice " + minvno) RETURN .F. ENDIF 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)) FULLNAME = "" mtxnlineid = " " FOR i = 0 TO InvoiceRet.orinvoicelineretlist.count - 1 IF NOT ISNULL(invoicelineretlist.GetAt(i).invoicelineret.itemref) APPEND BLANK mfullname=invoicelineretlist.getat(i).invoicelineret.itemref.FULLNAME.getvalue mparent=mfullname IF ":" $ mfullname mparent = SUBSTR(mfullname,1,AT(":",mfullname)-1) ENDIF REPLACE ITEM WITH mfullname,PARENT WITH mparent IF NOT ISNULL(invoicelineretlist.getat(i).invoicelineret.quantity) REPLACE qtyshp WITH invoicelineretlist.getat(i).invoicelineret.quantity.getvalue ENDIF 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 ENDIF REPLACE invno WITH minvno NEXT ENDIF ENDIF NEXT * Relinquish the communication channel with QuickBooks sessionmanager.endsession sessionmanager.closeconnection |
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) InvoiceQuery.orinvoicequery.invoicefilter.orrefnumberfilter.refnumberfilter.refnumber.setvalue(minvno) 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 Else 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) mfullname=invoicelineretlist.getat(i).invoicelineret.itemref.FULLNAME.getvalue mparent=mfullname 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 mbatchrq=invoicelineretlist.getat(i).invoicelineret.dataextretlist.getat(k).dataextvalue.getvalue END IF NEXT END IF Write #1, mfullname,mquant,,batchrq,parent END IF NEXT close #1 END IF * Relinquish the communication channel with QuickBooks sessionmanager.endsession sessionmanager.closeconnection |
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