You have more problems than that. Try it this way:
If (not IsEmpty (Person Title) ; Person Title & " " ) & FIRST NAME & " " & SURNAME & ¶ & Street Address & ¶ & City &
& ¶ & TextstyleAdd ( Postcode & ¶ & Country ; TitleCase )
But if you want all upper case, you need to use UpperCase, not TitleCase. TitleCase only capitalizes the first letter of each word.
thank you! what if i just want postcode and country in uppercase and the rest in titlecase?
TextStyleAdd ( If (not IsEmpty (Person Title) ; Person Title & " " ) & FIRST NAME & " " & SURNAME & ¶ & Street Address & ¶ & City ; TitleCase ) &
& ¶ & TextstyleAdd ( Postcode & ¶ & Country ; UpperCase )
But I must point out that this can produce incorrect capitalization of certain names such as MacKenzie--which the above calculation will render as Mackenzie.
thanks Phil, thats interesting about Mackenzie.. is there a way around that.. or is it one of the other?
There's no perfect way to set up the DB to correctly do the capitalization for the simple reason that two names can be spelled identically but capitalized differently.
I usually manage this with conditional formatting rather than a calculation field but what I've done is used the Exact function to compare the text to different common capitalization patterns and leave the name capitalized "as is" if the name is not all Caps but has at least one letter other than the first capitalized.
If ( Exact ( NameField ; Upper ( NameField ) ) or Exact ( NameField ; Lower ( NameField ) ) ; Proper ( NameField ) ; NameField )
Thank you, so in my case is conditional formatting - formula is -
If ( Exact ( CLIENTS::SURNAME ; Upper ( CLIENTS::SURNAME ) ) or Exact ( CLIENTS::SURNAME ; Lower ( CLIENTS::SURNAME ) ) ; Proper ( CLIENTS::SURNAME ) ; CLIENTS::SURNAME )
If seems if i then set the style to plain unfortunately the titlecase doens't work. If it set to titlecase I'm unable to capitalize the K of Mackenzie?
I provided an example for your calculation method not a conditional format. The details differ if I set this up as a conditional format expression. For conditional formatting, I'd use:
Exact ( CLIENTS::SURNAME ; Upper ( CLIENTS::SURNAME ) ) or Exact ( CLIENTS::SURNAME ; Lower ( CLIENTS::SURNAME ) )
and then specify the TitleCase option in the format options for this conditional format expression.
Thank you! the next tricky part will be adjusting my calculation field (which creates a summary of the contact details). This is what I have..
TextStyleAdd ( If (not IsEmpty (Person Title) ; Person Title & " " ) & If (IsEmpty (Person Title & FIRST NAME & SURNAME) ; COMPANY) & FIRST NAME & " " & SURNAME & ¶ & Street Address 1 & If (not IsEmpty (Street Address 2) ; ¶ & Street Address 2) & ¶ & City ; Titlecase ) &
¶ & TextStyleAdd ( Postcode & ¶ & Country ; Uppercase )
Do I need to add the beforementioned calculation to this somehow - If ( Exact ( NameField ; Upper ( NameField ) ) or Exact ( NameField ; Lower ( NameField ) ) ; Proper ( NameField ) ; NameField )
You can use the Let calculation to break this up into parts.
Let ( [ @Title = TextStyleAdd ( Person Title ; TitleCase ) ;
@Name = FIRST NAME & " " & SURNAME ;
@NameCap = If ( Exact ( @Name; Upper ( @Name) ) or Exact ( @Name; Lower ( @Name) ) ; Proper ( @Name ) ; @Name ) ;
@Company = TextStyleAdd ( Company ; TitleCase ) ;
@AddressBlock = TextStyleAdd ( List ( Street Address 1 ; Street Address 2 ; City ) ; TitleCase )
Trim ( @Title & " " &
If ( IsEmpty ( @Title & @Name ) ; @Company ) &
@AddressBlock & ¶ &
Upper ( Postcode & ¶ & Country )
) // Trim
Note, it might be simpler to set up auto-enter calculations on each field to get the capitalization correct there during data entry rather than use such a complex expression here.