InfoPath Dev

Hi, in our organization we use 8-character User-IDs for authentication and I would like my InfoPath 2010 form to display it as such:

• First letter of first name
• First letter of second name (middle initial)
• First few letters of the last name so that the User-ID will be displayed as being up to 8 characters

For example, if Luke D. Skywalker joined our organization, and a form was filled out with Luke as his first name, D as his middle name initial, and Skywalker as his last name, his user-ID would become LDSKYWAL.  This assumes the first letter of the first name, the first letter of the second name, and part of the last name to make a total of 8 characters in his user-ID.  Having said this, if Luke didn't have  a second initial, his ID should become LSKYWALK.

My formula for figuring this out is kind of wrong - it does work somewhat, but not the way I want it to.

translate(concat("IDIR\", substring(FirstName, 1, 1), substring(MiddleInitial, 1, 2), substring(LastName, 1, 6)), "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ")

This fomula will concatenate the first letter of the FirstName field to 1 character in length displayed in the first position, the first letter of the MiddleInitial to 1 character in length displayed in the second position, and up to 6 characters of the LastName field to make up 8 characters, and translate the user-ID into uppercase, preceeded with "IDIR\" text string at the beginning.

This is where I'm having trouble.  If I enter Luke's name as the fields require with no middle initial, it translates to "IDIR\LSKYWAL" which is WRONG, because it is only 7 characters in length.  I need this to become "IDIR\LSKYWALK".  If I enter his name with a middle initial, it then becomes "IDIR\LDSKYWAL", which is correct.

Can someone rewrite my formula above so that if the name is entered with or without a middle initial, it's still 8 characters in length?  I'm having no end of trouble with this.

Thanks for your help.

Dan

I'd concat the first initial, middle initial and last name, then take a substring of that.

Like this:

substring(concat(substring(FirstName, 1, 1), substring(MiddleInitial, 1, 2), LastName), 1, 8)

 So, that gives you the 8 char name you need and you can add your translate around that, then concat in the domain and \

Hi Hilary, thanks for the reply.  I tried this already and found out that it won't work because if I enter "Luke Skywalker", his ID turns out to be LDSKYWALK, which is 9 characters, and if I leave out the middle initial, it becomes LSKYWALKE, which is also 9 characters.  I need to limit this to 8.

Dan

 I must be misunderstanding, because the formula I suggested returns 8 chars (or less) in the sample I've attached.