4 Replies Latest reply on Feb 10, 2012 7:38 AM by MorganPursley

    Salutation formula

    MorganPursley

      Title

      Salutation formula

      Post

      I'm trying to create a formula that will display the full salutation based on other fields (First Name, Last Name, Title, Spouse Name, etc). I thought a nested IF statement would do the trick, and maybe I just missed something, but that generated a big fat ?

      Here's what I'm trying to accomplish.

      If the spouses last names are different, the result should be <Title> <First_Name> <Last_Name> "&" <Title1> <Spouse_Name> <Spouse_Last_Name>  (Dr. John Smith & Dr. Jane Doe)

      If they are the same, or there is a Spouse first name and no last name, the result should be <Title> <First_Name> "&" <Soupse_Name> <Last_Name>  (Dr. John & Jane Smith)

      If there is no spouse information, the result should be <Title> <First_Name> <Last Name>  (Dr. John Smith)

       

      The current formula is  Evaluate ( "Name_First & \" \" & Name_Last" ; [Name_First ; Name_Last] )  and I tried altering that to fit, but that didn't quite work either.

      Any suggestions? Thanks!

        • 1. Re: Salutation formula
          philmodjunk

          You have some syntax errors. <Merge text> notation only works on layouts, it can't be used in a calculaiton expression.

          Case ( Last_Name ≠ Spouse_Last_Name ;
                                                   Title & " " & First_Name & " " & Last_Name & " & " & Title1 & " " & Spouse_Name & " " & Spouse_Last_Name;
                       Not IsEmpty ( Spouse_Name ) ;
                                                   Title & " " & First_Namee & " & " & Spouse_Name & " " & Last_Name ;
                      /* else */   
                                                   Title & " " & First_Name & " " & Last Name
                     )
                     

          • 2. Re: Salutation formula
            raybaudi

            Let([
            A = Substitute ( List ( Title ; First_Name ; Last_Name ) ; ¶ ; " " ) ;
            B = Substitute ( List ( " &" ; Title1 ; Spouse_Name ; Spouse_Last_Name ) ; ¶ ; " " ) ;
            C = Substitute ( List ( Title ; First_Name ; "&" ; Title1 ; Spouse_Name ; Last_Name ) ; ¶ ; " " )
            ];
            Case(
            IsEmpty ( Last_Name ) ; "" ;
            Last_Name ≠ Spouse_Last_Name and not IsEmpty ( Spouse_Last_Name ) ; A & B ;
            IsEmpty ( Spouse_Name ) ; A ;
            Last_Name = Spouse_Last_Name or IsEmpty ( Spouse_Last_Name ) ; C
            )
            )

            • 3. Re: Salutation formula
              philmodjunk

              Use raybaudi's I see the logic error in mine.

              • 4. Re: Salutation formula
                MorganPursley

                raybaudi, that worked beautifully! thanks so much, I never would've come to that conclusion on my own. cheers!