Trying to format a field from SQL to Crystal.

[32ndFlava]

I am building a report and need some help with formating the name field. In the SQL database I am using the name field shows the "Last, Middle First" all in the same field under a title of "Name". I need to create some sort of formula to put the input of "Last, Middle First" onto the report as "First Middle Last" (NOTE: The input has a comma in it, in the database, the output to Crystal should not have a comma) There is no broken up fields in the database such as "First Name", "Middle Name", "Last Name". It is all compressed which is why I am having a problem. Any help would be greatly appreciated!

 

[lbass]

If there are always the three elements to the name, then the following should work:

//{@Firstname}:
stringvar array x := split(x," ");
if ubound(x) = 3 then
stringvar fname := x[3];

//{@Midname}:
stringvar array x := split(x," ");
if ubound(x) >= 2 then
stringvar mname := x[2];

//{@LastName}:
stringvar array x := split(x," ");
if ubound(x) >= 1 then
stringvar lname := left(x[1],len(x[1])-1);

-LB

 

[32ndFlava]

OK, Say the Field I want to pull in from SQL is named "FullName" how would I incorporate that into the formula? Would the "FullName" go in place of the 'x' in the following formula you created?

//{@Firstname}:
stringvar array x := split(x," ");
if ubound(x) = 3 then
stringvar fname := x[3];

Also, Would these essentially be split into three formulas say, "FirstName", "MiddleName","LastName"? Thanks for all your help!

 

[32ndFlava]

It also says that "This array must be subscripted. For example: Array ." refering to the 'x' in the split(x," ") statement.

 

[lbass]

So sorry--silly mistakes. In each formula add the field name in place of the x in the first line, like this:

stringvar array x := split({tablename.fullname}," ");

Replace "tablename" with your actual table name.

-LB

 

[32ndFlava]

Fantastic this worked very well! Thank You so much for the help!