Formula for Last Name, First Name Middle Initial but fixed length

[wanzek1]

I am creating a fixed width report.

This is my formula so far:

{PREH.LastName}+ ','+ space(1) + {PREH.FirstName} + space(1) + Left({PREH.MidName},1)

The problem I have is that this cannot go over 30 characters. I know that I can do

formuala above + space(30-len(forumala above)

This will give me what I need but the trick is that this will be over 30 characters long for some employees. I need to abbreviate the first name to however many characters are left if this happens.

 

[hilfy]

Try this:

Left(<your final formula>, 30)

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[fisheromacse]

i am not sure i got this understood correctly, but here is my take on it (and i apologize in advance for syntax/errors, i do not have crystal in front of me currently)


//Your current name formula
//{@ConcName}
{PREH.LastName}+ ','+ space(1) + {PREH.FirstName} + space(1) + Left({PREH.MidName},1)

//The total length of your name formula
//{@TotalLEN}
LEN({PREH.LastName}+ ','+ space(1) + {PREH.FirstName} + space(1) + Left({PREH.MidName},1))

//Check if it is over 30 and if so by how much, if not, display a zero
/{@AmtOver}
IF {@TotalLEN} > 30 then {@TotalLen}-30 ELSE 0;

//If total formula length is over 30, shorten the first name otherwise show the current name formula
//{@Display}
IF {@AmtOver} = 0 then {@ConcName}
ELSE
{PREH.LastName}+ ','+ space(1) + LEFT({PREH.FirstName},(LEN({PREH.FirstName})-{@AmtOver})) + space(1) + Left({PREH.MidName},1)

 

[wanzek1]

That doesn't work because then the middle initial would be cut off.

I suppose I could drop the middle initial and do

Left(<your final formula>,29) and then add the middle initial.

 

[wanzek1]

Thank you fisheromacse that worked perfectly!