ViaFox - A Compact Cursor—Ideal for Displaying Multiple Addresses

This article was originally published in Foxtalk, december 2001.

A Compact Cursor—Ideal for Displaying Multiple Addresses

Copyright Viafox

These days, it's common for people to have multiple phone numbers and other kinds of addresses. In the old days, most people had only one or two phone numbers, and exceptional people (the eccentrics) who had more phone numbers weren't really handled within our databases. We thought we couldn't, or we didn't want to, handle those eccentrics in the databases we built for our customers. For example, we created databases with at most two fields for phone numbers. People were happy with the fact that we could automate some things, and customers didn't really complain about the restrictiveness for a long time. But those days are over...

These days, not only do people have an ever-increasing number of addresses (where and how they can be reached), but the customer also wants to store and retrieve those multiple addresses. We can no longer use multiple fields; rather, we put those addresses in a separate table (table being the new word for the old database), and each address takes up a separate record in that table. The Condes Address Model that I developed is a clear example of such a structure (see my related article in this issue, "Introduction to the Condes Address Model"). That model not only describes the way the database should be structured, but also the functionality that classes and forms should support and the procedures that users, who have to enter and change addresses, should adopt.

This article explains a technique that can be used to display multiple addresses. For example, how do you display all addresses of a certain type on screen? And how do you display them on a report? It isn't easy! Anyone who's tried will know what I'm talking about. Let's stick to the burden of displaying them on a report. The problem is that there's only a single Detail Band available in a FoxPro report. So, if you're lucky, you can use that Detail Band to display a person's multiple street addresses. But if you're not lucky, you're also asked to display the multiple phone numbers that this person has. Oops, that means a shortage of one Detail Band already. What to do now?

Use my CompactCursor() function! This function will produce a "compact" cursor, the solution to the problem described in the preceding paragraph. And you can use it for free.

