9 Replies Latest reply on Jul 25, 2015 6:17 PM by philmodjunk

    Two textstyle adds in one calculation

    AndrewJudd

      Title

      Two textstyle adds in one calculation

      Post

      Hi all,

      I'm trying to add uppercase to my postcode and country fields in the below calculation. Can't work out how to end titlecase and begin uppercase or somehow add uppercase half way through each part?

      thank you!

       

      TextStyleAdd ( If (not IsEmpty (Person Title) ; Person Title & " " &  FIRST NAME & " " & SURNAME  & ¶ & Street Address & ¶ & City & ¶ & Postcode & ¶ & Country)

      &  If (IsEmpty (Person Title); FIRST NAME & " " & SURNAME  & ¶ & Street Address & ¶ & City & ¶ & Postcode & ¶ & Country) ; Titlecase)

       

       

        • 1. Re: Two textstyle adds in one calculation
          philmodjunk

          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.

             

          • 2. Re: Two textstyle adds in one calculation
            AndrewJudd

            thank you! what if i just want postcode and country in uppercase and the rest in titlecase?

            • 3. Re: Two textstyle adds in one calculation
              philmodjunk

              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.

              • 4. Re: Two textstyle adds in one calculation
                AndrewJudd

                thanks Phil, thats interesting about Mackenzie.. is there a way around that.. or is it one of the other?

                • 5. Re: Two textstyle adds in one calculation
                  philmodjunk

                  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 )

                  • 6. Re: Two textstyle adds in one calculation
                    AndrewJudd

                    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?

                     

                    • 7. Re: Two textstyle adds in one calculation
                      philmodjunk

                      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.

                      • 8. Re: Two textstyle adds in one calculation
                        AndrewJudd

                        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 )

                        thank you!

                        • 9. Re: Two textstyle adds in one calculation
                          philmodjunk

                          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 ) &
                                             @NameCap &
                                             @AddressBlock & ¶ &
                                              Upper ( Postcode & ¶ & Country )
                                             ) // Trim
                              )// Let

                          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.