Help with Naming calculation

[fbugeja]

I have just finished a database for school use. The administrative staff have asked to include an emailing option to automatically send emails to students.

Each student's user name for their email is simply the first letter of their Christian name plus their surname. "Mick Fleetwood" would have the user name: mfleetwood. In our school email system. it is only necessary to type in the username (e.g. mfleetwood) to send an email to the student's school email address.

Could someone help me with a calculation that would take the student's full name from the Name field and put it in another field (a calculation field) as described above.

This was my effort to date:

Left ( Name; 1) & Middle ( Name;2;MiddleWords (Name ; 2 ; 1 ))

Thanks for the help

Frank

[J__]

if the persons name is "Stevie Nicks" , then you want snicks right?

so if the field is called "name", then left (Name;1) gets you the S

then you want to find the space between their first and last name

the Right Function can help you here.

ex: Right(Name;Length(Name) - Position(Name;" ";1;1))

this gets you "Nicks"

So putting it all together you would have something like this

Left (Name; 1) & Right(Name;Length(Name) - Position(Name;" ";1;1))

and that should give you "SNicks"

but lowercase would be preferable, so just use the Lower() function.

lower( Left (Name; 1) & Right(Name;Length(Name) - Position(Name;" ";1;1)) )

create a calculation field, maybe called email, and for the calculation put that in there.

of course, this doesn't address middle initials or does it? Nope doesn't. Although I'm sure some folks in this newsgroup could provide a slicker solution, here's one that seems to work. Test it out, ok? I just threw it together... and did some basic testing

Let (

[ numSpaces = PatternCount(full_name; " ") ] ;

Lower ( Left ( full_name; 1) & Right ( full_name;Length(full_name) - Position(full_name;" ";1;numSpaces) ) )

)

some details probably are in order. First, Let() is a function (?) which allows you to set temporary variables while in your calculation. So, what I have done is used the function called patternCount to tell me how many blanks there are in the string. If there are 2, then I know there is a middle initial, so I start right from the second occurrence of " ", which skips over the middle initial. If the count is not 2, say there is no middle initial, then Right() grabs the text to the right of the first occurrence, so you still get the correct last name initial.

If the user typed "Fred B. Flintsone ( there are 4 spaces )

but Wait! What if there are spaces in front of the name or after the last name? does it still work?

Maybe.

I've incldued a sample, database, you can grab the calc from there.

hope that helps.

sincerely,

J__

emailAdd_From_Name.zip

[J__]

for some reason i couldn't get my file to upload for you.

here's the script and let me know if you want the sample file. just add trim() to remove blanks in beginning and ending of name so patternCount does not count those too.

Let (

[ fname = Trim(full_name) ;

numSpaces = PatternCount(fname; " ")

] ;

Lower ( Left ( fname; 1) & Right ( fname ;Length( fname ) - Position( fname ;" ";1;numSpaces) ) )

)

[fbugeja]

Thanks for your response J_! Muchly appreciated from the Land Down Under!

There is no need to send the sample file - I prefer to learn by typing in the commands myself - it aids understanding.

Again, many thanks!

Frank

[J__]

You are welcome!

I'm glad (hope) it helps.

Australia? I've always wanted to go there.

yes, typing is good, i like to learn that way too, i feel like I 'experience' it.

ha, looks like the attachments did show up... they weren't appearing when I first posted...

good luck with your filemaker adventures,

sincerely,

J__