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.
**This function will create a table from a file with the field names and structures
**the structure of the fieldlist.dbf is (Entity c(15), Fname c(50),Colname c(10),Type c(15),Maxlen c(10))
**for our project, we need just the Invoice Entity
**Entity="Invoice", Fname=anything to describe the field, colname=(up to 10 character field name)
**Type= String, Number, Boolean
**Maxlen=Width of field, for numbers, it is defaulted to 12,2 but you can change it using something like 12,5
**you will need to load this table with a set of records for each Entity. The resulting file that is created is
**where you place the data extracted from the company. You may wish to create every possible field that
**exists in quickbooks for the entity.
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
**extra fields I use these fields to consolidate the city, state and zip.
**for my purpose, I needed to use the ship address but if it was blank, go to the bill address
**at the end of my program, Load these fields with what I want
DIME marray(mfieldcount,4)
marray(mfieldcount,1)="City"
marray(mfieldcount,2)="C"
marray(mfieldcount,3)=40
marray(mfieldcount,4)=0
mfieldcount=mfieldcount+1
DIME marray(mfieldcount,4)
marray(mfieldcount,1)="State"
marray(mfieldcount,2)="C"
marray(mfieldcount,3)=2
marray(mfieldcount,4)=0
mfieldcount=mfieldcount+1
DIME marray(mfieldcount,4)
marray(mfieldcount,1)="Zip"
marray(mfieldcount,2)="C"
marray(mfieldcount,3)=10
marray(mfieldcount,4)=0
USE
CREATE TABLE (lcfilename) FROM ARRAY marray
USE
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
*****************************
*This is now the api call
SET SAFETY OFF
CLOSE DATA
CLEAR
SET TALK OFF
myear=SPACE(4)
@1,1 SAY "Enter year" GET myear PICTURE "####"
READ
?"Constructing the sql command"
mstart=myear+"-01-01"
mend=myear+"-12-31"***this section is the choosing the customer.
***Actually doing an authorization is optional after the first time on a qbo company
mwho=SPACE(5)
DO selcust WITH "Either customer"
IF mwho="X"
CANCEL
ENDIF
IF mwho="NEW"
mauthorize="Y"
DO authorize WITH ""
ELSE
mauthorize="N"
@12,10 SAY "Authorize ?" GET mauthorize PICTURE "@m N,Y"
READ
IF mauthorize="Y"
DO authorize WITH mwho
ENDIF
ENDIF
loscript = CREATEOBJECT("MSScriptControl.ScriptControl")
mxml=.F.
************ROUTINE FOR MULTICURRENCY************
**I have a client that does multicurrency so I needed this feature. It was to determine if it was enabled
**to do this. I ran a query on the Preferences entity and returned a True of False.
**my parsing routine requires these fields and my muticurrency routine has the entity and file names built in.
mchild=.f. &&there are no child records for the preference entity
CLOSE DATA
*****Determine of company info has multicurrency turned on
mmulticurrency=.F.
***DO multicurrency
CLOSE DATA
************END OF MULTICURRENCY************
*Add a space after invoice to keep it from reading any other set of records that start with the same name
*such as "Invoiceline
mentity="Invoice "
mentitychild=""
mchildfilename=""
mfilename="invmast" &&resulting DBF
DO createtable WITH mentity,mfilename
IF mchild
**need to build a second DBF to hold the child records
**We need to know the file name
**We need to know the entity
**For the lines to be accumulated in the child data base, the line field must be in
** the SQL or the SQL must be all fields
mentitychild="InvoiceLine"
mchildfilename="invlines"
DO createtable WITH mentitychild,mchildfilename
ENDIF
mmultiplier=1 &&need the multiplier because credit memos are returned positive but I want to make them negative
**the SQL could say "Select * from Invoice" but this example shows how to specify specific fields
SQL="select metadata,txndate,synctoken,totalamt,Line,TxnTaxDetail,docnumber,CustomerRef,shipaddr,billaddr from Invoice
where%20TxnDate%20%3E%3D%20%27"+ mstart+"%27and%20TxnDate%20%3C%3D%27"+mend+"%27ORDER BY DocNumber STARTPOSITION 1 MAXRESULTS 1000"
DO makecall WITH mwho,SQL,mfilename,mchildfilename,mentity,mentitychild
mmultiplier=1
SQL="select metadata,txndate,synctoken,totalamt,Line,TxnTaxDetail,docnumber,CustomerRef,shipaddr,billaddr from Invoice
where%20TxnDate%20%3E%3D%20%27"+ mstart+"%27and%20TxnDate%20%3C%3D%27"+mend+"%27ORDER BY DocNumber STARTPOSITION 1001 MAXRESULTS 1000"
DO makecall WITH mwho,SQL,mfilename,mchildfilename,mentity,mentitychild
mmultiplier=-1 &&now we flip to negative
SQL="select metadata,txndate,synctoken,totalamt,Line,TxnTaxDetail,docnumber,CustomerRef,shipaddr,billaddr from CreditMemo
where%20TxnDate%20%3E%3D%20%27"+ mstart+"%27and%20TxnDate%20%3C%3D%27"+mend+"%27ORDER BY DocNumber STARTPOSITION 1 MAXRESULTS 1000"
DO makecall WITH mwho,SQL,mfilename,mchildfilename,mentity,mentitychild
SELECT (mfilename)
REPL ALL city WITH ship_city
REPL ALL state WITH ship_state
REPL ALL zip WITH ship_zip
SET FILTER TO EMPTY(city)
REPL ALL zip WITH RIGHT(TRIM(bill_line1),5) FOR VAL(RIGHT(TRIM(bill_line1),5))>0
REPL ALL zip WITH RIGHT(TRIM(bill_line2),5) FOR VAL(RIGHT(TRIM(bill_line2),5))>0
REPL ALL zip WITH RIGHT(TRIM(bill_line3),5) FOR VAL(RIGHT(TRIM(bill_line3),5))>0
REPL ALL zip WITH RIGHT(TRIM(bill_line4),5) FOR VAL(RIGHT(TRIM(bill_line4),5))>0
REPL ALL zip WITH RIGHT(TRIM(bill_line5),5) FOR VAL(RIGHT(TRIM(bill_line5),5))>0
REPL ALL zip WITH RIGHT(TRIM(bill_line6),5) FOR VAL(RIGHT(TRIM(bill_line6),5))>0
REPL ALL zip WITH RIGHT(TRIM(bill_line7),5) FOR VAL(RIGHT(TRIM(bill_line7),5))>0
REPL ALL zip WITH RIGHT(TRIM(bill_line8),5) FOR VAL(RIGHT(TRIM(bill_line8),5))>0
REPL ALL state WITH SUBSTR(bill_line1,AT(",",bill_line1)+2,2) FOR "," $ bill_line1
REPL ALL state WITH SUBSTR(bill_line2,AT(",",bill_line2)+2,2) FOR "," $ bill_line2
REPL ALL state WITH SUBSTR(bill_line3,AT(",",bill_line3)+2,2) FOR "," $ bill_line3
REPL ALL state WITH SUBSTR(bill_line4,AT(",",bill_line4)+2,2) FOR "," $ bill_line4
REPL ALL state WITH SUBSTR(bill_line5,AT(",",bill_line5)+2,2) FOR "," $ bill_line5
REPL ALL state WITH SUBSTR(bill_line6,AT(",",bill_line6)+2,2) FOR "," $ bill_line6
REPL ALL state WITH SUBSTR(bill_line7,AT(",",bill_line7)+2,2) FOR "," $ bill_line7
REPL ALL state WITH SUBSTR(bill_line8,AT(",",bill_line8)+2,2) FOR "," $ bill_line8
REPL ALL city WITH LEFT(bill_line1,AT(",",bill_line1)-1) FOR "," $ bill_line1
REPL ALL city WITH LEFT(bill_line2,AT(",",bill_line2)-1) FOR "," $ bill_line2
REPL ALL city WITH LEFT(bill_line3,AT(",",bill_line3)-1) FOR "," $ bill_line3
REPL ALL city WITH LEFT(bill_line4,AT(",",bill_line4)-1) FOR "," $ bill_line4
REPL ALL city WITH LEFT(bill_line5,AT(",",bill_line5)-1) FOR "," $ bill_line5
REPL ALL city WITH LEFT(bill_line6,AT(",",bill_line6)-1) FOR "," $ bill_line6
REPL ALL city WITH LEFT(bill_line7,AT(",",bill_line7)-1) FOR "," $ bill_line7
REPL ALL city WITH LEFT(bill_line8,AT(",",bill_line8)-1) FOR "," $ bill_line8
SET FILTER TO
CLEAR
SET TALK ON
SUM amount-totaltax
COPY TO result.XLS TYPE XLS
WAIT WINDOW "Result.xls has been created"
QUIT
Now comes the next step, The MAKECALL.PRG. This program is a function that actually does the querying Intuit for the data
requested but first makes sure the access codes is valid and not expired. And if it is, it exchanges the refresh code (token) for a new
access token. then it parses the results into the DBF's created.
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.
PARAMETERS lcwho,msql,lcfilename,mchildfilename,mentity,mentitychild
*****************************
*This is now the api call
USE oauth_tokens
COUNT FOR NOT EMPTY(atoken)
IF _TALLY = 0
MESSAGEBOX("No OAuth tokens found.")
RETURN
ENDIF
LOCATE for who=lcwho
**getting the tokens
lcaccesstoken = atoken
lcrefreshtoken = rtoken
dtaccessexpires = CTOT(aexpires)
dtrefreshexpires = CTOT(rexpires)
lcrealmid=TRIM(realmid)
USE
* Check if access token expired
IF DATETIME() >= dtaccessexpires
mclientid = "your client id here"
mclientsecret = "your client secret"
mtokenendpoint = "https://oauth.platform.intuit.com/oauth2/v1/tokens/bearer"
lohttp = CREATEOBJECT("WinHttp.WinHttpRequest.5.1")
lohttp.OPEN("POST", mtokenendpoint, .F.)
lohttp.setrequestheader("Content-type","application/x-www-form-urlencoded")
lohttp.setrequestheader("Accept","Application/json")
lcbody= ;
"grant_type=refresh_token" + ;
"&refresh_token="+lcrefreshtoken + ;
"&client_id=" + mclientid + ;
"&client_secret=" + mclientsecret
lohttp.SEND(lcbody)
IF lohttp.STATUS = 200
lchtml = lohttp.responsetext
ELSE
MESSAGEBOX("Got nothing back from the exchange." + TRANSFORM(lohttp.STATUS))
CANCEL
ENDIF
?"Parsing the response"
*this is a simple parse, the only things we need are the access and refresh tokens
lcresponse=lohttp.responsetext
losc=CREATEOBJECT("ScriptControl")
losc.language = "JScript"
losc.addcode( ;
"function parsejson(s) { " + ;
" return eval('(' + s + ')');" + ;
"}")
loobj=losc.RUN("parsejson",lcresponse)
lcaccesstoken=loobj.access_token
lcrefreshtoken=loobj.refresh_token
dtaccessexpires = TTOC(DATETIME() + 3600)
dtrefreshexpires = DATETIME() + 3600*24*100
USE oauth_tokens EXCLUSIVE
******THIS SAVES THE TOKEN TO BE USED CONTINUOUSLY
LOCATE FOR who=lcwho
IF EOF()
APPEND BLANK
REPLACE who WITH lcwho
ENDIF
REPLACE atoken WITH lcaccesstoken, ;
rtoken WITH lcrefreshtoken, ;
aexpires WITH dtaccessexpires, ;
rexpires WITH DTOC(dtrefreshexpires), ;
realmid WITH lcrealmid
USE
ENDIF
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.
lcrealmid=STRTRAN(lcrealmid, CHR(13)+CHR(10),"") &&remove newlines
lcrealmid=STRTRAN(lcrealmid, CHR(32),"") &&remove spaces
lcurl = "https://quickbooks.api.intuit.com/v3/company/" + lcrealmid + "/query?query=" + msql + "&minorversion=75"
IF mxml
tcaccept = "application/xml"
ELSE
tcaccept = "application/json"
ENDIF
lohttp = CREATEOBJECT("WinHttp.WinHttpRequest.5.1")
lohttp.OPEN("GET", lcurl, .F.)
lohttp.setrequestheader("Accept",tcaccept)
lohttp.setrequestheader("Authorization", "Bearer " + lcaccesstoken)
lohttp.SEND()
IF lohttp.STATUS = 200
lcresponse = lohttp.responsetext
ELSE
lcresponse = "ERROR: " + TRANSFORM(lohttp.STATUS) + ;
" - " + lohttp.responsetext
ENDIF
lcresponse = LTRIM(RTRIM(lcresponse))
lcresponse = STRTRAN(lcresponse, CHR(13), "")
lcresponse = STRTRAN(lcresponse, CHR(10), "")
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.
IF lohttp.STATUS = 200
LOCAL json, success, root, customers, i, CUST
lcxml=STRTRAN(lcresponse,'"',"")
IF mxml
DO dbfit6xml WITH lcfilename,mchildfilename
ELSE
**a little bit of name changing
lcdbf=lcfilename
lcchilddbf=mchildfilename
**mentity is used to filter the fieldlist file. To watch for partial matches, I added a space at the end.
**in JSON, i am using the mentity to scan down the json. To do this, the added space needs to be removed
mentity=TRIM(mentity)
SELECT 0
USE (lcdbf)
IF mchild
SELECT 0
USE (lcchilddbf)
ENDIF
**parse the resulting text file
* lcJson = your JSON string
lcjson = lcresponse
loscript.language = "JScript"
* Inject JSON into JS context
loscript.addcode([var data = ] + lcjson + [;])
lctext = lcresponse && replace with your variable holding the text
**if the response is hard to understand, I can paste it into a word document or what is better is to ask
**chatgpt to indent this JSON and then paste it in. Stop the program with a SET STEP ON
**the text will be in the clip board so Ctrl-V in chatgpt
_cliptext=lctext
**Set step on
**you are going to see several entities which I have used on various projects
**skip down to CASE entity="invoice" or entity="CreditMemo"
DO CASE
CASE mentity="JournalEntry"
**purchase is the entity for transaction type "Expense"
lndoccount = (LEN(lctext) - LEN(STRTRAN(lctext, "sparse", ""))) / LEN("sparse")
SELECT (lcdbf)
FOR lninv = 0 TO lndoccount - 1
WAIT WINDOW STR(lninv,5) NOWAIT
SELECT (lcdbf)
APPEND BLANK
REPLACE id WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Id")
REPLACE synctoken WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].SyncToken")
REPLACE docnumber WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].DocNumber")
REPLACE descrip WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Name")
REPLACE txndate WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TxnDate")
ENDFOR
USE
CASE mentity="Item"
lndoccount = (LEN(lctext) - LEN(STRTRAN(lctext, "sparse", ""))) / LEN("sparse")
SELECT (lcdbf)
FOR lninv = 0 TO lndoccount - 1
WAIT WINDOW STR(lninv,5) NOWAIT
SELECT (lcdbf)
APPEND BLANK
REPLACE id WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Id")
REPLACE descrip WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Name")
ENDFOR
USE
CASE mentity="Preferences"
**the only think I am looking for is to see if multicurrency is enabled
**but i may want to add other preferences in the future
SELECT (lcdbf)
APPEND BLANK
lninv=0
REPLACE multicurr WITH .f.
ON ERROR *
REPLACE multicurr WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].CurrencyPrefs.MultiCurrencyEnabled")
ON ERROR
CASE mentity="Invoice" OR mentity="CreditMemo"
**the word "sparse" occurs once per invoice so counting the number of "sparse"s this way works
lninvoicecount = (LEN(lctext) - LEN(STRTRAN(lctext, "sparse", ""))) / LEN("sparse")
SELECT (lcdbf)
FOR lninv = 0 TO lninvoicecount - 1
WAIT WINDOW STR(lninv,5) NOWAIT
SELECT (lcdbf)
APPEND BLANK
* Get invoice fields
REPLACE ID WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Id")
REPLACE synctoken WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].SyncToken")
REPLACE metcretime WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].MetaData.CreateTime")
REPLACE metlastupd WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].MetaData.LastUpdatedTime")
REPLACE docnumber WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].DocNumber")
REPLACE txndate WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TxnDate")
REPLACE totaltax WITH VAL(loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TotalTax"))*mmultiplier
REPLACE duedate WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].DueDate")
REPLACE amount WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TotalAmt")*mmultiplier
REPLACE custname WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].CustomerRef.name")
REPLACE MEMO WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].CustomerMemo")
REPLACE projectref WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ProjectRef")
REPLACE bill_line1 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line1")
REPLACE bill_line2 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line2")
REPLACE bill_line3 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line3")
REPLACE bill_line4 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line4")
REPLACE bill_line5 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line5")
REPLACE bill_line6 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line6")
REPLACE bill_line7 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line7")
REPLACE bill_line8 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].BillAddr.Line8")
REPLACE ship_line1 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line1")
REPLACE ship_line2 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line2")
REPLACE ship_line3 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line3")
REPLACE ship_line4 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line4")
REPLACE ship_line5 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line5")
REPLACE ship_line6 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line6")
REPLACE ship_line7 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line7")
REPLACE ship_line8 WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.Line8")
REPLACE ship_city WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.City")
REPLACE ship_state WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.CountrySubDivisionCode")
REPLACE ship_zip WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ShipAddr.PostalCode")
IF mchild
* --- Count lines in transaction ---
lnlinecount = loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line.length")
* --- Loop through lines and extract if present ---
FOR lnl = 0 TO lnlinecount - 1
IF loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].DetailType")="SalesItemLineDetail"
SELECT (lcchilddbf)
APPEND BLANK
REPLACE docnumber WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].DocNumber")
REPLACE ID WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].Id")
REPLACE LINENO WITH
VAL(loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].Lineno"))
REPLACE DESCRIP WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].Description")
**some item are unitprice * quantity, others are just price
mprice=loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.UnitPrice")
IF TYPE("mprice")="C"
**then look for price instead
mprice=loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.Price")
ENDIF
IF TYPE("mprice")="C"
mprice=VAL(mprice)
ENDIF
mqty=loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.Qty")
IF TYPE("mqty")="C"
mqty=1
ENDIF
REPLACE price WITH mprice
REPLACE qty WITH mqty*mmultiplier
REPLACE extprice WITH price*qty
REPLACE ITEM WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.ItemRef.Name")
REPLACE ITEMID WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.ItemRef.Value")
REPLACE account WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.ItemAccountRef.name")
REPLACE accountid WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.ItemAccountRef.Value")
REPLACE taxcode WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].SalesItemLineDetail.TaxCodeRef.Value")
ENDIF
ENDFOR
ENDIF
ENDFOR
USE IN (lcdbf)
IF MCHILD
USE IN (lcchilddbf)
ENDIF
CASE mentity="Bill" OR mentity="VendorCredit"
**it seems as though you can count sparse occurances as every full transaction has a sparse indicator
lndoccount = (LEN(lctext) - LEN(STRTRAN(lctext, "sparse", ""))) / LEN("sparse")
FOR lninv = 0 TO lndoccount - 1
WAIT WINDOW STR(lninv,5) NOWAIT
SELECT (lcdbf)
APPEND BLANK
* Get invoice fields
REPLACE ID WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Id")
REPLACE synctoken WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].SyncToken")
REPLACE metcretime WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].MetaData.CreateTime")
REPLACE metlastupd WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].MetaData.LastUpdatedTime")
REPLACE docnumber WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].DocNumber")
REPLACE txndate WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TxnDate")
REPLACE totaltax WITH VAL(loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TotalTax"))*mmultiplier
REPLACE duedate WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].DueDate")
REPLACE amount WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TotalAmt")*mmultiplier
REPLACE balance WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Balance")*mmultiplier
REPLACE vendname WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].VendorRef.name")
REPLACE vendid WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].VendorRef.value")
REPLACE apacct WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].APAccountRef.name")
REPLACE apacctid WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].APAccountRef.value")
REPLACE MEMO WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].CustomerMemo")
REPLACE projectref WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ProjectRef")
*!* REPLACE Vendaddid WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].VendorAddr.Id")
*!* REPLACE Vendaddl1 WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].VendorAddr.Line1")
*!* REPLACE Vendaddl2 WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].VendorAddr.Line2")
*!* REPLACE Vendaddl3 WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].VendorAddr.Line3")
*!* REPLACE Vendaddl4 WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].VendorAddr.Line4")
* REPLACE city WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].ShipAddr.City")
* REPLACE state WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].ShipAddr.CountrySubDivisionCode")
* REPLACE zip WITH loScript.Eval("data.QueryResponse."+mentity+"["+allt(STR(lnInv,3))+"].ShipAddr.PostalCode")
* --- Count lines in transaction ---
IF mchild
lnlinecount = loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line.length")
* --- Loop through lines and extract if present ---
FOR lnl = 0 TO lnlinecount - 1
IF loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].DetailType")="AccountBasedExpenseLineDetail"
SELECT (lcchilddbf)
APPEND BLANK
REPLACE docnumber WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].DocNumber")
REPLACE ID WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].Id")
REPLACE LINENO WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].LineNum")
REPLACE acctid WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].AccountBasedExpenseLineDetail.AccountRef.value")
REPLACE account WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].AccountBasedExpenseLineDetail.AccountRef.name")
REPLACE extprice WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].Amount")
REPLACE taxcode WITH
loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Line["+ALLT(STR(lnl,3))+"].AccountBasedExpenseLineDetail.AccountRef.Value")
ENDIF
ENDFOR
USE
ENDIF
ENDFOR
USE IN (lcdbf)
IF MCHILD
USE IN (lcchilddbf)
ENDIF
CASE mentity="Class"
**this is the chart of accounts
lndoccount = (LEN(lctext) - LEN(STRTRAN(lctext, "sparse", ""))) / LEN("sparse")
FOR lninv = 0 TO lndoccount - 1
WAIT WINDOW STR(lninv,5) NOWAIT
SELECT (lcdbf)
APPEND BLANK
REPLACE NAME WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Name")
REPLACE synctoken WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].SyncToken")
REPLACE ID WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Id")
ENDFOR
USE
CASE mentity="Account"
**this is the chart of accounts
lndoccount = (LEN(lctext) - LEN(STRTRAN(lctext, "sparse", ""))) / LEN("sparse")
FOR lninv = 0 TO lndoccount - 1
WAIT WINDOW STR(lninv,5) NOWAIT
SELECT (lcdbf)
APPEND BLANK
REPLACE NAME WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Name")
REPLACE accttype WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].AccountType")
REPLACE ID WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Id")
ENDFOR
USE
CASE mentity="Purchase"
**purchase is the entity for transaction type "Expense"
lndoccount = (LEN(lctext) - LEN(STRTRAN(lctext, "sparse", ""))) / LEN("sparse")
FOR lninv = 0 TO lndoccount - 1
WAIT WINDOW STR(lninv,5) NOWAIT
SELECT (lcdbf)
APPEND BLANK
REPLACE accountfrm WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].AccountRef.Value")
REPLACE totalamt WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TotalAmt")*mmultiplier
REPLACE ID WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].Id")
REPLACE synctoken WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].SyncToken")
REPLACE metcretime WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].MetaData.CreateTime")
REPLACE metlastupd WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].MetaData.LastUpdatedTime")
REPLACE txndate WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].TxnDate")
**only get this if multicurrency is enabled
if mmulticurrency
REPLACE exchrate WITH loscript.EVAL("data.QueryResponse."+mentity+"["+ALLT(STR(lninv,3))+"].ExchangeRate")
endif
ENDFOR
USE
ENDCASE
ENDIF
ENDIF
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