Visual Foxpro connecting to QuickBooks Online

This is the third page to this blog, I describe the last step after we have received the lcresponse from Intuit which contains the XML stream based on our request.

This should be a universal parsing routing but I have a suspicion that there will be a different one for parsing reports instead of transactions On this last program, we will parse the text string lcResponse. Place it into the final DBF and child dbf.

Lets get started. I called this program DBFIT6XML.prg It was my 6th attempt.
I needed to create an intermediate parsing file where we can drop field names and field values keeping track of the levels or indents seen in the API
Below is part of the XML you should receive
<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2015-07-24T10:44:52.998-07:00">
<Invoice domain="QBO" sparse="false">
    <Id>130</Id>
    <SyncToken>0</SyncToken>
    <MetaData>
      <CreateTime>2014-09-19T13:16:17-07:00</CreateTime>
      <LastUpdatedTime>2014-09-19T13:16:17-07:00</LastUpdatedTime>
    </MetaData>
    <CustomField>
    <DefinitionId>1</DefinitionId>
    <Name>Crew #</Name>
    <Type>StringType</Type>
    <StringValue>102</StringValue>
    </CustomField>
    <DocNumber>1037</DocNumber>
    <TxnDate>2014-09-19</TxnDate>
</Invoice>
</IntuitResponse>
**there is a lot to unpack here. SyncToken is used when you post changes to the data. I don't need to get custom fields so if you need them, you will need to add them to your fieldlist. I am not sure what to do if there is more than 1 which would approximate if the invoice had multiple lines.
Here is the first bit of code. . We are going to need the fieldlist.dbf. It is probably bad form to name a field "Field". Perhaps it should be changed so as not to get confused.

Now begin the scanning. I will describe a real quirk in XML. The xml stream for customerRef should look like this

But it actually looks like this. And this matches the API reference
<CustomerRef name="Sonnenschein Family Store">24</CustomerRef>
So how to parse this. If I see name= in the key, I manually create records in the parse file for the name and the ID. This shows up on lines, vendors, etc.
I asked Chatgpt if this was an error and it said both are acceptable but it makes it really hard to parse manually.
There is too much here to unpack but all items at the same indentation level in xml get the same level number. You should suspend the program after this scan and take a good look into the parse.dbf. And then again after the next step of assigning field variables.

mlevel=0
DO WHILE .T.
DO CASE
CASE SUBSTR(lcresponse,1,2)="</"
    *this means that the value is blank
    *remove the end key. Place the end key on the next line, remove the end key from the string
    APPEND BLANK
    REPLACE keyend WITH SUBSTR(lcresponse,1,AT(">",lcresponse)),LEVEL WITH mlevel
    lcresponse=SUBSTR(lcresponse,AT(">",lcresponse)+1)
    mlevel=mlevel-1
    *loop because we have removed the entire key and value
CASE "/>" $ SUBSTR(lcresponse,1,AT(">",lcresponse))
    *this means that the value is blank
    *remove the end key
    APPEND BLANK
    REPLACE keyend WITH SUBSTR(lcresponse,1,AT(">",lcresponse)),LEVEL WITH mlevel
    lcresponse=SUBSTR(lcresponse,AT(">",lcresponse)+1)
CASE SUBSTR(lcresponse,1,1)="<"
    *this means there is no value but a child key
    *get key
    mkey=SUBSTR(lcresponse,1,AT(">",lcresponse))
    *i have key, remove key from string
    lcresponse=SUBSTR(lcresponse, AT(">",lcresponse)+1)
    SELECT parse
    IF "name=" $ mkey
      mlevel=mlevel+1
      *Write the group without the name
      APPEND BLANK
      REPLACE LEVEL WITH mlevel,keyname WITH SUBSTR(mkey,1,AT(" ",mkey)-1)

      mkey=STRTRAN(mkey,"name","Name")
      *Write name
      mlevel=mlevel+1
      APPEND BLANK
      mkeyvalue=SUBSTR(mkey,AT('"',mkey)+1)
      mkeyvalue=STRTRAN(mkeyvalue,'">',"")
      REPLACE LEVEL WITH mlevel,keyname WITH "Name"
      REPLACE keyvalue WITH mkeyvalue
      APPEND BLANK
      REPLACE keyend WITH SUBSTR(mkey,1,AT("=",mkey)-1)
      REPLACE keyend WITH STRTRAN(keyend,"<","</"),LEVEL WITH mlevel
      mlevel=mlevel-1
      *end of name
      *start if ID, there is always an id after name
      mlevel=mlevel+1
      APPEND BLANK
      * whenever there is a key with an attribute, add an Id field. The value of the ID field is
      * the next after the > is the value on the second line
      REPLACE LEVEL WITH mlevel,keyname WITH "Id"
      REPLACE keyvalue WITH SUBSTR(lcresponse,1,AT("<",lcresponse)-1)
      APPEND BLANK
      REPLACE keyend WITH SUBSTR(mkey,1,AT(" ",mkey))+"Id"
      REPLACE keyend WITH STRTRAN(keyend,"<","</"),LEVEL WITH mlevel
      mlevel=mlevel-1
      * i have to remove more from the lcresponse
      lcresponse=SUBSTR(lcresponse,AT("</",lcresponse))
    ELSE
      mlevel=mlevel+1
      APPEND BLANK
      REPLACE LEVEL WITH mlevel,keyname WITH mkey
    ENDIF
    *loop because we have removed the entire key and the next item is a sub key
