Usually, when people's names are stored in a table, they're stored in two fields, something like this:
We can certainly live with that scheme, but it comes with two drawbacks:
Field name Type Length FirstName Character 20 LastName Character 20
What if we were to store our names in a single field:
...where they would be stored in the format: LastName, FirstName?
Field name Type Length Name Character 25
What advantages does this give us?
— The names look great in a grid or report (or even if you just BROWSE the table), without any fiddling. They show up in a single, easy-to-read column.
— A Seeker-ready index tag is now
built simply by:
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.
— Disk space is conserved - a significant benefit if your table will contain a lot of names. A savings of 15 bytes per record means that you'll conserve 15 megabytes for every million records.
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
with (this)
left = 3
top = 2
width = 55
height = 3
metric = 0
// Chars
borderStyle
= 3 // None
endwith
this.TEXT1 = new TEXT(this)
with (this.TEXT1)
height = 1
left = 0.85
top = 0.3636
width = 14.8571
alignVertical
= 1 // Middle
text = "Last
name"
pageno = 0
endwith
this.EFLASTNAME = new NameENTRYFIELD(this)
with (this.EFLASTNAME)
left = 0.85
top = 1.3182
onChange =
class::GoIntoEditMode
endwith
this.TEXT2 = new TEXT(this)
with (this.TEXT2)
height = 1
left = 25.2857
top = 0.3636
width = 14
alignVertical
= 1 // Middle
text = "First
name(s)"
pageno = 0
endwith
this.EFFIRSTNAME = new NameENTRYFIELD(this)
with (this.EFFIRSTNAME)
left = 25.2857
top = 1.3182
onChange =
class::GoIntoEditMode
endwith
function onOpen
if type('this.rowset')
# 'O'
// If
the Name field is in a rowset other than Form.rowset, then
// the
container's custom Rowset property should be assigned in
// the
form's onOpen event.
//
// If
the custom property does not exist at this point, we
// assume
that Form.rowset contains our "datalink":
this.rowset
= form.rowset
endif
this.ExtractFirstAndLastNames()
// Create 2 custom
properties, to make it easy to reference the
// entryfields
in the rowset event handlers:
this.rowset.efLastName
= this.efLastName
this.rowset.efFirstName
= this.efFirstName
this.rowset.onSave
:= class::SaveFirstnameAndLastnameToName
this.rowset.onNavigate
:= class::ExtractFirstAndLastNames
this.rowset.onAppend
:= class::ExtractFirstAndLastNames
return
function ExtractFirstAndLastNames
//
// The Name,C,25
field is formatted (for example) as "Smith, John".
// In our forms,
we split the name into efFirstName and
// efLastName
nondatalinked entryfields.
//
// Set up the
values for the two entryfields:
local nCommaPosition,
cName
if this.className
== 'ROWSET'
// We're
in the rowset's onNavigate event, so THIS is the rowset.
cName
= this.fields['Name'].value
else
// THIS
is the container
cName
= this.rowset.fields['Name'].value
endif
if cName == null
this.efFirstName.value
:= ''
this.efLastName.value
:= ''
return
endif
nCommaPosition =
at(',',cName)
if nCommaPosition
== 0
this.efFirstName.value
:= ''
this.efLastName.value
:= cName
else
this.efFirstName.value
:= subs(cName,nCommaPosition+2)
this.efLastName.value
:= left(cName,nCommaPosition-1)
endif
return
function SaveFirstnameAndLastnameToName
// The opposite
of ExtractFirstAndLastNames().
// This method
will become the rowset's onSave event handler.
// THIS is a
rowset
this.fields['Name'].value
:= ;
properName( trim(this.efLastName.value) + ', ' + ;
this.efFirstName.value )
this.save()
return
function GoIntoEditMode
// This is the
onChange for the Name entryfields.
// The purpose
here is to ensure that the rowset is in Edit mode.
this.parent.rowset.fields['Name'].value
+= ''
return
endclass
****************************************
Class NameEntryField(parentObj) of ENTRYFIELD(parentObj)
custom
with (this)
height = 1.2
width = 22
picture =
"!XXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
colorNormal
= "blue/W+"
fontSize =
14
fontBold =
false
value = "
"
maxLength
= 30
pageno = 0
endwith
endclass
*****************************************
function ProperName( cName )
// Purpose: Format
names properly
// Usage: ? ProperName("MICHAEL
MACLEOD")
//
(returns "Michael MacLeod")
// Contributing authors:
Peter Rorlick, Dan Howard, Brian Kavanaugh.
//
local TempString, NewString,
EvalChar, mCount, Separators, OldString, CapNext
Separators = " /-'<>=*+."
// add final space to make
&& routine easier to manage
OldString = ltrim(rtrim(cName))
+ " "
NewString = ""
TempString = ""
CapNext = true
for mCount = 1 to len(OldString)
EvalChar = subs(OldString,
mCount, 1)
if EvalChar $ Separators
// end of word
CapNext
= true
NewString
+= TempString + EvalChar
TempString
= ""
elseif CapNext
// initial character of a word
TempString
= upper(EvalChar)
CapNext
= false
else
// character w/i word
evalchar
= lower(evalchar)
TempString
+= EvalChar
if TempString
= "Mc" or TempString = "Mac"
CapNext = true
NewString += TempString
tempstring = ""
endif
endif
next
return NewString
// 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 Pallette, drop a NameContainer onto the form. Now, from the Field Pallette, 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')
create table people ( Name char(25),
Telephone char(15) )
use people
append blank
replace Name with 'Smith, John', Telephone
with '123-4567'
append blank
replace Name with 'Jones, Mary', Telephone
with '433-0000'
use
endif
******************
** END HEADER -- do not remove this line
//
// Generated on 13/10/1999
//
parameter bModal
local f
f = new People2Form()
if (bModal)
f.mdi = false
// ensure not MDI
f.readModal()
else
f.open()
endif
class People2Form of FORM
set procedure to Name.cc additive
with (this)
scaleFontBold
= false
metric = 6
// Pixels
height = 224
left = 119
top = 20
width = 463
text = "Test
form for the NameContainer class"
autoCenter
= true
sizeable =
false
endwith
this.PEOPLE1 = new QUERY()
this.PEOPLE1.parent = this
with (this.PEOPLE1)
left = 389
top = 157
sql = 'select
* from "PEOPLE.DBF"'
active = true
endwith
this.NAMECONTAINER1 = new NAMECONTAINER(this)
with (this.NAMECONTAINER1)
left = 2
top = 2
width = 327
height = 50
endwith
with (this.NAMECONTAINER1.TEXT1)
height = 16
left = 7
top = 3
width = 83
colorNormal
= "BtnText/BtnFace"
fontSize =
8
fontBold =
true
endwith
with (this.NAMECONTAINER1.EFLASTNAME)
height = 20
left = 6
top = 20
width = 154
fontSize =
10
fontBold =
true
value = "
"
maxLength
= 30
endwith
with (this.NAMECONTAINER1.TEXT2)
height = 16
left = 165
top = 3
width = 98
fontSize =
8
fontBold =
true
endwith
with (this.NAMECONTAINER1.EFFIRSTNAME)
height = 20
left = 163
top = 20
width = 157
fontSize =
10
fontBold =
true
value = "
"
maxLength
= 30
endwith
this.TEXT1 = new TEXT(this)
with (this.TEXT1)
height = 16
left = 337
top = 5
width = 84
fontSize =
8
fontBold =
true
text = "Phone"
endwith
this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
dataLink =
form.people1.rowset.fields["Telephone"]
height = 20
left = 336
top = 22
width = 116
fontBold =
true
endwith
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink =
form.people1.rowset
bgColor = "white"
cellHeight
= 18
allowEditing
= false
allowAddRows
= false
height = 156
left = 8
top = 55
width = 445
endwith
this.RECTANGLE1 = new RECTANGLE(this)
with (this.RECTANGLE1)
left = 8
top = 57
width = 443
height = 1
text = ""
colorNormal
= "black/black"
border = false
fontSize =
8
fontBold =
true
borderStyle
= 3 // None
endwith
this.rowset = this.people1.rowset
endclass
// End of People2.wfm
To
download the code shown in this article, click
here
(it's
a 33Kb zipped executable file)