2 Replies Latest reply on Mar 31, 2017 8:00 AM by uncleboski

    Concatenation variables

    uncleboski

      I have been wracking my brain about a text calculation field, “DisplayName,” to concatenate one or two people in a household and whether or not they share the same last name.

       

      Name Fields:

        member #1:  NameFirstM1, NameLastM1

        member #2:  NameFirstM2,  NameLastM2

       

      1.  If only one member: John Smith and the desired return is:  “John Smith” (w/o quotation marks)

      By itself, calc is easy:

        NameFirstM1 & “^” & NameLastM1

       

      2.  If there are two members with same last name and the desired return is:  “John & Mary Smith”

      By itself, calc would be:

        NameFirstM1 & “^&^” & NameFirstM2 & “^” & NameLastM1

       

      3.  If there are two members with different last names and the desired return is:  “John Smith & Bob Jones”

      By itself, calc would be:

        NameFirstM1 & “^” & NameLastM1 & “^&^” & NameFirstM2 & “^” NameLastM2

       

      Is it possible to do all these in one calculation, or a sequence, a script, or another "best way?"  Searching the concatenation discussions has given me a few ideas, but I'm stumped and welcome any suggestions.  Thanks

        • 1. Re: Concatenation variables
          SteveMartino

          Seems like a possible problem with your db structure.  Is:

             member #1:  NameFirstM1, NameLastM1

            member #2:  NameFirstM2,  NameLastM2

          fields on the same table, fields from a related table (portal), or  repeating fields from the same table?

          Is there ever only one or 2?

          let([

                 @singleperson=case(isempty (NameLastM2); NameFirstM1 & “^” & NameLastM1); //if only one name set this variable

                 @twopeople=Case(NameLastM1=NameLastM2; // if both members have data, check for same last names

                 NameFirstM1 & “^&^” & NameFirstM2 & “^” & NameLastM1;

                 NameFirstM1 & “^” & NameLastM1 & “^&^” & NameFirstM2 & “^” NameLastM2);

                 @name=case(isempty (@singleperson);@twopeople;@singleperson)

            ];

           

              @name   

          )

          Untested so it may not be perfect

          1 of 1 people found this helpful
          • 2. Re: Concatenation variables
            uncleboski

            Wow, thanks for such a quick response Steve.  Yes, both names are in the same table (and I have to be very careful with aging eyesight).  If there's only one person, he or she is always "M1" so there's never an "M2" name by itself.

             

            Looks like a great thing to try; I'll let you know.  Thanks again.

            1 of 1 people found this helpful