What's a compact cursor? A compact cursor is a cursor that takes as input another cursor or table, and puts information that's scattered over one or more records in the original cursor/table into one record in a newly created cursor—the "compact" cursor. The gathering of several records into one record is determined by the value of an ID field. All records in the original cursor/table that have the same value in the ID field are the candidates for the gathering. Look at the following scheme (assume it's a normal cursor):

ID	StreetAddress
00125	M. Emantsstr. 2
01023	Sesamestreet 32
01023	Wall Street 1
02318	Abbey Road 3
01023	42nd Street 21

This original cursor has three records with the same ID 01023—let's say a person's unique key. So this person has three addresses. In the compact cursor, there's only one record for this person, and there will be one memo field besides the ID field:

ID	StreetAddress
00125	Memo
01023	Memo
02318	Memo

What do you think is in the memo field? Right—the street addresses, directly below each other! So the memo of 01023 will read:

Sesamestreet 32
Wall Street 1
42nd Street 21

Do you get the picture? Then it's only a small step to understanding that displaying this cursor no longer requires multiple Detail Bands.

You might do the same with phone numbers. Let's suppose there's another cursor with these phone numbers:

ID	Phonenumber
00125	02-233423445
01023	09-234230001
01023	004-45345545 begin_of_the_skype_highlighting 004-45345545 end_of_the_skype_highlighting
02318	0292-22343444
01023	-

CompactCursor() would create the next cursor:

ID	Phonenumber
00125	Memo
01023	Memo
02318	Memo

A normal Select-SQL, with some outer join statements, is enough to create a cursor that has all of the required info about the persons.

ID	Surname	StreetAddress	Phonenumber
00125	BeGood	Memo	Memo
01023	Pietersen	Memo	Memo
02318	Fa Leng	Memo	Memo

Now the solution is complete, and you'll even think of other possible usages. For example, it's ideal for displaying multiple From-Thru dates, as you can see in Figure 1, where the first person needs more lines.

Figure 1 - Displaying multiple From-Thru dates.

The funny thing is that the memo fields and the name field are on the same line in the report. Make the memo fields stretchable, and they'll display all dates perfectly.

Today the need for multiple addresses is real. The function I wrote does a good and fast job. If I had my druthers, I'd like the VFP development team to add this capability to the native product—they can deliver unmatchable speed and ease of implementation. I guess there could be a keyword added to the Select-SQL command, but there's an international committee that will inhibit such "abuse." So I propose a separate command inspired by the Select-SQL command. Here's an example that demonstrates the capability I'd like:

COMPACT SELECT ;
   a.ID, ;
   a.Surname, ;
   b.StreetAddress, ;
   c.Phonenumber ;
   FROM person a, streetaddress b, phonenumber c ;
   WHERE a.ID = b.ID
   AND   a.ID = c.ID ;
   GROUP BY 1 ;
   ORDER BY 1 ;
   INTO CURSOR c_person

The GROUP BY expression is the equivalent of the field that CompactCursor() regards as the ID field. And no matter what datatype the other fields originally have, they'll be of type Memo in the resulting cursor. (So that also accounts for Surname in the preceding example.) Other solutions are certainly possible—this was only a first move.

Reference
Okay, now let's see how the CompactCursor() function works:

• It takes its input from a table or cursor.

• On exit, the new compact cursor will be the "currently" selected cursor.

• The From table or cursor typically has several records that share the same value in an ID field in those same records.

• The first field in the From table/cursor will be regarded as the ID field.

• The n-found values found in an equal number of records in the From table/cursor will be on an equal number of lines in the memo field, unless you use the sixth parameter.

Here's the syntax:

lOK = CompactCursor( cFrom, cIntoCursor, cDoFields, cFormatCodes, lTrim, lNotIfEmpty, cThermo )

All parameters are optional and are described here:

• cFrom—This parameter should be an existing table or cursor. If it's not yet open, it will be opened and stay open (just like a Select-SQL would do). It defaults to the current ALIAS(). The first field in this table/cursor will be regarded as the ID field.

• cIntoCursor—This parameter should be a valid name for a compact cursor. It defaults to the name of the From table/cursor with the addition of "Compact."

• cDoFields—This parameter should contain a comma-delimited list of the field names that must be processed. It defaults to all fields (except the ID field, of course).

• cFormatCodes—The TRANSFORM() function is used for the conversion of the fieldvalues so that they fit into the memo field. This parameter will be passed to that function as FormatCodes, if specified. So, read the Help text on that function to check what the parameter should look like in your case.

• lTrim—Pass .T. if you want to trim leading and trailing spaces.

• lNotIfEmpty—Pass .T. if you want to "skip" records that have an empty field. Thus you'll be able to avoid empty lines in the memo. The default is that such empty fields aren't skipped—a situation that's required if there are, for instance, two fields in which even empty lines have meaning. An example is a From Date memo field and a To Date memo field.

• cThermo—Do you want to inform your users about the progress the routine makes? Pass the name of your progress bar/thermometer function. It will be executed with macro substitution for each record in the created compact cursor. At that moment the total number of records will already be known, so you can use both recno() and reccount() in your function.

The function does return a Boolean value that indicates whether or not it was successful in creating a compact cursor.

Following are some important notes:

• The parameter cFormatCodes is used for each processed field. This may pose a problem if some of the fields actually need different formatting. In that case, you should call the function two or more consecutive times, each time processing only a subset of the fields, by specifying cDoFields. This enables you to specify a different cFormatCodes for each subset. The compact cursor will be created on the first call, and it will also receive all records in that first call. The additional calls will only process the additionally declared fields.

• The routine hardly checks for error-causing conditions. You're supposed to understand what the routine does (RThisFM) and thus be able to debug the cause of an error.

• The resulting compact cursor will have an index tag PRIMARY based on the ID field.

Examples
And, finally, here are some examples. The first one demonstrates the basic principle and the use of format codes.

select ;
   city as id, ;
   padr(alltrim(address)+' '+number,50) as address,;
   amount ;
   from address ;
   where recno() < 6000 ;
   into cursor c_adr nofilter

CompactCursor('c_adr','c_adrX','address')
CompactCursor('c_adr','c_adrX','amount','999,999.99')

browse

In the next, more realistic, example, the street addresses of companies are in a separate table StreetAddress, to allow for storing eventual extra street addresses. Each record also has the ID of the owning company. The first statement creates a cursor with a record for each full address. Then, CompactCursor() uses this cursor to create a compact cursor. Finally, the info in the Company table is put into a cursor that's extended with a memo field that contains all street addresses of a company.

select ;
   a.CompanyID, ;
   alltrim(a.cStreet)+' '+alltrim(a.cNumber)+CRLF+ ;
   alltrim(a.cPostalCode)+' '+alltrim(a.cCity)+CRLF ;
                                 as mStreetAddresses ;
   from StreetAddress a ;
   into cursor c_StrAddress0

CompactCursor('c_StrAddress0','c_StrAddress',,,.t.)

select ;
   a.*, ;
   b.mStreetAddresses ;
   from Company a left outer join c_StrAddress b ;
   on a.CompanyID = b.CompanyID ;
   into cursor c_SelectedCompany

Okay, enough said. Go ahead and download the function, which is packed together in the Download file for my Condes Address Model article in this issue or available on my Web site, and give it a try!

Home