8 Replies Latest reply on Apr 25, 2014 4:15 AM by Agnès

    How to merge 2 lists in custom function

    mark_b

      Hi All,

       

      I am trying to build a custom function that will take 2 return separated lists (both have to be same ValueCount) and merge them into one list with values from each separated by a specified delimiter. An example will clarify: ListA = A¶B¶C and ListB = 1¶2¶3 and Delimiter = "|". The desired result would be: A|1¶B|2¶C|3. The length of the lists will be from 1 (not really a list) to maybe 5 at the most. So I figured some recursive custom function would be the answer. I looked through Brian Dunning's site and didn't find anything close.

       

      The function should look something like: ConcatList (ListA; ListB; Delim; Next). "Next" would start out as 1 and the function would increment itself up to the ValueCount of either list and then exit, returning the desired new list. I understand the concepts but I can't get my head around the implementation. Thanks for any guidance.

       

      Cheers, Mark

        • 1. Re: How to merge 2 lists in custom function
          FileKraft

          ConcatList (ListA; ListB; Delim ) ::=

           

          Let ([

           

          _headA = GetValue ( ListA ; 1 ) ;

          _headB = GetValue ( ListB ; 1 ) ;

           

          _vcA = ValueCount ( ListA ) ;

          _vcB = ValueCount ( ListB ) ;

           

          _tailA = RightValues ( ListA ; _vcA - 1 ) ;

          _tailB = RightValues ( ListB ; _vcA - 1 )

           

          ];

           

          Case (

          _vcA ≠ _vcB ; "" ;

           

          _vcA = 1 ; _headA  & Delim & _headB ;

           

          _headA  & Delim & _headB  & ¶ & ConcatList ( _tailA ; _tailB ; Delim )

           

          )

          )

          • 2. Re: How to merge 2 lists in custom function
            erolst

            As a variation on my predecessor's theme:

             

            ZipLists ( listA ; listB ; delim ) =

             

            Let ( [

              vcA = ValueCount ( listA ) ;

              vcB = ValueCount ( listB )

              ] ;

             

            Case (


                vcA = vcB ;

             

                Let ( [

             

                  hA = GetValue ( listA ; 1 ) ;

                  hB = GetValue ( listB ; 1 ) ;

             

                  tA = RightValues ( listA ; vcA - 1 ) ;

                  tB = RightValues ( listB ; vcB - 1 ) ;

             

                  result = hA & delim & hB

             

                  ] ;

             

                  List ( result ; Case ( vcA - 1 ; ZipLists ( tA ; tB ; delim ) ) )

             

                 )

              )

            )

            • 3. Re: How to merge 2 lists in custom function
              mark_b

              THANKS to both of you!  I will study your examples so I can learn what is going on.  Apparently I made it too complex by passing a counter.  Both of you decrease the lists each iteration until done.

              Cheers, Mark

              • 4. Re: How to merge 2 lists in custom function
                Norsult

                Hi Mark,

                 

                basically I do share the ideas of the given proposals. Off course ther is the universal function at Brian Dunning's collection ("CustomList" of Agnes Barouh) but in this case it seems to be overkill. I gave it a try too with less intermediate calculation. You can directly copy the function and see from the first line the three expected parameters and their names.

                 

                //**COPY FROM HERE

                // CustomFunction: ConcatLists ( alist; blist; delim )

                //      alist and blist must be of same cardinality!

                //      Author: V. Krambrich 22 APR 2014 

                Let ([

                       acount = ValueCount ( alist);

                       bcount = ValueCount ( blist) ];

                 

                Case ( acount <> bcount; "Error!";

                          acount = 0; "";

                  //else

                         GetValue (alist; 1) & delim & leftValue (blist; 1) & 

                         ConcatLists ( RightValues ( alist; acount - 1) ; RightValues ( blist; acount - 1); delim )

                   )

                )

                //**ENDCOPY

                 

                In principle it would be easy to expand the function to deal with lists of different lengths...

                 

                Let us know how you finally decided to go!

                 

                Regards

                Volker

                • 5. Re: How to merge 2 lists in custom function
                  mark_b

                  Thanks Volker,

                   

                  Since time was a factor, I already used erlost's function.  It served the purpose - thanks again erlost.  But I also went ahead and installed yours with one minor typo change.  Your //else clause should have been getValue (blist; 1) instead of leftValue (blist; 1).  I like the error checking you did and the format with comments. When I get time, I'll probably go back and replace with your version - again the format and comments is more like what I do.

                  Cheers,Mark  

                  • 6. Re: How to merge 2 lists in custom function
                    Norsult

                    Mark, that was not a typographic error.

                     

                    Since the new list shouöd be a "list" in FileMaker sense, i.e. lists' elements separated by <cr>,  the else clause does this:

                    take the headvalue of the alist and strip the <cr>, concatenate the result with the value stored for a delimter, then add the headvalue of the blist WITHOUT stripping the <cr> -– thus creating a new list value befor further calling processing of the rest lists.

                     

                    This way my function will always generate a valid list; either the empty list, here represented by the word "Error!", or a list with 1 or more values delimited by carriage return signs!

                     

                    Then performance is an issue whenever you program, and less operations are usually faster than more operations.

                     

                    Regards

                    Volker

                    • 7. Re: How to merge 2 lists in custom function
                      mark_b

                      Wow - Thanks.  I see what the problem was.  When I pasted your code into the custom function and clicked the "OK" button, FM said it couldn't find the "leftValue" function.  I assumed it was a typo and replaced that with the GetValue function. I should have serached the function list and I would have found "leftValues" (with an "s").  I also now understand why the GetValues would not have worked - thanks for the explanation.

                      Cheers, Mark

                      • 8. Re: How to merge 2 lists in custom function
                        Agnès

                        Hello,

                        Only with CustomList (http://www.briandunning.com/cf/868), it would read :

                        Let ([

                        $La = "A¶B¶C" ;

                        $Lb = "1¶2¶3" ;

                        $Lim = "|"

                        ];

                        CustomList ( 1 ; ValueCount ( $La ) ; "GetValue ( $La ; [n] ) & $Lim & GetValue ( $Lb ; [n] )" )

                        )

                         

                        Be careful also that there is no empty values in your list A and B, if it is established by List ()

                        otherwise, you can edit your list directly by CustomList

                        Let ([

                        $Lim = "|"

                        ];

                        CustomList ( 1 ; Get ( FoundCount ) ; "GetNthRecord ( FieldA ; [n] ) & $Lim & GetNthRecord ( FieldB ; [n] )" )

                        )

                         

                        But maybe it's better to make a field A & "|" & B and make a list() or ListOf (forv13) of this field.

                         

                        Agnès