Contents of the LETTER.DOC file
Once upon a time, creating pop-up pick lists was a tedious
task. You had to select the file, define the fields that would be
shown, control what each keypress would do. It took even more pains
to define the labels that would appear over the fields and format
calculated fields if you used them. Unless you took the time to do
it properly, you had to create a new routine for each pick list.
With Foxpro's BROWSE command, these routines can now be created in
a matter of minutes. However, even this command did not do
everything. It let users select a record by using the positioning
keys (arrows, PgUp, PgDn), but it did not allow them to enter a
value. If the data file contained thousands of records and the one
desired was somewhere in the middle, the user would still have to
press the PgDn key many times. Y. Alan Griver showed us how to use
the On Key Label function to enter values during a browse. I have
used his concept here and included additional features such as:
1) making it a generic routine, callable with a few lines of code
from any program
2) allowing the use of files which are non-indexed, indexed or
indexed on multiple fields
3) having it work identically on character, numeric or date
fields. It will search from the leftmost characters entered. A 7
will find the first search field beginning with 7. (7, 70-79,
700-799). Naturally, this search will be slower than an indexed
4) allowing the user to add a record to the file, if the
programmer permits it.
5) display of the search key being built and the field being
located for that value
6) selecting multiple records with one call to Browze
7) allowing the search to work on any field in the browse that
belongs to the active database. If the user moves the cursor over
to the next field, (and that field exists in the active database)
any previously entered search value is cleared and the locate for
the new search value entered will take place against that field.
However, only the first field in the browse list can be SEEKed.
This feature must be disabled if the browse uses the key clause,
where only records for which the index key matches a specific value
are shown. The reason for this is that the locate might match a
record with a different index value. Since the located record would
not appear in the browse, it would confuse the user.
I use this routine in all my programs to allow the user to
select records from a pick list. The program BROWTEST demonstrates
various uses of BROWZE including using it to select multiple records.
By passing an third parameter of .t., the return key no longer selects
the record and exits, it selects the record, moves down to the next
record and waits for another record to be selected, the ESC key to be
pressed to cancel or F2, signalling the end of the selection process.
Pressing return also resets the search key to blank, so that it is ready
for the next search value.
Free-Form Letters with Data Fields
Foxpro's powerful use of the memo field lets the user add
free-form text to any data record. However, most programmers still
come up against certain shortfalls when trying to allow their
clients to send out customized letters. The commonly used approach
is to extract the desired data records to an ASCII or SDF file and
then import it into a word processor. This is tedious. Even this
approach falls short if data from several files is needed. The
programmer would have to create some intermediate file which has
all the fields required, then extract the data into it, then copy
this to the other format and import it into the word processor.
With Foxpro's power, there had to be a better way.
The MAKELETR program uses a file called WORKLETR to hold 1)
a letter template memo field (workletr), 2) a finished letter memo
field complete with data fields (actual) and 3) a description of
the letter template so it can be selected for re-editing and use.
The user selects the letter to edit (with the BROWZE routine) or
creates a new letter. Data fields are entered into the template by
inserting # followed by the code corresponding to the field
desired. The fields can come from any open file and can even be
edited results of field manipulation. The file FIELDLST contains a
record for each field which can be included. It is composed of:
1) PROJECT: the name of the project for which this field is
available. If the user is designing billing letters, the project
might be called billing. He will want to see only fields that are
concerned with billing. If the user is designing "Welcome to the
Club" letters, he will want to see only information that might be
included in such letters. The PROJECT field limits the fields which
the user will see while editing the letter.
2) FIELDNO: the character which will be replaced by the actual
data. #A is replaced by the field that has A as the fieldno. (The
index for this file is PROJECT+FIELDNO).
3) DESC: a description of the field for the user.
4) FEELD: the Foxpro command which will yield the data. If the
field is a straight data field, this might be
trim(filename->fieldname). Date and numeric fields must be
converted to character, because the letters are built by adding the
user's text to the data.
After the user selects the letter to edit, he is presented
with a memo editing window. Above the window he can see the first
few fields available for inclusion. Pressing F1 reveals the entire
help screen, so all fields and instructions are shown. The edit can
be canceled by pressing ESC. If F2 is pressed, the memo is saved
and a second window appears showing a preview of the letter with
the variables replaced by the actual data. The user can press ESC
to return to the editing screen and fix the letter or F2 to exit
and save the edited letter.
The next logical step for the programmer would be to allow
the user to enter criteria for record selection (such as all people
of a certain age) or allow the user to select the records using the
code found in MULTSEL and then print the letters.
Because I ran into a problem with Foxpro dropping off blanks
and carriage returns between lines, I had to use ~ to indicate that
a hard return be inserted and I had to have the program insert a
space at the end of each line.
I am sure that once some of you power users start playing
with this, you will find ways to make it faster, easier and more
powerful. I would appreciate hearing from you.
(Yisroel Goodman is Complete Computer Services, Inc. He lives
and works in Far Rockaway, NY. When not designing and coding, he is
working on other projects, such as Shayna and Dani. He can be
reached at (718) 868-3000 or on Compuserve 70243,443)