Skip to Content
0
Jul 23, 2018 at 03:34 PM

Formula Help

101 Views Last edit Nov 20, 2018 at 05:57 PM 5 rev

Hi,

I am working with a report that contains organizations and consumer names and it is extracted in all caps from the data processing system.

I need the name to read in propercase from start to finish.

I have a formula that (a) concatenates the FML names, puts in (b) proper case, changes the (c) suffix, and changes the letter after the (d) hyphen.

The problem that I am having is combining them all. Also, there is one more formula that I haven't figured out yet.

Formula (a) and (b) are successfully combined, formula (c) and (d) are functional, but in their own formula.

Here is an example of what I see:

George W. Mcgreedy III

or

George W Hunt-moore Jr

I would like to combine all formulas, or link them, as well as add a formula to capitalize the letter after the hyphen. I've pasted all 3 formulas below.

Thank you,

Shanna

ProperCase and Concantenate formula:

ProperCase (if {MasterEOM.Member Type} = "MO" then
 if isnull({MasterEOM.First Name}) then
 {MasterEOM.Last Name}
 else if isnull({MasterEOM.Last Name}) then
 {MasterEOM.First Name}
 else
 if Length({MasterEOM.Last Name}) = 15 then
 if isnull({MasterEOM.Middle Initial}) then
 {MasterEOM.Last Name}+{MasterEOM.First Name}
 else
 {MasterEOM.Last Name}+{MasterEOM.First Name}+{MasterEOM.Middle Initial}
 else
 if isnull({MasterEOM.Middle Initial}) then
 {MasterEOM.Last Name}+" "+{MasterEOM.First Name}
 else
 {MasterEOM.Last Name}+" "+{MasterEOM.First Name}+" "+{MasterEOM.Middle Initial}
else
 if Length({MasterEOM.First Name}) = 15 then
 if isnull({MasterEOM.Middle Initial}) then
 {MasterEOM.First Name}+{MasterEOM.Last Name}
 else
 {MasterEOM.First Name}+{MasterEOM.Middle Initial}+". "+{MasterEOM.Last Name}
 else
 if isnull({MasterEOM.Middle Initial}) then
 {MasterEOM.First Name}+" "+{MasterEOM.Last Name}
 else
 {MasterEOM.First Name}+" "+{MasterEOM.Middle Initial}+". "+{MasterEOM.Last Name})










Suffix Formula:

stringVar array values;
numberVar index := 0 ;

values := split({@Member Name});

for index := 1 to ubound(values) do
(

Select values[index]
Case "jr"
: values[index] := uppercase(left(values[index],1)) + lowercase(mid(values[index],2))

Case "PO" 
 : values[index] := uppercase(values[index])

Case "i" 
 : values[index] := uppercase(values[index])

Case "ii" 
 : values[index] := uppercase(values[index])

Case "iii" 
 : values[index] := uppercase(values[index])

Case "iv" 
 : values[index] := uppercase(values[index])

Case "v" 
 : values[index] := uppercase(values[index])

Case "vi" 
 : values[index] := uppercase(values[index])

Case "viii" 
 : values[index] := uppercase(values[index])

Case "ix" 
 : values[index] := uppercase(values[index])

Case "x" 
 : values[index] := uppercase(values[index])

Case "nw" 
 : values[index] := uppercase(values[index])

Case "ne" 
 : values[index] := uppercase(values[index])

Case "se" 

 : values[index] := uppercase(values[index])

Case "sw" 
 : values[index] := uppercase(values[index])


Default 
 : values[index] := uppercase(left(values[index],1)) + lowercase(mid(values[index],2));

);


join(values)




Hyphen Formula:
Replace ({@Member Name}, "m", " -M")