Using Streaming Output
and the Low Level File functions for a simple
Mail Merge /Custom Report Generator
by John York, BRITESIDE, inc (dBASE user since dBASE II on a CPM machine - self taught from that point on.  Specializing in Municipal software — Consulting to any government entity).
The streaming output option for printing from inside any version of VdB is an often overlooked capability that can actually be a very useful component for some quick and simple reports. And when it is combined with another often overlooked capability, namely the low level file functions, some very interesting results may be obtained.

Both of these capabilities are too often ignored or forgotten, perhaps because they are so misunderstood.  The following example combines these two capabilities to provide a very simple example of a combination mail merge / report generator.  Included is the source code for the form itself along with a 3 record demo data table.

In my case, I have a few experienced users who like this form and use it in its present condition. One of those long-term projects pending is to flesh it out, hide the formatting codes, and combine it with another homespun query builder that creates a temp table for use by this print form.

The segments of code that bear closer examination are included here with expanded remarks and explanations.

After a data table is selected, the datasource for a combobox is set to “structure”. This gives us a list of all the fields in the selected table for inclusion into our printout.

  Procedure PUSHBUTTON1_OnClick
    *--> get the table we are going to use
    cFile = getfile("*.dbf")
    form.entryfield1.value = cFile
    *--> set the form's VIEW property to the table selected
    form.view = "&cFile"
    *--> now we can turn on the other controls

Note the setting of the combobox's data source AFTER the table is opened/changed. This automatically fills the combobox's data source with a fresh array of field names.

  form.combobox1.datasource = "structure"
  form.combobox1.enabled = .t.

The next two procedures simply get a string as the result of selecting a new font or picking a field name in the combobox.

  Procedure PUSHBUTTON4_OnClick
    *--> get the font we are going to use next
    cStyle = getfont()
    *--> place it in the document
    form.editor1.value = form.editor1.value + "<FT-" + trim(cStyle) + ">"

  Procedure COMBOBOX1_OnLeftMouseUp(flags, col, row)
    *--> enter a field into our document
    form.editor1.value = form.editor1.value + "<FN-" + trim(this.value) + ">"

And the work is actually done in the onClick routine of the print button.

  Procedure PUSHBUTTON7_OnClick
    *--> make sure we have a file to use
    if isblank(form.entryfield2.value)
      msgbox("Enter a text file name","ERROR",0+16)
      *--> save the document
      cFile1 = trim(form.entryfield2.value)

The first of the low level functions we use does two things. It gives us a “Handle” for the file we are going to use, and it creates a file - overwriting any existing file by the same name.

      nhandle = fcreate("&cFile1")

So we now have a handle (nHandle) on a file that we can use in the future to reference this particular file.  And we write the contents of the form's editor to that file.


And then we close the file (which destroys the reference contained in our variable nHandle)


    *--> get logical for printing
    *--> by using the variable, we can make decissions later in the routine
    lPrn = chooseprinter()
    *--> if printer was selected - open a channel to the printer
    if lPrn
      set printer on
    *--> start at the beginning
    go top
    *--> if this is a report instead of a letter, we need to be able
    *--> to format the report so we open a second copy of the table
    *--> to use in calculating the spacing required for a columnar report
    if form.radiobutton2.value
      cFile2 = trim(form.entryfield1.value)
      use &cFile2 again in select() alias copy2
    *--> redundant but secure
    select 1
    *--> loop through table
    do while .not. eof()
      *--> create the beginning style based on the editor's default style

Streaming output can be formatted with any available font, or font characteristic — this data is placed as the parameter for the “STYLE” clause of the ? Command.

      cStyle = form.editor1.FontName + "," + ltrim(rtrim(str(form.editor1.FontSize)))
      *--> open the text file and set the pointer at the beginning
      nHandle = fopen("&cFile1")
      *--> read the file line by line
      do while .not. feof(nHandle)

