Manipulating Names
by Peter Rorlick [dBVIPS], co-founder of  Montreal Business Software. He has been developing xbase solutions since 1981.
IF YOU'VE DEVELOPED database applications, then most likely you've dealt with the issue of getting people's names into and out of a database.  User names, customer names, contact names - these are things that are commonly found in the applications we create.

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: A new approach

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?

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)