Usually, when people’s names are stored in a table, they’re stored in two fields, something like this:
Field name | Type | Length |
FirstName | Character | 20 |
LastName | Character | 20 |
We can certainly live with that scheme, but it comes with two drawbacks:
What if we were to store our names in a single field:
Field name | Type | Length |
Name | Character | 25 |
…where they would be stored in the format: LastName, FirstName?
What advantages does this give us?
Index on upper(Name) tag Name |
|
Compare this
to the tricky index expression we’d have to use if names were stored in
two fields:
Index on left(upper(trim(LastName)+','+FirstName)+space(25),25) tag UpperName |
|
The simpler index expression allows dBASE to build and update the index more efficiently, and the expression is also easier to read and understand. And you’re less likely to get it wrong when you create the index.
Most often, when a person reads
the data, [last name] and [first name] are thought of as a single data
item — and our new approach conforms nicely to that thought process, because
the raw data is already formatted the way people typically want to see
it. For the rare cases where just the last name or just the first
name is needed, the presence of the comma makes it easy for us to extract
one or the other:
nCommaPosition = at(',',Name) cLastName = left(Name,nCommaPosition-1) cFirstName = subs(Name,nCommaPosition+2) |
|
But...
Most data entry programs have separate
entryfields for entering first and last names. Certainly this
is a good idea; we can’t expect users to consistently type names into a
single entryfield in our desired format:
Surname first...
...then a comma...
...then a space...
...and finally
the first name.
Any errors on the user’s part, and we end up with bad data. So we want (and the user will expect) to be able to enter names into separate First Name and Last Name entryfields, and we must somehow convert the data when it’s being read from and saved to the table.
How can we accomplish that? It’s pretty simple, really. All we have to do is not datalink the two entryfields, and make sure that they get filled with appropriate values when the row is read, and that the value of the Name field gets written properly when the row gets saved.
Here’s a custom container class,
written in Visual dBASE 7.01, that automatically takes care of all of this:
// Beginning of Name.cc class NameContainer(parentObj) of CONTAINER(parentObj)
custom
this.TEXT1 = new TEXT(this)
this.EFLASTNAME = new NameENTRYFIELD(this)
this.TEXT2 = new TEXT(this)
this.EFFIRSTNAME = new NameENTRYFIELD(this)
function onOpen
function ExtractFirstAndLastNames
function SaveFirstnameAndLastnameToName
function GoIntoEditMode
endclass
// End of Name.cc |
|
To use this custom class, save
the code above to Name.cc. Then, in the Command window, type:
set procedure to name.cc additive create table people ( Name char(25), Telephone char(15) ) use people append blank replace Name with 'Smith, John', Telephone with '123-4567' modify form people |
|
Drag People.dbf from the Tables tab of the Navigator onto the Form Design surface, then from the Custom tab of the Component Palette, drop a NameContainer onto the form. Now, from the Field Palette, drop the Telephone field onto the form. Press F2 to run the form.
Below is another form you can run, to see how the container works. Save the code below to People2.wfm, and run it. Edit the names, move around in the grid, and you’ll see how it performs.
// Beginning of People2.wfm
if not file('People.dbf')
class People2Form of FORM
this.PEOPLE1 = new QUERY()
this.NAMECONTAINER1 = new NAMECONTAINER(this)
with (this.NAMECONTAINER1.TEXT1)
with (this.NAMECONTAINER1.EFLASTNAME)
with (this.NAMECONTAINER1.TEXT2)
with (this.NAMECONTAINER1.EFFIRSTNAME)
this.TEXT1 = new TEXT(this)
this.ENTRYFIELD1 = new ENTRYFIELD(this)
this.GRID1 = new GRID(this)
bgColor = "white"
this.RECTANGLE1 = new RECTANGLE(this)
this.rowset = this.people1.rowset endclass // End of People2.wfm |
|
To
download the code shown in this article, click
here
(it’s
a 3Kb zipped file)