1 2 Previous Next 19 Replies Latest reply on Oct 9, 2014 1:35 PM by JavierDura

    Sum ListA to ListB

    JavierDura

      I have two value lists:

       

      ListA: 4 ¶ 2 ¶ 3

      ListB: 5 ¶ 4 ¶ 1

       

      What is the right function (or custom function) to get a new list that sums each value from listA to its corresponding value in listB.

      In other words, I want to get this result:

       

      9 ¶ 6 ¶ 4

       

      Thanks!

        • 1. Re: Sum ListA to ListB
          raybaudi

          A custom function or an UNSTORED calculation field can do the job.
          The calculation could be made along this line:

           

          Let([

          $i = $i + 1 ;

          a = GetValue ( listA ; $i ) ;

          b = GetValue ( listB ; $i ) ;

          v = Max ( ValueCount ( listA ) ; ValueCount ( listB ) ) ;

          result = If ( $i ≤ v ; List ( a + b ; sumLists ) ) ;

          $i = ""

          ];

          result

          )

           

          Note that sumLists is the name of the fied itself: create it before as a text field than change it to a calculation field.
          The custom function could use nearly the same calc.

           

          *** the unstored calculation works well with value lists that have not more than 200 150 values.***

          1 of 1 people found this helpful
          • 2. Re: Sum ListA to ListB
            TomHays

            A calculation (stored or unstored is fine) that does not use recursion could be the following.

            Using Substitute() it builds an expression for summing the values in each line of the list and then uses Evaluate().

             

            Testing using FileMaker Pro 11,

            I found that it worked on lists up to 2500 test values composed of a simple sequence 1¶2¶3¶4...¶2500 for both ListA and ListB.

            It gave an empty result when I tried 3000 values.

            I expect that the length of the string generated for Expression() is the limiting factor.

            At 2500 values the argument for Evaluate() was over 130,000 characters long and still worked.

            3000 values the argument for Evaluate() was over 158,000 characters and didn't work.

             

             

            Let([

            fieldnameA = GetValue(Substitute(GetFieldName(ListA);"::";"¶");2);

            fieldnameB = GetValue(Substitute(GetFieldName(ListB);"::";"¶");2);

             

            $i=1;

            lineBR = Filter(GetField(fieldnameA); "¶");

            expr =

            Case(  // Accommodate empty first values in both lists

            // Return 0 instead of empty quotes if you want adding two empty values to be zero

            IsEmpty(Trim(GetValue(ListA;1))) and IsEmpty(Trim(GetValue(ListB;1))); "\"\"";

            GetValue(ListA;1) + GetValue(ListB;1)

            ) &

            Substitute(lineBR; "¶";

            "&\¶&Let($i=$i+1;GetValue(" & fieldnameA & ";$i)+GetValue(" & fieldnameB & ";$i))")

            ];

            Case(

            ValueCount(ListA) = 0; "";

            Evaluate(expr)

            )

            )

             

             

             

            -Tom

            • 3. Re: Sum ListA to ListB
              BruceRobertson

              Why is it necessary to do this?

              Why isn't the data stored in normal FileMaker fields?

              • 4. Re: Sum ListA to ListB
                raybaudi

                Where did you empty the $i ?

                If a list ( listA ) is empty, the sum of the two value lists correspond to listB, I think.

                A recursive custom function can evaluate 10000 values.

                • 5. Re: Sum ListA to ListB
                  TomHays

                  Daniele Raybaudi wrote:

                   

                  Where did you empty the $i ?

                   

                  I'm not sure I understand the question.  $i is initialized to 1 in Let().  Its value is inherited in when $i is evaluated inside the Let() inside the Evaluate().

                  Since $i is a script variable, the calculation engine will empty it after the outermost Let() is evaluated.

                   

                  If you invoke this from another calling context, script or calculation, the value of $i will persist for that context.

                  If you anticipate that this will present a name conflict problem, I recommend renaming $i to something that is not used by the calling function or script.

                   

                  Daniele Raybaudi wrote:

                   

                  If a list ( listA ) is empty, the sum of the two value lists correspond to listB, I think.

                   

                  I think that is a valid interpretation of the function's requirements.  I think that interpretation would imply also that if ListB were empty, the function should return ListA.   Thus

                   

                  ValueCount(ListA) = 0; "";

                   

                  could be rewritten as

                   

                  ValueCount(ListA) = 0; ListB;

                  ValueCount(ListB) = 0; ListA;

                   

                  or perhaps

                   

                  IsEmpty(ListA); ListB;

                  IsEmpty(ListB); ListA;

                   

                   

                  -Tom

                  • 6. Re: Sum ListA to ListB
                    raybaudi

                    "Since $i is a script variable, the calculation engine will empty it after the outermost Let() is evaluated"

                     

                    $i isn't a script variable, it is a Let variable; its value persists at the end of the calculation.

                    You could see it with the data viewer.

                    • 7. Re: Sum ListA to ListB
                      TomHays

                      Daniele Raybaudi wrote:

                       

                      A recursive custom function can evaluate 10000 values.

                       

                      True.

                      Recursion works pretty well in that context.

                       

                      If we combine the techniques with batching, I suspect we could handle lists with 100,000 values.

                       

                       

                      Recursion inside an unstored calculation field other the other hand can apparently handle far less.

                      In FileMaker 7, your calc's recursion appears to stop evaluating at 169 values.

                      In FileMaker 12, the recursion looks to go deeper allowing 185 values.

                       

                       

                      -Tom

                      • 8. Re: Sum ListA to ListB
                        TomHays

                        Daniele Raybaudi wrote:

                         

                        "Since $i is a script variable, the calculation engine will empty it after the outermost Let() is evaluated"

                         

                        $i isn't a script variable, it is a Let variable; its value persists at the end of the calculation.

                        You could see it with the data viewer.

                         

                        That's a new one to me.  I had mistakenly assumed that $value assigned in a variable and one assigned in a script would have a similarly limited scope.

                         

                        I interpreted the persistence of $i to be at the same level or inherited into sub-calcs.  I didn't think it would persist past the current calculation context.  The data viewer and viewing using other calculated fields reveals its persistence.

                         

                        Thank you for making me aware of that.

                         

                        (Please excuse the threadjack.)

                         

                        This begs the question of what difference is there, if any, between using $$variableName and $variableName in a Let() assignment in a FileMaker calculation outside of a Script? 

                         

                        -Tom

                        • 9. Re: Sum ListA to ListB
                          TomHays

                          TomHays wrote:

                           

                          This begs the question of what difference is there, if any, between using $$variableName and $variableName in a Let() assignment in a FileMaker calculation outside of a Script?

                           

                          FileMaker Help has enlightened me.

                           

                          "Local variables defined in a calculation are scoped to the file but are only available when scripts are not running."

                           

                          Thanks again Daniele for pointing this out.

                           

                          -Tom

                          • 10. Re: Sum ListA to ListB
                            raybaudi

                            "This begs the question of what difference is there, if any, between using $$variableName and $variableName in a Let() assignment in a FileMaker calculation outside of a Script?"

                             

                            In a CF or in a calculation using the $ variable assigned by a Let ( ) function there is no need to use a $$ global variable, they work the same and both must be emptied.

                            • 11. Re: Sum ListA to ListB
                              TomHays

                              Here is a later version where the index variable is cleared to prevent it persisting beyond the scope of the Let() following Daniele's advice.

                               

                              I also cleaned up the code so that the only references to the field names is at the beginning (as originally intended).

                              This code has the same limits on performance as before.

                               

                              Let([

                              fieldnameA = GetValue(Substitute(GetFieldName(ListA);"::";"¶");2);

                              fieldnameB = GetValue(Substitute(GetFieldName(ListB);"::";"¶");2);

                               

                               

                              $j=1;

                              lineBR = Filter(GetField(fieldnameA); "¶");

                              expr =

                              Case(  // Accommodate empty first values in both lists

                              // Return 0 instead of empty quotes if you want adding two empty values to be zero

                              IsEmpty(Trim(GetValue(GetField(fieldnameA);1))) and IsEmpty(Trim(GetValue(GetField(fieldnameB);1))); "\"\"";

                              GetValue(GetField(fieldnameA);1) + GetValue(GetField(fieldnameB);1)

                              ) &

                              Substitute(lineBR; "¶";

                              "&\¶&Let($j=$j+1;GetValue(" & fieldnameA & ";$j)+GetValue(" & fieldnameB & ";$j))");

                              result = Case(

                              IsEmpty(ListA); ListB;

                              IsEmpty(ListB); ListA;

                              Evaluate(expr)

                              );

                              $j=""

                              ];

                              result

                              )

                               

                               

                               

                              -Tom

                              • 12. Re: Sum ListA to ListB
                                raybaudi

                                This is a custom function:

                                 

                                /*

                                SumLists ( listA ; listB )

                                */

                                Let([

                                $i = $i + 1 ;

                                a= GetValue ( listA ; $i ) ;

                                b= GetValue ( listB ; $i ) ;

                                v= Max ( ValueCount ( listA ) ; ValueCount ( listB ) ) ;

                                result = If ( $i ≤ v ; List ( a + b ; SumLists ( listA ; listB ) ) ) ;

                                $i = ""

                                ];

                                result

                                )

                                • 13. Re: Sum ListA to ListB
                                  DavidJondreau

                                  I'm not a big fan of using local variables in custom functions. Especially a generic one, like $i. You may want to consider using $sumlists_counter as your variable, or passing a parameter to use as a counter.

                                   

                                  If a developer attempts to use the $i as a counter in a script along with this function, it could really screw things up.

                                  • 14. Re: Sum ListA to ListB
                                    TomHays

                                    Here's one without a local $variable.

                                     

                                     

                                    /* SumListsAB (listA; listB)

                                    */

                                     

                                    Let(

                                    [

                                       maxCount=Max ( ValueCount ( listA ) ; ValueCount ( listB ) ) ;

                                       firstSum = GetValue(listA; 1) + GetValue(listB; 1)

                                    ];

                                    Case(

                                    IsEmpty(listA); "";

                                    IsEmpty(listB); "";

                                    maxCount = 1; firstSum;

                                    firstSum & "¶" & SumListsAB(RightValues(listA; maxCount -1); RightValues(listB; maxCount -1))

                                    )

                                    )

                                     

                                     

                                    -Tom

                                    1 2 Previous Next