5 Replies Latest reply on Jan 25, 2012 11:01 AM by LauraZ.

    calculating list of two names in a field

    LauraZ.

      Hi -

       

      Calc on related values List(name) where names are:

      Joe

      Jim

      Jane

       

      Substituting to get Joe, Jim and Jane

       

      I have a couple of ways to do it, based on number of values or number of words.

       

      However, what about:

       

      Mary Jane

      John Henry

      Anna Lee

       

      ??

       

      I am sure somebody has it.

       

      Thank you!

       

      Laura Z.

        • 1. Re: calculating list of two names in a field
          s

          Hi LauraZ,

          Substitute ( thelist ; "¶" ; ", " )

           

          Substitute comma space for carriage returns

           

          --

          Steve Moore

          Cumberland, Maine

          • 2. Re: calculating list of two names in a field
            LauraZ.

            Thank you Steve.

             

            Of course, I was not super clear on the problem.

             

            This is an attempt to collect nicknames from child individual records for the household letter salutation.


            Dear Uno,

            Dear Duo and Duodo,

            Dear Once, Twice and Thrice,

            Dear Un, Deux, Trois and Quatre,

             

            All work fine. Then there are people with two first names like Mary Anne and John Henry. In that case, RightValues works better than RightWords, but using RightValues messes up what works with RightWords. See below:

             

            Case( Count (test child::name) = 2; Substitute (RightWords ( list ; 2 ) ; ","; " and");Count (test child::name)  > 2;

            LeftWords (list; (Count (test child::name) - 2))  &", "  & Substitute (RightWords ( list ; 2 ) ; ","; " and")

            ; list)

             

            list is a calc field like Steve suggested above, substituting the carraige return with a comma.

             

            That's what is going on. Sorry to be so vague before.

             

            I started to look at the value list option, and maybe should examine that more, where List(valuelist) where the valuelist is filtered for each household and shows the nicknames, can count them and may treat them more like items in a list (not words or values), therefore keeping Mary with Anne. However, that approach also alphabetizes the names and I am anticipating that the client is going to want to control the order of the nicknames - I might be wrong about that, and maybe should explore that more.

             

            Thank you again,

            Laura Z.

            • 3. Re: calculating list of two names in a field
              comment

              How about =

               

              Let ( [
              names = List ( Child::Name ) ;
              n = ValueCount ( names )
              ] ;
              Case ( 
              n ≤ 1 ; names ;
              
              Substitute ( LeftValues ( names ; n - 1 ) & ¶ ; [ "¶¶" ; " and " ] ; [ ¶ ; ", " ] ) & GetValue ( names ; n )
              )
              )
              
              • 4. Re: calculating list of two names in a field
                comment

                Or, if you prefer =

                 

                Let ( [
                names = List ( Child::Name ) ; 
                pos = Position ( names ; ¶ ; Length ( names ) ; -1 ) 
                ] ;
                Case ( pos ; Substitute (  Replace ( names ; pos ; 1 ; " and " ) ; ¶ ; ", " ) ; names )
                )
                
                1 of 1 people found this helpful
                • 5. Re: calculating list of two names in a field
                  LauraZ.

                  Both work Great!

                  Thank you very much!

                  Laura Z.