OTHERWISE
    **there is a value, get value
    mvalue=SUBSTR(lcresponse,1,AT("<",lcresponse)-1)
    SELECT parse
    * append blank
    REPLACE LEVEL WITH mlevel,keyname WITH mkey,keyvalue WITH mvalue
    **now remove value and remove endkey
    lcresponse=SUBSTR(lcresponse, AT("<",lcresponse))
    *mlevel=mlevel-1
    *loop because we have removed the entire key and value
ENDCASE
IF LEN(lcresponse)=0
    EXIT
ENDIF

ENDDO

If at this point you want to inspect the parse.dbf, put in a suspend or set step on here.
The next step is to go through the parse.dbf and find the items that match the names in the fieldlist.dbf and place the field name in the "Field" field. I realize this isn't all that elegant and that I could probably set a relation to and replace all but this was very procedural. The most interesting thing about this is that as you scan through the parse dbf, there will be records in there pertaining to the child table and when you hit the "Line field, we need to switch the filter of the fieldlist to looking at the child entity. And when exiting the line group, we need to set the filter back.. You will also notice that the content of field fname in fieldlist that is being searched is being compounded. IE Line.SalesItemLineDetail.ItemAccountRef.Name Several levels down with period separators as we go down the levels. I haven't seen a level 9 yet.

*phase 2 clean up the key name field and set the field names
*Scan through pars and fix all of the #5 level where the next item is a #6

REPL ALL keyname WITH STRTRAN(keyname,"/>","")
REPL ALL keyname WITH STRTRAN(keyname,"<","")
REPL ALL keyname WITH STRTRAN(keyname,">","")
REPL ALL keyname WITH STRTRAN(keyname," ","")
?"Setting field names on Parse file"

minaline=.F.
SELECT fieldlist
SET FILTER TO entity=mentity
SELECT parse
SCAN
DO CASE
CASE LEVEL=5 AND keyname="Line " AND mchild
    **Set the filter on fieldlist to mentitychild,
    SELECT fieldlist
    SET FILTER TO entity=mentitychild
    SELECT parse
    m5prefix=TRIM(keyname)
CASE LEVEL=5 AND TRIM(keyend)="</Line>" AND mchild
    SELECT fieldlist
    SET FILTER TO entity=mentity
    SELECT parse
    m5prefix=""
CASE LEVEL=5
    SELECT fieldlist
    *Level 5 items are at the top of the document so no prefix
    LOCATE FOR UPPER(fname)=UPPER(TRIM(parse.keyname))
    m5prefix=TRIM(parse.keyname)
    SELECT parse
    IF NOT EMPTY(keyname)
      REPLACE FIELD WITH fieldlist.colname
    ENDIF
CASE LEVEL=6
    SELECT fieldlist
    LOCATE FOR UPPER(fname)=UPPER(m5prefix+"."+TRIM(parse.keyname))
    m6prefix=TRIM(parse.keyname)
    SELECT parse
    IF NOT EMPTY(keyname)
      REPLACE FIELD WITH fieldlist.colname
    ENDIF
CASE LEVEL=7
    SELECT fieldlist
    LOCATE FOR UPPER(fname)=UPPER(m5prefix+"."+m6prefix+"."+TRIM(parse.keyname))
    m7prefix=TRIM(parse.keyname)
    SELECT parse
    IF NOT EMPTY(keyname)
      REPLACE FIELD WITH fieldlist.colname
    ENDIF
CASE LEVEL=8
    SELECT fieldlist
    LOCATE FOR UPPER(fname)=UPPER(m5prefix+"."+m6prefix+"."+m7prefix+"."+TRIM(parse.keyname))
    m8prefix=TRIM(parse.keyname)
    SELECT parse
    IF NOT EMPTY(keyname)
      REPLACE FIELD WITH fieldlist.colname
    ENDIF
ENDCASE
ENDSCAN

