Hi, am completely new to FM and to databases so please go easy.....

I have the following fields in a simple database that will be used to generate an email and a physical mailshot:

Contact First

Contact Last

mail_front

URL

mail_front is a value list consisting of the following options:

firstname.surname

intial.surname

firstnameintialofsurname

intitalsurname

firstname

firstname_surname

and URL is the part of the email address after the "@" sign.

I need to create a new field that takes the neccessary data from the Contact First and Contact Last fields to make the correct email address up for each record.

I hope this makes sense. I presume one could write a big long nested IF statement that would do this, but I'm afraid I am completely overwhelmed.....

Or is this something better done with a Replace?

any gentle hints?

cheers

You can use a case statement to create the email address like this:

case(

mail_front="firstname.surname",Contact First &"."& Contact Last,

mail_front="intial.surname",Left(Contact First,1)&"."&Contact Last,

mail_front="firstnameintialofsurname",Contact First & Left(Contact Last,1),

mail_front="intitalsurname",Left(Contact First,1)&Contact Last,

mail_front="firstname",Contact First,

mail_front="firstname_surname",Contact First &"_"& Contact Last,

"Error")&"@"& URL

However, for a more general approach, I would prefer to use value list items of the form:

<First><Last>

<First>

<First>.<Last>

<First>_<Last>

<FirstInitial><Last>

<FirstInitial>_<Last>

etc.

and then use the following calculation like this:

Substitute(Substitute(Substitute(Substitute(mail_front

,"<First>",Contact First)

,"<FirstInitial>",Left(Contact First,1))

,"<Last>",Contact Last)

,"<LastInitial>",Left(Contact Last,1)) & "@" & URL

This is more versatile since it will work with any combination of First and last names and initials with any type of punctuation that you might encounter.

[ December 15, 2001: Message edited by: BobWeaver ]

thanks Bob,

once I'd figured out to replace the commas with semi-colons, it worked a treat. Once I get some spare time I'll try out your suggestion of the value list & Substitute function - I can see that this is a more robust solution.

Many thanks

James