Tables, Columns, and Counting

One of the most requested features from the pre-FileFlex 3 days has been what we've called "match lists". A match list is essentially a list of matches. For example, let's assume you search for all customers in Princeton, NJ. FileFlex allows you to retrieve the first match and then use DBSkip to iterate through each subsequent match.

But in FileFlex 3, you can also retrieve the entire set of matching items, all at once. Using a function called DBGetMatchList, you retrieve one or more "columns" of data. A column is a list of items, separated by newlines. For example, if you're looking for customers in Princeton, one column might have all the last names, another all the phone numbers.

Another function, DBSuperPadString, allows you to pad out a column so all the entries are exactly the same width. If you're retrieving a phone number, this isn't as big an issue because you might have forced entry so all were ten characters wide. But last names aren't. Smith is five characters wide, while Schlessinger is twelve characters wide (assuming I've counted correctly). If you wanted to create a precise table where all the elements line up, you might want to add ten spaces after Smith and three spaces after Schlessinger, creating a column where all entries were spaced out to fifteen characters. DBSuperPadString does all this automatically for you.

Now what if you want to fill a column with a separator character (or characters)? Let's say you want a column that is filled with nothing besides vertical bars. You can use DBFillColumn to do this. One call and you've filled it all.

We've got some pretty interesting tools. DBGetMatchList retrieves columns of data. DBSuperPadString makes all those columns the same width. DBFillColumn creates columns of separator characters. But how do you display all this information together?

Well, that's the role of DBColumnMash. DBColumnMash takes two columns and mashes them together, creating a third, combined column. Just mash column after column together, and you get a complete table.

We'll look at each of these in turn. But first, let's look at a few simple counting functions.

Counting the Total Number of Records

You can find the total number of records in a database (this includes records marked as deleted by not yet removed) by calling DBCount.

Use this function whenever you want to know how many records are in a FileFlex file. For example:

put DBCount() into howMany

Calculate an Average

With DBAverage, you can calculate the average of a designated field's values in all of the records of a database file. The function requires the name of a field as an argument. The field must contain numeric data or an error will result. Here is a typical call to this function:

put DBAverage("Salary") into avgSal

DBAverage isn't aware of lists, so it scans the entire data file.

Calculate the Sum of a Field

With the FileFlex DBSum function, you can calculate the total of a designated field's values in all of the records of a database file. The function requires the name of a field as an argument. The field must contain numeric data or an error will result. Here is a typical call to this function:

put DBSum("Salary") into bigBucks

DBSum isn't aware of lists, so it scans the entire data file.

Finding out the Number of Records in a Given Set

DBCount will tell you the number of records in your database. Period. It has no understanding of searches or lists or anything like that. If you did a search and you want to know how many items have been found, you'll need DBCountMatches.

For example, let's assume you did a search of your name and address database for everyone at Macromedia. Once the search has completed successfully, the record pointer would point to the first person in the database (in the current sort order) that had the field COMPANY contain "Macromedia".

