3 Replies Latest reply on Nov 3, 2016 8:44 AM by jimahopkins

    First Name "AKA" Last Name (Previous Last Name)

    woodemma

      I'm new at this concatenation deal. I have four fields that I need to string together and I'm just not getting it right. I continually get error messages either "this text constant does not end with a quotation mark" or "A number, text constant, field name or "(" is expected here."

       

      The first name and last name fields will always have data. The AKA and Previous Last Name field may be empty.

       

      I would like the final string to be formatted in this way:

      First Name "AKA" Last Name (Previous Last Name)

       

      This is the calculation I'm working with right now:

       

      =FirstName &" if(  not IsEmpty (FirstNameAKA); " " & FirstNameAKA;"") & Lastname

        • 1. Re: First Name "AKA" Last Name (Previous Last Name)
          beverly

          The double-quote needs to be escaped in calculations:

          \"

          So

          & "\""

          Will add one double-quote to string.

           

          Others also use the

          & Char(34)

          To avoid the escaping

           

          Sent from miPhone

          • 2. Re: First Name "AKA" Last Name (Previous Last Name)
            erolst

            First of all, leave away the equal sign at the beginning; that is implicit.

             

            Also, you need to escape quote marks, because they're already employed as string delimiters; using them as literals without escaping will confuse the parser and cause the error messages you've seen.

             

            Try

             

            FirstName &

            Case ( not IsEmpty ( FirstNameAKA ) ; " \"" & FirstNameAKA & "\"" ) &

            " " & Lastname &

            Case ( not IsEmpty ( Previous Last Name ) ; " (" & Previous Last Name & ")" )

             

            under the assumption that first and last name will never be empty, otherwise there will be gaps.

             

            Here's an alternative approach:

             

            Let (

              nameList =

              List (

                FirstName ;

                Case ( not IsEmpty ( FirstNameAKA ) ; "\"" & FirstNameAKA & "\"" ) ;

                Lastname ;

                Case ( not IsEmpty ( Previous Last Name ) ; "(" & Previous Last Name & ")" )

            ) ;

            Substitute ( nameList ; ¶ ; " " )

            )

             

            where also the above assumption doesn't have to be true, without getting gaps.

             

            If you're using this in a calculation field, don't forget to set the result data type (popup menu at the bottom) to "Text".

            2 of 2 people found this helpful
            • 3. Re: First Name "AKA" Last Name (Previous Last Name)
              jimahopkins

              FirstName &" if(  not IsEmpty (FirstNameAKA); " " & FirstNameAKA;"") & Lastname  What kind of response do you get from your calculation?  And do you use the field PreviousLastName in the calculation?

               

              i.e.  Firstname = Jim  FirstNameAKA = Jimmy   Lastname = Brown   PreviousLastName= Johnson

               

              So your calculation for this record should be

               

              First name Jim Jimmy Brown.  Did you want parenthesis around that AKA?  Just wondering what output you're looking for.