Tilde in Expression

[twebb]

I am trying to reverse "lastname~firstname" to "firstname lastname" in a single field (continuation of a thread from yesterday). I know I can make 2 fields, but if I can get this to work, I'd rather go with it. I also know I can change the tilde to a comma space, but our A4 database is already setup with the tilde (we're transitioning to Alpha 5).

The following works with a comma, but if I substitute a tilde, it doesn't. Or if it does, it does so one time. Then the operation files disappears, or part of the script vanishes, or I just get an error.

if(AT(",", CONAME, 1)>0,SUBSTR(CONAME, AT(",", CONAME, 0)+1, (AT(",", CONAME, 1)-AT(",", CONAME, 0))-1),CONAME)

Anyone know why a comma would work and not a tilde? Or?????

[Stan Mathews]

I am trying to reverse "lastname~firstname" to "firstname lastname" in a single field (continuation of a thread from yesterday). I know I can make 2 fields, but if I can get this to work, I'd rather go with it. I also know I can change the tilde to a comma space, but our A4 database is already setup with the tilde (we're transitioning to Alpha 5).

The following works with a comma, but if I substitute a tilde, it doesn't. Or if it does, it does so one time. Then the operation files disappears, or part of the script vanishes, or I just get an error.

if(AT(",", CONAME, 1)>0,SUBSTR(CONAME, AT(",", CONAME, 0)+1, (AT(",", CONAME, 1)-AT(",", CONAME, 0))-1),CONAME)

Anyone know why a comma would work and not a tilde? Or?????

Try a simpler expression

 fld = "lastname~firstname         "
? word(alltrim(fld),2,"~")+" "+word(alltrim(fld),1,"~")
= "firstname lastname"

[CALocklin]

twebb - I'd think very carefully about that. If one of my customers asked me to do that, I'd pretty much refuse to do it. In fact, I'd be thinking about finding a 4x4 to smack them over the head with. The only thing I would consider is to put them in separate fields now while I'm making changes.

"I just want to do the easiest thing works for now." -- I've been down that road far too many times. It usually ends up creating HUGE problems down the road that are even harder to fix - especially in the case of name fields. And, of course, nobody notices until it's a panic that needs to be solved yesterday. Combining separate fields into one for display purposes is a no-brainer - difficulty level of 1 on a 1-10 basis. Separating name fields into prefix (Mr., Dr., etc.), first, middle, last, suffix/title is a difficulty level of about 12 on the same scale. In other words, it usually can't be done accurately.

If you have one name field it's just about guaranteed that you will end up with name structures like Mr. Joe Btfsplk; Sally Forth, D.D.S.; M.K. Somebody; M. K. Somebodyelse (spaces between initials); Sally Forth Smith (compound last name); Fred M Nobody; and many others. Unless you are the only person who ever enters data in this database, there is no way you will get others to "follow the rules." Now, let's say we decide that the 2nd "word" is always the middle initial/name - so, in this case we have middle initials/names of Joe, Forth, K, K, Forth, and M. If the 3rd word is the last name, the last name of Sally Forth, D.D.S. is "D".

[twebb]

Thanks, Cal. We only use first and last names here (no prefixes, suffixes or middle initials) and use Alpha strictly as a database for domestic addresses for mailings. We underutilize it terribly (but I'm working on that!). We've used Alpha for years and years and years and apparently at some point way back when the tilde actually automatically reversed the two fields. The tilde has worked perfectly in Alpha 4 for a decade, but we're due for updating and it looks like it's time to do it!