Solved

Crystal Reports 8 - Using TitleCase

Posted on 2011-03-01
20
1,603 Views
Last Modified: 2012-05-11
I'm trying to create a field similar to Access Substring function that will place capitalization on the first letter of each word.  Currently the data is in all caps in the database I am retrieving from.  Any suggestions?
0
Comment
Question by:lbeeson
  • 8
  • 5
  • 5
  • +1
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Other than the first word is there a space before each word?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I haven't used CR 8, but CR 10 has a ProperCase function that does that kind of thing.  See if CR 8 has that function.

 James
0
 
LVL 19

Expert Comment

by:GJParker
Comment Utility
James is right use the ProperCase function if it exists. If it doesn't try the formula below, this assumes a space between each word

Local StringVar Array Words;
Local NumberVar i;

Words := Split('this is my sentence to change', ' ');

For i := 1 to Ubound(Words)
Do(
Words[i] := UCase(Left(Words[i], 1)) & Right(Words[i], (Len(Words[i]) - 1)));

Join(Words, ' ')

Open in new window

0
 

Author Comment

by:lbeeson
Comment Utility
I actually have 2 fields I would be using this for... A name field and an address field.  The data is stored in all caps
0
 

Author Comment

by:lbeeson
Comment Utility
I don't see the ProperCase function in version 8 :(
0
 
LVL 19

Expert Comment

by:GJParker
Comment Utility
You can create 2 new formulas using teh syntax I provided above replacing the 'this is my sentence to change' part of the formula with your fields
0
 

Author Comment

by:lbeeson
Comment Utility
@GJParker - I am going to try your solution... I am surprised that it is not a built in function.  Another option maybe to format it in a view out of SQL Server (which is the database software used).
I will post soon to let you know.  Thanks so much!
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Slight variation on GJParker's formula.  You can replace Right(Words[index], (Len(Words[index]) - 1)) with Mid(Words[index], 2).  It's simpler to read, and might even be a bit more efficient, although I doubt that it would actually have a significant effect on the performance of the formula.

 I replaced the "i" in the subscripts with "index", because EE was whining about it being some kind of tag and wouldn't let me post this with them there.  You'd need to change the "index" back to "i" to use that Mid function in that formula.

 James
0
 

Author Comment

by:lbeeson
Comment Utility
Thanks James,
I think you lost me with the replacement of "i".

Let me be a little more descriptive in my issue.
I am connecting to SQL Server via ODBC connection to and Employee table that has FirstName and
LastName as field names.  The content of both of those fields is in all caps.

I am using the Crystal Reports "Formula Editor" to conjoin those fields like so:
{Employee.FirstName} & " " & {Employee.LastName}

I want the report to display Laura Beeson instead of LAURA BEESON.

In MSAccess it's super easy, I just say FirstLast: StrConv([Employee]![FirstName] & " " & [Employee]![LastName],3) an viola it displays as I wish.

Crystal Reports 8 just wants to be difficult.
0
 
LVL 19

Expert Comment

by:GJParker
Comment Utility
This should do it

Left({Employee.FirstName}, 1) & LCase(Right({Employee.FirstName}, (Len({Employee.FirstName}) - 1))))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Right({Employee.LastName}, (Len({Employee.FirstName}) - 1))))
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:lbeeson
Comment Utility
Hmmm It says there is an error in that formula but I think we are getting closer...
I also have the same issue with the {Address1} field and that one can be more complicated since its not a single word and can contain an unknown number of numbers in the beginning.
Thank you so much for helping with this.  
0
 
LVL 19

Expert Comment

by:GJParker
Comment Utility
Ovberdid it on the brackets, try this

Left({Employee.FirstName}, 1) & LCase(Right({Employee.FirstName}, (Len({Employee.FirstName}) - 1))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Right({Employee.LastName}, (Len({Employee.FirstName}) - 1))

You should be able to use my earlier formula for the address field, if this doesn't work then provide an example of your addresses and how you want them to appear
0
 

Author Comment

by:lbeeson
Comment Utility
Still getting an error in that formula. Says it's missing a ')'.
I will keep playing with it.
I am shocked Crystal doesn't offer am easier solution.
as for the address field(s)
The address Appears as 123 ABC ST.
And I would like it to appear as 123 Abc St.
The field name is Address1
0
 
LVL 19

Accepted Solution

by:
GJParker earned 175 total points
Comment Utility
aargh

just add e3xtra brackets at the end of each line

Left({Employee.FirstName}, 1) & LCase(Right({Employee.FirstName}, (Len({Employee.FirstName}) - 1)))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Right({Employee.LastName}, (Len({Employee.FirstName}) - 1)))

try this for address

Local StringVar Array Words;
Local NumberVar i;

Words := Split('123 ABC ST,', ' ');

For i := 1 to Ubound(Words)
Do(
Words[i] := UCase(Left(Words[i], 1)) & LCase(Right(Words[i], (Len(Words[i]) - 1))));

Join(Words, ' ') 

Open in new window

0
 

Author Comment

by:lbeeson
Comment Utility
Woot Woot - The First and Last Name part worked :)

So now I will work on the Adress

I have never used code like that in Crystal before so this will be interesting.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
To change to the other method

Left({Employee.FirstName}, 1) & LCase(Mid({Employee.FirstName}, 2))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Mid({Employee.LastName},2))

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 75 total points
Comment Utility
 > I think you lost me with the replacement of "i".

 FWIW, all I was saying was that where I had written

 Mid(Words[index], 2)

 you would need to replace the word "index" with the letter "i".  That was a modification for part of GJParker's formula, and he had used "i" in the brackets, but when I tried to post that message with the "i" in the brackets, EE (the Experts Exchange site) was trying to interpret it as a formatting tag for my post, and giving me an error because the tag wasn't used properly.  So, I replaced the "i" with "index", so that EE would let me post the message.  In order to use that code in GJParker's formula, you would have to change the "index" back to an "i" (without the quotes).

 It was supposed to just be a simple suggestion for a small change to GJParker's formula.  :-)  The problem with trying to include the "i" in brackets in my post just complicated things.

 And in case you're wondering, GJParker was able to use the "i" in the brackets presumably because he had posted that in a "code" block, whereas I was trying to include it in the body of a message.  I could have used a "code" block too, but I wasn't posting a block of code - Just a small change to someone else's code - so the "code" block thing didn't really seem appropriate.  And I'm stubborn.  :-)


 Anyway, getting back to your problem, you should be well on your way.  GJParker's formula should work, as long as the words that need to be capitalized are either the first word in the address, or follow at least one space.  What it does it split the address into separate "words" wherever there's a space, then convert the first character in each word into uppercase, and the remaining characters in each word into lowercase.  If the characters aren't letters, they won't be changed.  He used '123 ABC ST,' as a test address.  To use the formula on your address field, you'll need to change

Words := Split('123 ABC ST,', ' ');

   to

Words := Split({Employee.Address1}, ' ');


 James
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Oh, yeah.  Another FWIW.

 > I am shocked Crystal doesn't offer am easier solution.

 CR 10 does.  The ProperCase function.  But apparently they hadn't implemented that function yet in CR 8 (which, to be fair, is quite old).

 James
0
 

Author Closing Comment

by:lbeeson
Comment Utility
Thanks so much :)
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now