Contact Name Field to ContactLast and ContactFirst fields

[puppy39]

I have a inherited a fox pro 9.0 database where the developer instead of creating Contact Last Name and Contact First Name fields they created only Contact Name.

Is there function that I can use where it searches for the blank space in order to split the contact name? Can I then add a replace code to copy the information to the 2 new fields I just created?

I can do this with other programs but I can't figure out how to this in fox pro. Thanks!

 

[Mike Lewis]

Yes, this is easy:

lcFullName = "Robin Hood"
lcFirstName = GETWORDNUM(lcFullName, 1) 
lcLastName = GETWORDNUM(lcFullName, 2)

Of course, it falls down if there are three names, or some other punctuation between the names. But the above will solve the problem as you defined it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Just to elaborate a bit on my previous answer ....

If the full name contains multiple names (e.g Edna St Vincent Millay), the following will correctly give you the first and last names:

lcFirstName = GETWORDNUM(lcFullName, 1)
lcLastName = GETWORDNUM(lcFullName, GETWORDCOUNT(lcFullName))

Of course, that still might not be what you want, but I guess it would cover the majority of cases.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

alter table myTable ;
 add column firstName c(20) ; 
 add column lastName c(20)

update myTable set ;
 firstName = substr(contactName,1,rat(' ',trim(contactName))) ;
 lastName = substr(contactName,rat(' ',trim(contactName))+1)

Cetin Basoz
MS Foxpro MVP, MCP

 

[puppy39]

Thank you soooo much. Couple of questions:

Can I force the code to stop if let say the contactname field has Dr. Michael Smith Jr. and just leave lastname and firstname blank. What I am looking for is quick fix that will take care of 80% I can manually change the other 20% who knows how many variation this system might have.

Do I have to create a .prg file in order to run this code or can I run this from the command window.

Once again Thank you!

 

[cbasoz]

You mean skip records where lastname is Jr? or has title like Dr, Mr, Mrs,Miss?
(I think you have already done alter table)

Update myTable Set ;
  firstName = Substr(contactname,1,Rat(' ',Trim(contactname))) , ;
  lastName = Substr(contactname,Rat(' ',Trim(contactname))+1) ;
  where RegularName(contactname)

Function RegularName(tcName)
  Return !( Alines(laLines, Lower(Trim(m.tcName)), 1, ' ','.') > 1 And ;
    (Inlist(laLines[1],'dr','mr','mrs','miss') Or ;
    laLines[ALEN(laLines)]='jr') )

This one you can't (well you can but consider can't) run from command line but there is an easy way. In command window type:

modi comm[enter]

In code window that pops up paste the code and correct layout (it doesn't get copied as you see it). Select all the code, right click and "Execute selection". Then you may dismiss the code pressing Esc.


Cetin Basoz
MS Foxpro MVP, MCP

 

[TamarGranor]

Another option is for you to decide that you'll automate those names with two words and leave all the longer ones for individual attention:

REPLACE FirstName WITH GetWordNum(ContactName, 1), ;
LastName WITH GetWordNum(ContactName, 2) ;
FOR GetWordCount(ContactName) = 2

Tamar

 

[Mike Lewis]

Do I have to create a .prg file in order to run this code or can I run this from the command window.

In general it's easier to create a PRG. The chances are that you won't get it 100 percent right first time, and you'll want to tweak the method as you go along. This is easier with a PRG. Also, the PRG is a permanent record of what you've done, which you'll find useful if you need to solve the same problem again some time.

As for "stopping" when you hit a non-standard name: It's better to flag these in some way for you to review later.

I'd suggest you put a separate field in your table, called, say, Flag. Make this a logical field. Your PRG will set this to .T. for each record that is to be reviewed manually.

If you define these "manual review" cases as names containing more than two words, the entire code would be something like this:

REPLACE ALL ;
  FirstName WITH GETWORDNUM(ContactName, 1), ;
  LastName WITH GETWORDNUM(ContactName, 2) ;
  Flag WITH (GETWORDCOUNT(ContactName) > 2)

To review the manual cases, you could then do this:

BROWSE FIELDS ContactName, FirstName, LastName ;
  FOR Flag

This will bring up a Browse window which will show the contacts in question, and let you edit them as you see fit.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[craigsboyd]

Automation of Outlook is another option when parsing names...

faq184-5790

SweetPotato Software Website
My Blog