To read a complete line from the text file you can use fGets or fRead - the main difference is that while fRead will read the complete line (or any specified portion of it), it does not return the end-of-line marker while fGets does.

      *--> fGets includes the end-of-line marker
      *--> so we get the hard returns
      cStr = fGets(nHandle)
      *--> we are going to print a new line so
      *--> we start with a single print statement

Streaming output has changed a bit from the old DOS days - ? and ?? are still there, but ??? is problematic and pretty much useless. You can set up a generic print driver in Windows, but the codes sent by the old ??? command are quite often rejected/ignored/garbled when passed through the Windows Print Manager.

     *--> we are going to print a new line so
     *--> we start with a single print statement

The next code segment is a simple parsing of the string looking for the delimiters used to identify the font or field name included in the file, and printing the segment(s) of string left over.

*--> parse the string for codes/field names
if "<" $ cStr
  do while "<" $ cStr && might be more than one code or field
    *--> we get the location of the first set of delimiters
    nStart = at("<",cStr)
    nFinish = at(">",cStr)
    *--> and then get the code itself
    cCode = substr(cStr,nStart,(nFinish-nStart)+ 1)
    *--> strip off the delimiters
    cCode = substr(cCode,2,len(cCode) -2)
    *--> print the portion that came before the code
    cPrint = left(cStr,nStart -1)
    ?? cPrint style "&cStyle"
    *--> determine if the code is for a font change or a field name
    *--> The plan is to add some more functionality in the form of
    *--> report headers and column spacing so we are using two "if"
    *--> statements at this time
    if left(cCode,3) = "FT-" && a font name
      *--> change the style variable
      cStyle = right(cCode,len(cCode) - 3)
    if left(cCode,3) = "FN" && a field name
      cCode = right(cCode,len(cCode) - 3)
      *--> using macro substitution we can place the fields content
      *--> into a variable
      com = "cTest = " + cCode
      *--> test the value for type
      cVar = type(cCode)
      *--> process the value depending on type
      *--> at this time we are keeping the list limited to Character,
      *--> Numeric, date and logical.
      do case
        case cVar = "C"
          *--> character field
          if form.radiobutton2.value && are we printing a report
            *--> use the copy of the table
            select 2
            go top
            *--> determine how wide the maximum value is
            calculate max(len(trim(&cCode))) to nSize
            select 1
            *--> back to the working copy of the table
            cPrint = left(cTest + space(nSize + 3),nSize + 3)
            cPrint = trim(cTest)
        case (cVar = "N") .or. (cVar = "F")
          *--> numeric/float value
          if form.radiobutton2.value && are we doing a report
            *--> size the field and pad it
            cPrint = str(cTest,10,2," ")
            *--> trim the value for readability
            cPrint = ltrim(rtrim(str(cTest,10,2)))
        case cVar = "D"
          *--> date value
          *--> you could use one of the functions in the dUFLP
          *--> to format the date instead of the plain vanilla
          *--> dTOC function used here.
          cPrint = dtoc(cTest)
        case cVar = "L"
          *--> a boolean field that defaults to FALSE for a NULL value
          cPrint = iif(cTest,"True","False")
          *--> if it doesn't fit in the above choices, don't try
          *--> and print it
          cPrint = ""
        *--> print the field's value
        ?? cPrint style "&cStyle"
      *--> cut off the used code and check the string again
      *--> for another code
      cStr= right(cStr,len(cStr)-nFinish)
    *--> print whatever is left of the string
    ?? cStr style "&cStyle"
    *--> no codes in the string, so just print it
    ?? cStr style "&cStyle"
  *--> go back and get another string to process
enddo && while .not. Feof()
*--> close the text file
*--> if a letter instead of a report, eject the page
*--> but only if the printer is on
if form.radiobutton1.value .and. lPrn
*--> get the next record in the data table and
*--> do it all over again

To download this application, click here  for the VdB 5.x version
or click here  for the VdB 7.x version
(it's a 35Kb zipped executable file)