This is the API Call for a report. It differs only slightly from a transaction query in that the request is not an SQL type. Instead it looks like calling a URL and passing the report request with options and authorization. The application I needed to create is for a vendor detail balance report. You might ask, why don't I just output an excel report. But in this case, I want to do a comparison of one company's vendor detail report and a second company's customer detail report. It is a question of determining the transactions between two QBO companies intercompany balance. I won't bore you with the last steps to do the comparison. I will leave the eventual processing to you. My blog will be about running the API on a report and parsing the result in JSON. It is usually helpful to know what the report looks like in the GUI. The reason is that if you don't modify the columns requested, it will look like the report in the GUI.
Lets get started. We will call our program getvdb.prg. (Get Vendor Detail Balance)
We will need to construct a resulting table and so I added some records to my fieldlist.dbf. For my proposes, I only need 5 fields.
Create table result (vendor c(40),docnumber c(20),amount n(12,2),balance n(12,2),txndate c(20),Transtype c(20)) I could do this in the code or
I can
call the build table routine
**Do whatever you need with the result.dbf
FUNCTION createtable
PARAMETER lcentity,lcfilename
USE fieldlist
SET FILTER TO entity=lcentity
mfieldcount=1
SCAN
DIME marray(mfieldcount,4)
marray(mfieldcount,1)=(colname)
DO CASE
CASE TYPE="String"
marray(mfieldcount,2)="C"
marray(mfieldcount,3)=VAL(MAXLEN)
marray(mfieldcount,4)=0
CASE TYPE="Number"
marray(mfieldcount,2)="N"
marray(mfieldcount,3)=12
marray(mfieldcount,4)=2
IF "," $ MAXLEN
marray(mfieldcount,3)=VAL(LEFT(MAXLEN,AT(",",MAXLEN)-1))
marray(mfieldcount,4)=VAL(SUBSTR(MAXLEN,AT(",",MAXLEN)+1))
ENDIF
CASE TYPE="Boulean"
marray(mfieldcount,2)="L"
marray(mfieldcount,3)=1
marray(mfieldcount,4)=0
ENDCASE
mfieldcount=mfieldcount+1
ENDSCAN
USE
CREATE TABLE (lcfilename) FROM ARRAY marray
USE
The next step is the makecallreport. This is almost identical to the makecall.prg for the first part which is about checking the
tokens
At this point, there is a case statement that runs the parsing for the reports. JSON's for the reports are
columnar based so there is a title to each column but you don't extract the amounts by naming the column. It is the column number
starting with 0 so you should know the order and meaning of the columns. The best way to do this is to stop the program, and read
through the response. Pasting it into word will work but it isn't that easy to read. Pasting it into ChatGPT and asking it to
indent the JSON works much better but ChatGPT has a limit so you may need to cut the text down before pasting. It really helps.
There is one other thing. You will notice that for many reports, the data is only one level down. IE, there is a vendor grouping
and then the list of transactions. On the Profit and Loss Detail, the data is on the 4th level in. Look at a report in the GUI,
At the top, Ordinary Income/Expense, Income, Sales, and then the transaction. See, its the 4th level in so in the example below, you
will see a case statement for PLD. This digs down to the 4th level.
You might notice at nearly the bottom, one of my clients uses a general ledger in a multi-currency situation. The home currency is in
Euros, the currency I need is in $ so I extract the exchange rate. I could simply set on error * or I can option this.
So that is parsing reports. Another little bugaboo was classes. Many transactions use classes but this level of the API
rarely allows the class field to be added to a report or transaction. One of the few reports was the Profit & Loss Detail.
Classes are really important and the fact that you cannot get it in most reports here even thought it is readily available in the GUI
customize report column list is frustrating.
Good Luck
Craig Sobel