You could then call DBCountMatches (no parameters are necessary with this function. For example:

put DBCountMatches() into matchTotal

After execution, DBCountMatches sets the record pointer to the top (as if DBTop had been called) and returns an integer value of the number of matches found.

Retrieving Lists of Matches

DBGetMatchList is one of the most powerful of the new FileFlex 3 functions. It retrieves a list of matches into global variables. Let's look at how this works. Let's assume you have a database containing a bunch of names and phone numbers (before you complain to us, that's how Gennifer spells her name):

Clinton        Bill          202-222-2222
Clinton        Hillary       202-222-3333
Jones          Paula         202-222-4444
Lewinsky       Monica        202-222-5555
Flowers        Gennifer      202-222-6666

Obvious field names for this database would be LAST, FIRST, and PHONE. Assuming everything had been set up right, after calling the DBGetMatchList function, the FIRST would be retrieved and placed into a global variable. The contents of the variable would be:


The way DBGetMatchList works is by taking the current search results (like in DBCountMatches) and retrieving the contents of a column into global variables. Prior to calling DBGetMatchList, you need to set up a specification variable that tells FileFlex where to get the data and where to put it.

FileFlex will allow you to retrieve up to 32 columns in one call. Each column goes into the specification variable on it's own line. You specify each column by including the name of the field to be extracted and the name of the global to place the data into. Here's the contents of a spec variable called vSPEC you might use for the above database:


In this way, the contents of the column FIRST will be placed into the variable called gFIRST.

DBGetMatchList also supports "arrow" notation. The field name can be specified as FILE->FIELD, although the results of this are unpredictable outside relational queries.

DBGetMatchList takes two parameters: the name of the spec variable and a maxlines value (more on maxlines in a moment). Let's assume the spec variable is called vSPEC. Here's what the DBGetMatchList call might look like:

put DBGetMatchList(vSPEC,0) into dbResult

This function has the potential to consume a large amount of temporary memory (it's freed up upon exiting the function). If the second parameter is set to 0, DBGetMatchList will retrieve as much data as will fit in a 32K buffer. This means that for each global you request, the function temporarily allocates 32K. To reduce this, specify the maximum lines. For example, if you specified 50 lines maximum and the field you're retrieving is 10 chars in width, you'd temporarily allocate 500 bytes instead of 32,000.

DBGetMatchList leaves the record pointer on the last record who's data has been returned. To get more data, perform a DBSkip(1) and then call DBGetMatchList again. If there's more data to be received and execution was successful, it returns 0. If it's reached the end of the matches, it returns 3.

One other important note: DBGetMatchList does not support encrypted data. It will not decrypt nor can the DBEncrypt function be used because plain-text carriage returns are placed between each record's field data.

Padding Strings and Columns

When you retrieve a column of data with DBGetMatchList, you're getting back a series of global variables. Each global is a string of data, consisting of the contents of one field per line, each line separated by a newline. However, although fields in FileFlex are of a fixed length, the data returned by DBGetMatchList is not. For example, when you get back the contents of field FIRST into variable gFIRST, you'll get the following data


The first line is only four characters long, the second seven, and so forth. But what if we wanted to make all the lines exactly the same width (presumably so we could line up a table)? We'd want gFIRST to contain data that looked more like the following (with X's taking the place of spaces:


The DBSuperPadString function is the function that makes all this happen. It pads out each line, all the way down the column. It takes four parameters: the item being padded (in this case gFIRST), the final width (in this case 9), the pad character (a space, or in this example, an X), and the justification (L for left or R for right).

So, to take gFIRST and created a padded variable called gFIRSTpad, here's the call:

put DBSuperPadString(gFIRST, 9, "X", "L")

Of course, if you used R instead of L, your data would look like this:


You might want to right justify to format a set of numbers, for example.

Hint: if you use DBSuperPadString on a container containing only one line, it'll just pad that line, making for a nice string padding function for setting up indexed searches.

Filling a Column With a Character

DBFillColumn is a very simple, special purpose function. It's ideal for creating separators. For example, let's assume your goal is to create a table that looks like this:

Clinton   | Bill     | 202-222-2222
Clinton   | Hillary  | 202-222-3333
Jones     | Paula    | 202-222-4444
Lewinsky  | Monica   | 202-222-5555
Flowers   | Gennifer | 202-222-6666

You'd want to create a column of separators consisting of the vertical bar, followed by a space. You can easily do this using DBFillColumn. DBFillColumn takes two parameters: the fill string, and the number of repetitions. To create a five repetition column with the vertical bar and the space, you'd call DBFillColumn like this:

put DBFillColumn("| ", 5) into filler

Combining Columns Into a Table

So now you have a bunch of loose columns, sitting inside variables. For the purpose of our example, let's continue using X for a space. So you have the following in gFIRST:


And you have the following in gLAST (you used DBSuperPadString to fill it out):


You have the following in gPHONE (although you didn't need to pad anything since it's at the end and a fixed value already):


You even have a column of separators all ready, stored in filler:


So let's assemble them all together. The idea is to, literally, mash the columns together. What better tool to use than DBColumnMash? DBColumnMash takes two arguments, the containers for the columns to mash, and returns a new column, already mashed. So let's run through it. First we want the last name and the column of separators. So we call:

put DBColumnMash(gLAST,filler) into mash

The variable mash now contains:


Now we want to add the first names. So we take our already mashed column, and mash onto it the first names:

put DBColumnMash(mash,gFIRST) into mash

The variable mash now contains:


Now it's time for the next column of dividers, which is done by mashing the filler onto the end of the mash column:

put DBColumnMash(mash,filler) into mash

The variable mash now contains:


And finally, we add the phone numbers:

put DBColumnMash(mash,gPHONE) into mash

The variable mash now contains:


Obviously, you're going to want to keep track of the size of your columns and the total variable sizes, so that you don't try building a table that's too big to fit into the display variables.

Tutorial: Building HTML Tables

You can use the table tools to build more complex tables. Here's how to use the various table tools to turn the previous example into an HTML table. Let's assume that DBGetMatchList has already been called and the variables gFIRST, gLAST, and gPHONE have been created. Because HTML doesn't require padded strings, DBSuperPadString doesn't need to be called.

In HTML, a table begins with a <TABLE> directive and ends with a </TABLE>. Each row starts with <TR> and ends with </TR>. And each cell begins with <TD> and ends with </TD>. So, the first thing we want to do is create a couple of fill columns. We're going to need a set of <TR>, followed by <TD> at the very beginning of the table:

put DBFillColumn("<TR><TD>",5) into leftSide

We'll need to close out the rows and cells at the end of the table (remember that you reverse the HTML tag order):

put DBFillColumn("</TD></TR>",5) into rightSide

In between the columns, we'll need to close the open cell (the one opened earlier), and open a new cell:

put DBFillColumn("</TD><TD>",5) into middleSep

Next, we mash together the columns:

put DBColumnMash(leftSide,gLAST) into mash
put DBColumnMash(mash,middleSep) into mash
put DBColumnMash(mash,gFIRST) into mash
put DBColumnMash(mash,middleSep) into mash
put DBColumnMash(mash,gPHONE) into mash
put DBColumnMash(mash,rightSide) into mash

Finally, we add the mashed columns to the opening <TABLE> directive and close out the table:

put "<TABLE>" & return & mash & return & "</TABLE>" into vTable

HTML doesn't really require return characters, but it makes the subsequent HTML much easier to read. Here's what the final vTable variable contains:


Discuss this chapter on the FileFlex Boards.

Copyright © 1992-1998 by David Gewirtz, under license to Component Enterprises, Inc.

Casa de Bender