This is the second page to this blog, I describe the next step after we have received the access code and realm ID, Now we want to do something with the company data.

We are going to write 3 programs here.  The first program will construct what we want. In this example, we will extract all of the invoices and credit memos for a single year and save that data to a DBF.  To do this, we will need to construct an SQL statement, a generic program to check on the expiration of the access code and to refresh it if needed, then once we have the extraction, to parse the results into a DBF table.  What you do with this table depends on what the project's goal is.  This first project was designed to extract the invoices, add the city shipped so that a business license filing could be done.  It turns out that QBO cannot run this report.  The ship-to column is not available in the report writer for the report we want.  This is just an example of doing something that QBO cannot do natively.

Lets get started.  We will call our program getinvoices.prg.
As you can see, I am writing this for me to use so it isn't very pretty.  I want to filter the results for a single year.  Let me digress,  Foxpro 6 DBF's have field names that are max 10 characters so using the QBO field names won't due, they are too long so we need to create a translation of QBO field to our field name.  I have constructed a table for this translation.  As I create additional queries, I will need to expand this table to other files to be extracted such as the customer file.  So you might be asking, where can I get a list of the fields for the Invoice data file.  Going back to the developer site, look at the top for API Explorer, Accounting (These are the two scopes)  If you choose Accounting, you will see a list of files on the left side, go down to Invoice, Invoice Object.  I prefer to see this in XML so switch the JSON to XML. In the window, you will see all of the fields and the sub fields.  Here is a structure
Create table fieldlist (Entity c(15), Fname c(50),Colname c(10),Type c(15),Maxlen c(10)) 
For the Entity, Fill in "Invoice". for Fname, this is the name of the field on the API.  Colname is up to 10 character field name in the resulting DBF.
Type:  String, Number or Boolean.  Numbers are assumed to be (12,2) you don't need to fill this in, Boolean will create a logical field (L), String requires some explanation.  Foxpro allows up to 254 characters per field but quickbooks allows much more in some fields.  Perhaps you could use a memo field.
I chose to ignore this as it was unlikely that I would need to use more than 254.  There doesn't seem to be much documentation as to the limits for these fields.  I asked ChatGPT to get them and it did to some extent.  Create for yourself maximums that make sense.  I doubt the invoice number will be more that 20 or so.  You will need to add records to the file for each field you want to get.  The complication is for sub fields.  For instance CustomerRef has an ID and a name so you need a record with a Fname of CustomerRef.Name and another record for CustomerRef.Id.  Name is C(100), Id is C(10).
You will notice that I run a program which will create a DBF for the Invoice Entity with a file name. There are several ways to create a table, I chose to use this function which should be down at the bottom of your PRG or you could call it as a separate PRG.  Also, Intuit uses the term Entity instead of transaction type.

In my program, I need to get all of the invoices and all of the credit memos.  Each extraction will return and add to my resulting data file but as I need to net sales, I need to make the credit memos negative.  The program returns credit memos with positive numbers.  So I added a multiplier mmultiplier.  I also set a variable to indicate I want Intuit to give my request back to me in XML format.  Intuit can do it in JSON or XML.  Foxpro does not have a parser for either and XML was easier for me to handle.  But if you can parse JSON, then have at it and write your own parser into a DBF

One big caveat here.  There are several entities (Transaction types) that have child records.  In this case, the header record and the line items of an invoice can be returned from a query to QBO so to handle the line items, I will load them into a separate DBF.  So because of the separate DBF, I will need to have these fields in my fieldlist table.  So for instance, if I ask for all fields "Select * from invoice",  then the lines will be returned.  If I specify only certain fields and do not use "Line" then the return will not include the line items.  So my calling program will opt to do the lines or not and what file name result to use.  My fieldlist will have an entity code if "invoiceline".

So now comes the SQL.  This SQL is not the full SQL and it is not the Foxpro SQL.  It has some limits.  It can do some filtering and it can set orders but I don't believe it can do joins and groups and the more sophisticated features.  If you call it and it returns a bad request, it means some part of the SQL is no good.  A field name or one of these commands.  Intuit is really picky.  Lots of other stuff here.  For instance: "Select * from Invoice" works. you get everything even the line items but it takes longer to process.  Where clauses seem to need to be encoded but not the rest of the command???  Another quirk is that an request will only return up to 1000 records.  If you don't put in the maxresults, it will limit to 100 records so if you have a lot of records, you may have to make multiple calls.  The second call you would say STARTPOSITION 1001 MAXRETURNS 1000.  And so on.  I don't know of a way to know how many invoices there would be if it were to be unlimited.  In the example below, you will see 2 queries for invoices and 1 for credit memos.  You might be wondering if the credit memo can be added to the invoice DBF.  It turns out that the data structures are exactly the same otherwise we would need another set of records in the fieldlist.dbf and we would need to create a separate DBF based on that structure.  Then you would need to do whatever you want separately.  And after the three separate calls, And the result has been processed, I needed to do some work on te data file to fix the addresses

Here is the code.  You might notice that I left some code in here if I were to use the Chilkat library just to give you a look at it.  Chilkat is 16 lines of code, without, it is 34 lines. 

The calling program above was "DO makecall WITH SQL,filename,childfilename,mentity,mentitychild"
 

Now we have a valid access token.  We pass the company in the url and the SQL and we pass the access token in the header of the HTML request.  All of the parts are in place either way.  If you get a bad request, you might notice the _cliptext.  If you cannot figure what is wrong with the request, you could paste the error message into ChatGPT and it might tell you what is wrong.  For me, it was learning what needed to be encoded.  I will also note that I was thinking I could use this SQL  Select Customerref.Name from Invoice.  All of the documentation said I could do this but I could never get it to work.  Always bad request.  So we  will remove extraneous characters and get ready to pass the required fields.  I cannot tell you why some parts are passed through the header and others are passed through the URL but read the code below and you will see what works.

That is the MakeCall.prg.  It should be universal for all of your API calls.
In my many attempts to figure the last piece out, I tried parsing JSON, I tried parsing XML, I tried parsing with a parsing program that ChatGPT wrote.
Finally I wrote my own for XML. Whatever you call your PRG, replace my DBFIT6XML with your prg. Sometimes this is the best way so you know what is happening and how to fix what doesn't work.  I also decided to store the response in a separate variable because my parsing technique removes from the string, the fields I get and in the end, my LCresponse has a length of zero. 

See the xml parsing blog for that last chunk of code.

Craig Sobel