13 Replies Latest reply on May 25, 2015 1:00 AM by erolst

    Recursive Calculation let() List Loop Question

    cateringtraveller

      Hello all,

       

      I am stuck on calculation that I cannot build the recursion into- I sucked at math, probably thats the problem.

       

      Here is what I am trying to do:

      Get a list of values. Go to the fist item of the list, do something with it and create a result,

      go to the next item of the list; do something with it and create a result, add the result of this line to the result of the line before

      end when I reached the last item of the list.

      Output the result.

       

      Here is what I have so far:

       

      Let ([

      list= List (of a field);

      c=Valuecount (list);

      $i = $i+1;

      getitem=GetValue (list ; $i);

      $result = getitem*dosomethingwithit

      ];

      If ( $i = c; $result; getitem*dosomethingwithit + $result )

      )

       

      What am I doing wrong? I dont get the loop in my head in getting the calc to add to itself...

       

      Thank you!

        • 1. Re: Recursive Calculation let() List Loop Question
          comment

          A recursive calculation needs to recur, i.e. call itself. In Filemaker, you need to define a custom function for this (requires Filemaker Advanced). The custom function could look something like this:

           

          MyCustomFunction ( listOfValues ) =


          Let ( [

          item = GetValue ( listOfValues ; 1 ) ;

          n = ValueCount ( listOfValues )

          ] ;

          item * 3 + Case ( n > 1 ; MyCustomFunction ( RightValues ( listOfValues ; n - 1 ) ) )

          )

           

           

          Note the recursive call marked in red.

          • 2. Re: Recursive Calculation let() List Loop Question
            BruceHerbach

            Hi,

             

            Initially,  I was looking at this as a script and not a calclation in a field.  The issue is you are not looping. I don't believe that the Loop funtion is available in the calculation engine, it is part of scripting. Michael's method of a custom function would provide the loop you need using recursion.  The issue if there is one with recursion is the limitation on the number of iterations that can be pushed on to the stack.  I believe the limit is 50,000.  I have to say that so far I have not hit the limit when providing an appropriate exit from the recursive loop.  On the other hand... when I goofed,  the limit provided an out.  Of course I had to wait a while before it got there.

             

             

            HTH

            Bruce

            • 3. Re: Recursive Calculation let() List Loop Question
              cateringtraveller

              Thank you, this helped. I had read the needle in the haystack examples and could never get the gist of it, it was just too abstract.

              It took me a couple of hours to get my cf running, however now it works.

               

              I guess the easiest way is to draw on paper what you want to do and then try it out- good to use the data viewer to bugproof.

               

              What got me messed up at first was the

               

              cf (condition1 ; condition2; condition3)

              of course I named the conditions after my thoughts and

              when I was writing the case statement I had cf (conditionorder) mixed up my bad.

               

              Thanks again.

              • 4. Re: Recursive Calculation let() List Loop Question
                cateringtraveller

                That is correct. I am displaying the result in a portal and I cannot loop.

                I got it figured out int the end. I think the recursion is like the let statement. If you get the hang of it in your mind, its all easy.

                • 5. Re: Recursive Calculation let() List Loop Question
                  ch0c0halic

                  cateringtraveller may not be talking to himself enough,

                   

                  A custom function becomes recursive when it calls itself. Your calc is a one pass thru operation.

                   

                  You may be better off figuring out how to do it in a script first.

                   

                  You have a function that gets the result and call that from within a looping script for each value in the List.

                   

                  Set variable $Var_list to List (of a Field)

                   

                  Loop

                  Set $i to $i + 1

                  Set variable $item to GetValue ( $Var_list ; $i )

                  exit loop if

                  Set $results to CF ( $item )

                  end loop

                   

                  Where CF is the Customer Function call the does you conversion/interpretation "getitem*dosomethingwithit"

                  • 6. Re: Recursive Calculation let() List Loop Question
                    cateringtraveller

                    sorry chocohalic,

                     

                    you leave me with a big ?. It must be because of my bad English I guess.

                    Besides the fact that I did get the recursion right now, since you took the time to write:

                     

                    cateringtraveller may not be talking to himself enough, ? huh? what is that supposed to mean?

                     

                    You may be better off figuring out how to do it in a script first. how do you do recursion in the display of values inside a portal?

                     

                    Your calc is a one pass thru operation. Eh, no, and I think that was the initial question, how to get the recursion going

                     

                    Anyhow- thanks for the hint- I know now I have to click "Question" above to make it not a discussion. My issue is solved.

                     

                    Thanks

                    • 7. Re: Recursive Calculation let() List Loop Question
                      MicheleOlson

                      cateringtraveller wrote:

                       

                      sorry chocohalic,

                       

                      ...

                      cateringtraveller may not be talking to himself enough, ? huh? what is that supposed to mean?

                      ...

                      Catering Traveller,

                       

                      You have just met Mr. ch0cohaLic. Lucky you.

                       

                      Do not take offense at his opening line. That is his standard lead in ... an enigmatic message leading to the real message he offers. Consider yourself lucky if he notices your post and replies.

                       

                      Cheers,

                       

                      M.

                      • 8. Re: Recursive Calculation let() List Loop Question
                        ch0c0halic

                        Catering Traveller may need a bigger question,

                         

                        What's it mean?

                        Recursion in FMP: The act of a custom function repeatedly calling itself.

                        In people: the act of saying out loud what you've already said in your head.

                        So Talking to oneself is a form of people recursion. Since your Let() calculation didn't call itself it needed to talk to itself more.

                         

                        In a custom function you get recursion when the CF calls itself. Your Let() function didn't call itself so there was no recursion. It is a one-pass calculation.

                         

                        When defining a CF you have to give it a name. naming conventions come in handy here and you should figure out how you want to name your CF's. Something like "My_milti_line_data_extracter", may be a llittle to long for real world use. But "DE" is a little short and not descriptive enough. For now I'll use "Data_extractor". BTW, I'd put all of these comments in the CF so I'd know what each parameter is for and how its used. This is pretty much what you'll need to create a recursive Custom Function. But, since I don't know what you want to extract from the data from the related records its really only pseudo code for what you'll need.

                         

                        Note that I've broken some of the calculation definitions into separate defined values even though some can possibly be combined. I originally suggested definiting each operation of this CF in a script using a loop/end loop configuration to perform the same recursion operation. Use a passed parameter to submit the related field for evaluation.

                         

                         

                        Data_extractor ( related_field ; line_number ; results ) (This is my CF when used).

                        "related_field" is the data input field used to get the list of values from the related records to use in the "dosomethingwithit" operation.

                        "line_number" is the currently operated on related record from the value list. Always start with a 1 in this value.

                        "results" is the accumulated list of extracted values from the ;list of values. Always start with "".

                         

                        Let ( [

                         

                        $value_list = Case ( IsEmpty ( $value_list ; List ( related_field ) ; $value_list ;

                        //Using a local variable means it only has to get the related list of values once, not every time it goes thru a recursion.

                        current_value = GetValue ( $value_llist ; line_number ) ;

                        //data from the Nth related record in the list.

                        result = dosomethingwithit ( current_value ;

                        //the special code you want to do on the initial data.

                        results = result & ¶ & results ] ;

                        //A concatenation of each returned value.

                         

                        Case ( IsEmpty ( current_value ; result ; Data_extractor ( related_field ; line_number + 1 ; results ) )

                        //When all of the values from the list have been processed stop calling itself

                         

                        )

                         

                        Whew, not easy to write free-hand without the data viewer to check it out. I may have mispelled some of the words, I'm not a real good speller and I rely on the Data Viewer to get it right.

                         

                        Recursion in a custom function is one of the harder things to understand in FMP. But, once you know how to do it the results are worth it.

                         

                        • 9. Re: Recursive Calculation let() List Loop Question
                          cateringtraveller

                          Thanks ch0c0halic,

                          a very nice explanation of how a cf works.

                          • 10. Re: Recursive Calculation let() List Loop Question
                            weaverd

                            Hello - I have been attempting to get this to work for some time but cannot. I really really really struggle with recursive custom functions. I want to do virtually the same thing as in the original post - that is take a list of numbers and do some calculations on them, returning another list of numbers. I could do all of this on related records, but my aim is to graph the results and view in a popover in a portal. That I can only do if the data belongs to the table occurence in the portal rather than a related table occurence further away. Alternatively I could use Execute SQL, or scripts, but would prefer not to as I would very much like to understand how this (and recursive functions in general) recursive function works.

                             

                            To test the CF I recreated it almost as above, and simply asked it to return the values in the original list, rather than do calculations on the values in the list. My list was simply return delimited values from 0 to 300 in increments of 10 (

                            0

                            10

                            20

                            30

                            40

                            50

                            60

                            70

                            80

                            90

                            100

                            110

                            120

                            130

                            140

                            150

                            160

                            170

                            180

                            190

                            200

                            210

                            220

                            230

                            240

                            250

                            260

                            270

                            280

                            290

                            300)

                            , and as written below I would have expected this CF to return that same list, but I get nothing. I tested various parts of the CF that I could in the Data Viewer, for example "$value_list"  returned my original list, and when  "current_value" and "result" were included and I manually changed "line_number" the result was the value of that line from the list as expected. "results" and the final Case statement couldn't be tested in the Data Viewer. If I can get this to work, then I can "hopefully" modify the CF further to undertake the calculations I need. This will need additional variables added to the CF, and sub calculations done as part of the Let function.

                             

                             

                             

                            Data_extractor ( related_field ; line_number ; results)

                             

                            Let ( [

                             

                            $value_list = Case ( IsEmpty ( $value_list) ; List ( related_field ) ; $value_list );

                             

                            current_value = GetValue ( $value_list ; line_number) ;

                             

                            result = current_value;

                             

                            results = result & ¶ & results ] ;

                             

                            Case ( IsEmpty ( current_value) ; result ; Data_extractor ( related_field ; line_number + 1 ; results) )

                             

                            )

                             

                            Thanks in advance

                            • 11. Re: Recursive Calculation let() List Loop Question
                              erolst

                              Some notes: if you want to use a $var, use if for a good cause – and if you want to process a list, pass a list as an argument, rather than calculate it each time, or put it into a $var; i.e.

                               

                              /*

                              ProcessRecursively ( theList ) – if you want to process a list, pass the list upfront

                              */

                              Let ( [

                                $i = $i + 1 ; // initialize counter

                                curVal = GetValue ( theList ; $i ) ;

                                result = curVal * 2 // process somehow, e.g. multiply

                                ] ;

                                Case ( $i > 1 ; ¶ ) & result &

                                Case (

                                  $i = ValueCount ( theList ) ;

                                  Let ( $i = "" ; "" ) ; // reset counter

                                  ProcessRecursively ( theList )

                                )

                              )


                              e.g. ProcessRecursively ( List ( SomeTable::someField ) )

                              • 12. Re: Recursive Calculation let() List Loop Question
                                weaverd

                                Thankyou very much. That works perfectly. Not sure I understand completely why it works and my suggested CF doesn't.

                                 

                                For the first time in any explanation/example code/tutorial/white paper/textbook/video clip of a recursive CF I have explored since FM7 is it now clear that the recursive nature of the CF is the whole CF, not just part of the CF that has the incrementing or decrementing part. That is, the entire CF code commences again from the beginning of the CF. Hence in your CF the counter increments by 1 each time (recursion)  the CF is called.


                                regards

                                • 13. Re: Recursive Calculation let() List Loop Question
                                  erolst

                                  weaverd wrote:

                                  Not sure I understand completely why it works and my suggested CF doesn't.

                                   

                                  Yours doesn't work because this

                                   

                                  Case ( IsEmpty ( current_value) ; result ;

                                   

                                  should read

                                   

                                  Case ( IsEmpty ( current_value) ; results ;


                                  That's bound to happen when you use variables with (too) similar names.


                                  Even when working it isn't a good implementation IMO because


                                  1. The function itself is responsible for creating the source list – which it shouldn't be

                                  2. It is thus limited because you cannot pass it an existing list

                                  4. The function signature is complicated due to the two dummy arguments …

                                  4. … for which you must pass (and know/remember) the correct dummy values, i.e. 1 and ""

                                  5. It doesn't clear the helper $var it is using.


                                  btw, you can do without any helper arguments / $vars by passing an ever-dwindling list:


                                  /*

                                  ProcessRecursively ( theList ) – if you want to process a list, pass the list upfront

                                  */

                                  Let ( [

                                    curVal = GetValue ( theList ; 1 ) ;

                                    result = curVal * 2 ; // process somehow, e.g. multiply

                                    remainder = MiddleValues ( theList ; 2 ; ValueCount ( theList ) - 1 ) ;

                                    remainder = Left ( remainder ; Length ( remainder ) - 1 ) ;

                                    carryOn = not IsEmpty ( remainder )

                                    ] ;

                                    result &

                                    Case (

                                      carryOn ;

                                      ¶ & ProcessRecursively ( remainder ) ;

                                    )

                                  )