This is the third 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, #1 we will need to construct an SQL statement, #2 a generic program to check on the expiration of the access code and to refresh it if needed, then once we have the extraction, #3 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.  In constucting the SQL, we will also need to construct a resulting DBF to place what we get back.  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.  This first function located at the bottom of Getinvoices.prg creates the resulting data file.  To get a sense of the fields available, go 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.  In the window, you will see all of the fields and the sub fields.   Foxpro has a 254 limit so no String field can exceed this.  Many fields in QBO have a name and an ID which is an internal number which is used quite often so you need fields for both most of the time.  For instance CustomerRef has an ID and a name. And each field in the DBF must be unique so sometimes you want to have CUSTID and ACCOUNTID.
This is a function at the bottom of every API call.  It is mostly the same but every API might need extra fields so it isn't generic.

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 JSON format.  Intuit can do it in JSON or XML.  Foxpro does not have a parser for either and this blog will show the JSON parser.

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 and I will use the same create table function to generate a child dbf.  My entity would be something like Invoiceline and a set of records appropriate to the line items of an invoice.  I set a variable of Child=.t. or .f. to control this.  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 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 but frankly, it is sometimes easier to get everything and then sort and filter in the resulting table.  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 for credit memos.  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 the data file to fix the addresses.  So lets look at the full program up to the createtable function

The calling program above was "DO makecall WITH mwho,SQL,mfilename,mchildfilename,mentity,mentitychild".  Since we have chosen the company, we can pass that company to the first part to get the token we use in our call to Intuit.
 

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

This part above is the first section of the MAKECALL.PRG.  The next step is parsing the result.  This program now includes
the following section which parses the JSON response.  Parsing transactions follows a fairly consistent approach.  This next section is all about parsing JSON.  I have the entities that I needed for various projects.  You will need to work out which fields you need and you need to make sure all of the fields you want have corresponding fields in the fieldlist data file.

This parsing routine is pretty cool.  The field names in the replace statement of course must match the table.
the loscript.EVAL( ) function seems to be some built in function that is enabled back in the getinvoices.prg
loscript = CREATEOBJECT("MSScriptControl.ScriptControl")
What I noticed is the repeated creations of the object will sometimes crash the system so I only run this command once.  Originally I had this in the parsing routine but parsing gets called at least 3 times here and would randomly crash.

Well that is it.  The next blog is about running and parsing reports.  There are many overlapping concepts but reports are not called with an SQL command and so I have a completely different MAKECALL.PRG which I named MAKECALLREPORT.PRG

Craig Sobel