In this blog, I describe my progression to working with Foxpro and connecting it to Quickbooks Online.
My Background for this:
For the past several years, I have mastered using Foxpro to access Quickbooks Desktop to do various functions. See the other blogs for information on this. But Intuit has discontinued all but the executive desktop versions for future sales and grandfathered anybody with lower versions. It is clear that they are going to the online versions which are fairly good. At least much better than in the early years. So out of curiosity and speculation, I wanted to move in that direction.
There is a lot of information about this on the Intuit developers site but it didn't boil it down to what and how to actually do it from my preferred programming language Foxpro. To make things even more challenging, Foxpro 6.0. There are many other programming languages listed by Intuit but not Foxpro and much of the information is web based, not desktop application based. I located some help from a class library from ChilKat. They offer a sample program for Foxpro that doesn't quite work out of the box and they want $299 for a yearly license. Indeed I did use the 30 day trial version and was able to get it working.
I also used ChatGPT and, although helpful, it was not always correct. Many times I had to remind it that the code was not Foxpro 6.0.
I later, after getting Chilkat to work, asked ChatGPT to take that code and change it to work without Chilkat and it rewrote some of it. This was successful, again with some tweaks.
I will write this as if you know nothing except Foxpro and have a personal web site and can program in HTML.
#1. You will need to create an Intuit
Developer account. Go to this URL
https://developer.intuit.com/app/developer/homepage and create an account.
From there, you will need to create an App. It doesn't matter what you call it.
Then you will need to create a sandbox company. It is this company that you will access during testing.
Each company has an ID. It will show up below the sandbox company. This code will be called Realm ID. We will address this
later. By the way, in order to access a QBO company, your username must be a user or accountant on the company you are trying to
access. They don't let just anyone access a database.
#2. Now you are ready for to write your authorization program.
Let me explain how this works.
Foxpro will cause a browser to open which will look like the regular QBO login screen. You will pass several things to Intuit.
These include your ClientID and your Client Secret. These are provided to you in the developer site. To get them,
go to the app dashboard, click on the app. Then on the left go to Keys and Credentials, move the slider show credentials. You
will need these two. I will show you where in the program to insert them. When you have successfully logged in, Intuit will do a
GET on the webpage you will create on a personal website and pass an authorization code to it. You will store this on your web
site.
Now create a prg called something like Authorize.prg. Here is the first portion of the code. I display progress steps to the screen. I should mention about the mcallback. I will show the html I used later on. But this web page must be able to save on the your personal web server a few passed attributes which will need to be retrieved later. The callback url has to be logged with Intuit. Click on your App, Settings, Redirect URI. You will see a localhost. This isn't usable in production so enter a new one exactly the same as the mcallback below. They must be exactly the same. It has to be a URL usable on any browser. I understand that there are hosting companies that essentially rent space to do this. I have my own site (sobelsoftware.com) that I use.
The next part that deserves explanation is when running a web browser from Foxpro with a run command, we need to pass some fairly long
strings. It turns out that Foxpro has a 240 character limit on the run command itself so I needed to create a file to store the
contents. I used the low level file functions to create this. Look for the sys(2003) below.
There are also a few additional variables sent to Intuit. Scope is something that Intuit uses. There are two Scopes, #1 for
accounting companies, #2 for payment. I know nothing about that one. The second variable is State. State can be anything. In theory,
you should test to make sure what Intuit passes back has the same State value as you sent to them. I didn't do that but you need to
pass the browser this field too.
I also use a couple of programming conventions. I use "M" at the beginning of memory variables
and also LC. And I don't do projects and screens as there isn't any input a user must enter. This is a single PRG. Much
easier for me to work with. I am old school.
I will also say that when I call the authorize program, I am calling it from a small customer choice program and am passing a short customer
code like ABC for abc company so I pass a paramenter with this code. I have improved this program and my technique in that I keep a
table of authorizations which I get when I call a API. So you don't need to authorize every time you run an api. From your API call
the customer choice program, get the token, pass the token with the API
First portion of AUTHORIZE.PRG
At this point, Intuit has run the GET and sent that callback URI web page the authorization code. Now we must get that code from
your personal web server so we need another web page on that server that simply displays what we want. This routine captures a stream
produced by running that web page. the HTML page api_call.asp is that page. It will extract the fields we need from a simple DBF
behind that web site. I will provide that code later.
This is that HTML api_call.asp
%>
<body>
<%response.write "Code="&trim(mcode)
response.write "State="&trim(mstate)
response.write "Realmid="&trim(mrealmid)
%>
</body>
</html>
Second Portion of AUTHORIZE.PRG
What we know now is the Realm ID which is the customer chosen in the login screen. We also have an authorization code (mcode).
The next step in Oauth2 is to exchange this authorization code for an access code or an access token. It is this access code (Token) we need to
send to Intuit when we ask for data from the QBO bookkeeping. Below is the procedure for exchanging this authorization code for an
access code (Token) . I will note here that the stream that comes back from Intuit is in the JSON format, not XML. I suppose you
could ask for it in XML get the the results that way, but I am not sure. Asking for information might require the JSON format. I
know that when you write to a QBO database, you must do it in JSON but when you get info from them it can be in either format.
Frankly, I didn't know what JSON was before this endeavour.
Third portion of AUTHORIZE.PRG
So now we have sent them the request to exchange the authorization code for an access code. It isn't an exchange so much as a "I have the
authorization, now can I have the access code?" When Intuit responds, it will give you 3 things. Access Code, Expiration date and Refresh Code. Note, Intuit calls these tokens
not codes.
This took me a bit of figuring but here it goes. Intuit gives you an access code with which you can use to extract data from a QBO
company in a session called an API Call but this token is only good for 1 hour. After this hour has expired, you would need to
log in again. This would be really annoying so what they did is provide you a refresh token. If you passed this refresh token back
to Intuit, they would give you a new access token for another hour. The refresh token is good for 100 days so you can continue to do
this over and over for 100 days before being forced to log in again. So if you stored the refresh token somewhere and you tested the
current date time against the expiration date time, you could build an automatic refresh routine into your API calls. I have this
routine in my API calls which you will see later. But for now, we need to create a DBF to store these two tokens, the expiration date time and realm ID. So I
extract this and save it to the file. This is that routine. One more thing. After the we get the tokens, we can compute the
expiration date ourselves.
Forth Portion of the AUTHORIZE.PRG
Crazy Right?
What do you need on your personal server to accept the initial call from Intuit when the user logs in and chooses the company? A
web page. I do everything in an ASP setting with is IIS on a microsoft server. If you have a linux server, you might need to do
this in PHP. I will leave this to you.
This in an ASP program residing on the web server. I
actually use foxpro DBF's as the data file behind my web server. It uses a OBDC driver. Again, if you have sql server or
something else, you will need to figure that out. This program is effectively a listener.
When Intuit runs it, the URL would look like this.
www.yourdomain.com/auth_call.asp?code=akjdlakdakdjka&state=12345xyz&Realmid=adakldsjfakdsjfkasda
This web page would save the passed variables to a data file on your server. As you can see, I use the ADO method of writing and
reading from the data file. This is also Classic ASP.
At this point, the authorization process is complete and we have the access token and the RealmId. Next is to write an API or a program to extract what ever we want from the QBO company.
Craig Sobel