So now that the parse file has the values and the names of the fields they are going in, it is a fairly simple task to go through the file and append lines to my resulting data file(s), one line per invoice in this case and one line per child record with the docnumber field being used in both files. It turns out that the word "SPARSE" appears at the beginning of each invoice. This sparse is an indicator that if you want to write to the file, you can do it piecemeal. IE you don't have to do every field, only the ones you want. It doesn't pertain to us here but it is a good place to know you are at the beginning of the invoice. Lastly, adding the child records is done in a separate function at the bottom called getthisline.

**look for the beginning of each document and store the numbers
*especially the doc number
mdocnumber=""
?"Scanning the parse file and writing the values onto the final DBF"
SELECT parse
SCAN
DO CASE
CASE "SPARSE" $ UPPER(keyname) &&we are at the beginning of a new document
    **need to store the id and the docnumber
    SELECT (lcdbf)
    APPEND BLANK
    WAIT WINDOW STR(RECNO(),12,0) NOWAIT
    SELECT parse
    **everything from line to line is fields
    **should put me on the first line of the invoice
ENDCASE
IF keyname="Line " AND mchild
    **switch to child procesing until keyend="</Line>"
    DO getthisline
    LOOP
ENDIF
IF NOT EMPTY(keyvalue) AND NOT EMPTY(FIELD)
    IF keyname="DocNumber"
      **store the docnumber for the child file
      mdocnumber=keyvalue
    ENDIF
    SELECT fieldlist
    SET FILTER TO entity=mentity
    LOCATE FOR UPPER(colname) = UPPER(parse.FIELD)
    SELECT (lcdbf)
    DO CASE
      CASE fieldlist.TYPE="String"
        REPLACE (parse.FIELD) WITH parse.keyvalue
      CASE fieldlist.TYPE="Number"
        REPLACE (parse.FIELD) WITH VAL(parse.keyvalue)*mmultiplier
      CASE fieldlist.TYPE="Boolean"
        REPLACE (parse.FIELD) WITH IIF(parse.keyvalue="True",.T.,.F.)
    ENDCASE
    SELECT parse
ENDIF

ENDSCAN

FUNCTION getthisline
SELECT fieldlist
SET FILTER TO entity=mentitychild
SELECT (lcchilddbf)
*if this line type is SalesItemLineDetail, the do this.
**don't do it for other line types. This will work for invoices and credit memos
**when I get to bills, this might need to add other line types here
**the complication is that the line type might not be the first field so lets keep a record
**of the record numbers and if it turns out we don't want it, we can delete them
APPEND BLANK
mstartrec=RECNO()
**for each record in the child, write the docnumber
REPLACE docnumber WITH mdocnumber
*this routine is to parse out all of the parts of the <Line> ...</Line>
*first of all, only look at the SalesItemLineDetail type lines
SELECT parse
DO WHILE .T.
IF NOT EMPTY(keyvalue)
    SELECT fieldlist
    LOCATE FOR UPPER(colname) = UPPER(parse.FIELD)
    SELECT (lcchilddbf)
    DO CASE
    CASE fieldlist.TYPE="String"
      REPLACE (parse.FIELD) WITH parse.keyvalue
    CASE fieldlist.TYPE="Number"
      REPLACE (parse.FIELD) WITH VAL(parse.keyvalue)*mmultiplier
    CASE fieldlist.TYPE="Boolean"
      REPLACE (parse.FIELD) WITH IIF(parse.keyvalue="True",.T.,.F.)
    ENDCASE
    SELECT parse
    **if this is not a salesitemlinedetail, delete all of the records and exit
    **the type can be found anywhere in the line
    IF parse.keyname="DetailType"
      mtype=parse.keyvalue
    ENDIF
ENDIF
**at the end of the line, exit out
IF parse.keyend="</Line>" OR EOF()
    EXIT
ENDIF
SKIP
ENDDO
**invoices use salesitemlinedetail
**bills use AccountBasedExpenseLineDetail to split the distribution of the bill's expense
**if this is not one of these delete all of the records and exit
IF mtype<>"SalesItemLineDetail" and mtype<>"AccountBasedExpenseLineDetail"
SELECT (lcchilddbf)
DELETE ALL FOR RECNO()=>mstartrec
PACK
ENDIF
SELECT fieldlist
SET FILTER TO entity=mentity
SELECT parse
RETURN

That is the XML Parser. Probably someone could make up a more perfect parser for XML but placing those key fields into a 10 character DBF fieldname makes it very weird and I never learned how to use a parsing program as Foxpro 6.0 doesn't have one natively.
You should now have two files, one with header records, one with child records.

Well that is it. Extracting is one thing, writing is another. Intuit only accepts JSON to write so the process is to construct a JSON. My next project.
If you found this helpful, send me an email Craigs@sobelsoftware.com