10 Replies Latest reply on Mar 21, 2012 1:19 PM by sporobolus

    Breakdown of a MASSIVE calculation

    ian.moree

      Hello Experts and Other FM Enthusiasts.

       

      Hopes this gets at least a little answer.

       

      I am trying to decipher how & why this works and would like any help that can be offered/ advised please.

       

      thanks in advanve,

       

      -ian

       

      [CODE]

      // ChargeFoundSet ( FieldNameList ; LockUnLock ) _v1.0 - not a CustomFunction but a CustomField

      // @ Agnès.

       

      // Get ( FoundCount ) ≤ 60000

       

       

      Let ([

       

       

      // --------------- parameter

      /*

      FieldNameList can be : IDRecord

      FieldNameList can be : Get ( RecordNumber ) & ". " & IDRecord & " " & FirstName & " " & Name

      FieldNameList can be : GetNthRecord ( MaTable::X ; 1 )

      FieldNameList can be can calculation fields etc... */

       

       

      FieldNameList = Case ( Get ( LayoutName ) = "ListeNP" ; IDRecord & " " & FirstName & " " & Name ; IDRecord ) ;

      LockUnLock = Lock // if LockUnLock are 1 : not calc. not list.

       

       

      ] ; // ------------------- Calcul --------------------------

       

       

      Case (

      LockUnLock <> 1 ;

       

       

      Case (

      Get ( RecordNumber ) = Get ( FoundCount ) ;

      Let ([

      $n = - 1 ;

      $rc = ¶ ; //declare 2 variables local $n & $rc

      End = Floor ( Get ( FoundCount ) / 150 ) ; // becomes 10 here

      //GetNthRecord ( fieldName ; recordNumber ) //main function

      Calc = "GetNthRecord ( ChargeFoundSet ; let ( $n = $n + 150 ; $n ) ) & $rc &" ;

      Eval = Case ( End > 0 ; Substitute ( ( 10 ^ end ) - 1 ; 9 ; Calc ) & """" ) ;

      CFSResult = Evaluate ( Eval )

      ];

      Case ( Filter ( CFSResult ; ¶ ) = CFSResult ; "" ; CFSResult ) ) )

      &

      Case (

      Get ( RecordNumber ) ≤ 1 or Mod ( Get ( RecordNumber ) ; 150 ) = 0 ; FieldNameList ;

      GetNthRecord ( ChargeFoundSet ; Get ( RecordNumber ) - 1 ) & Case ( not IsEmpty ( FieldNameList ) ; ¶ & FieldNameList )

      ) ) )

      [/CODE]

       

      Message was edited by: ian.moree

        • 1. Re: Breakdown of a MASSIVE calculation
          ninja

          You might want to remove agnes' email address...

           

          speaking of which...have you asked agnes?  looks like she would know the details of the script in the perspective of its use in your database.

           

          There will be some answers here, but from an outside view.

          • 2. Re: Breakdown of a MASSIVE calculation
            ian.moree

            Done. Didnt think of that. Hope she wont be piSD(Ed  .: (

            • 3. Re: Breakdown of a MASSIVE calculation
              Malcolm

              Have you tried it yet? At a glance it looks like it will build a list from a found set. If you need to know more you could kick start the discussion by telling us which part you don't understand.

              • 4. Re: Breakdown of a MASSIVE calculation
                BruceRobertson

                Or link to Agnes' original example file.

                • 5. Re: Breakdown of a MASSIVE calculation
                  ian.moree

                  1217591460-FoundSet_test_V1.fp7.zip

                   

                  Original file is on FmForums.com- This is link;

                   

                  here is website page.

                  http://fmforums.com/forum/topic/61394-found-set/page__p__290371__hl__chargefoundset__fromsearch__1#entry290371

                   

                  I dont know how to even try to test in data viewer. This is why i posted. Antoher question was why she did this and how it works/ why it works.

                   

                  -i

                   

                  *PS* i did email her personally, but the only answer i did receive was this technique was discovered after she wrote the CustomList Function.

                  • 6. Re: Breakdown of a MASSIVE calculation
                    Malcolm

                    Every record in the found set grabs data from the record preceding it. As a result, the data is aggregated.  By the time you get to the last record in the found set you have a complete list of values from the fields specified.

                    • 7. Re: Breakdown of a MASSIVE calculation
                      ian.moree

                      Here is some of my comments going through this again for the umpteenth time.

                       

                      Let ([

                       

                      FieldNameList = Case ( Get ( LayoutName ) = "ListeNP" ; IDRecord  & " " & FirstName & " " & Name ; IDRecord ) ;

                      // if ( Layoutname = "ListeNP" ; show IDRecord <fname> <name> else return IDRecord only )

                       

                       

                      LockUnLock = Lock    // if LockUnLock are 1 : not calc. not list.

                      it will only proceed id this is 0

                      ] ;   

                      Case (

                      LockUnLock <> 1 ; // is LockUnlock not equal to 1

                       

                       

                      Case (

                      Get ( RecordNumber ) = Get ( FoundCount ) ; // This makes the FoundCount = the RecordNumber currently on

                      Let ([

                      $n = - 1 ; // I guess she is accounting for a empty space so she is going back 1 space?

                      $rc = ¶ ;  // $rc defines a new pilcrow - makes it easier and better looking than pilcrows everywhere.

                       

                       

                      End = Floor ( Get ( FoundCount ) / 150 ) ; // Defines End variable to be equal to foundcount/150 ? why? Floor just cleans up the decimals..

                       

                       

                      GetNthRecord ( fieldName ; recordNumber ) // Get nth record of FIELD::fieldname; FIELD::recordNumber -- this basically gets the recordNumber from the fieldname currently on

                       

                       

                      Calc = "GetNthRecord ( ChargeFoundSet ; let ( $n = $n + 150 ; $n ) ) & $rc &" ; // ok here i am lost. Calc is a new variable that GetNthRecord ( OUR current calc field; // why does she add 150 to $n(-1) & adds carriage return to this)

                      Eval = Case ( End > 0 ; Substitute ( ( 10 ^ end ) - 1 ; 9 ; Calc ) & "\"\"" ) ;

                      //She is evaluating here . If End is greater than 0 ( found set i guess) then she Substitutes result of 10 to the power of End then puts the -1 back on> WHY? if there is a 9 found, put in the Calc result and \ i think?

                       

                       

                      CFSResult = Evaluate ( Eval ) // Defines a new variable CFSResult and evaluates result above on the fly..

                       

                       

                      ];

                      Case ( Filter ( CFSResult ; ¶ ) = CFSResult ; "" ; CFSResult ) ) ) // here she is filtering the CFSresult to only find ¶ characters and then put an blank space if true, else it just returns CFSresult

                       

                       

                      & // Below here i think is where i go crazy as to what the heck this is?

                      Case (

                      Get ( RecordNumber  ) ≤ 1 or Mod ( Get ( RecordNumber  ) ; 150 ) = 0 ; FieldNameList ;

                      GetNthRecord ( ChargeFoundSet ; Get ( RecordNumber ) - 1 ) & Case ( not IsEmpty ( FieldNameList ) ; ¶ & FieldNameList )

                      ) ) )

                      • 8. Re: Breakdown of a MASSIVE calculation
                        sporobolus

                        on 2012-03-20 3:12 ian.moree wrote

                        Here is some of my comments going through this again for the umpteenth time.

                         

                        i don't have time for a full analysis, so i'll just offer some comments where i

                        can tell what's happening by reading the code; i might have mixed something up,

                        but you should get the idea

                         

                        i do suggest that instead of trying to analyze something so abstract that

                        Fabrice (who is no slouch) said on fmforums "Once I've understood your code (it

                        might be in a month or two) …", you might instead work your way up through

                        simpler calcs

                         

                        this would have been easier if — from the start — you had provided the full

                        context so we knew where you were starting from; for one thing it is essential

                        to know that the calc in question is in the definition of an unstored calc

                        field named ChargeFoundSet (which makes the field itself quasi-recursive as it

                        retrieves the values of itself from previous records)

                         

                        i would also suggest adopting an indentation scheme for complex code; it helps

                        a lot with comprehension

                         

                         

                        LockUnLock = Lock     // if LockUnLock are 1 : not calc. not list.

                        it will only proceed id this is 0

                        ] ;

                        Case (

                        LockUnLock <> 1 ; // is LockUnlock not equal to 1

                         

                         

                        Lock is a field on the layout in the example from which this is drawn; it is

                        used to turn off the automatic calculation

                         

                         

                        Case (

                        Get ( RecordNumber ) = Get ( FoundCount ) ; // This makes the FoundCount = the RecordNumber currently on

                         

                        no, this is a test of whether the current record is the last record in the

                        found set

                         

                         

                        Let ([

                        $n = - 1 ; // I guess she is accounting for a empty space so she is going back 1 space?

                         

                        no, $n is simply assigned the value minus one; note below that the first time

                        $n is used, 150 is added to it before it is used

                         

                         

                        End = Floor ( Get ( FoundCount ) / 150 ) ; // Defines End variable to be equal to foundcount/150 ? why? Floor just cleans up the decimals..

                         

                        because the process chunks the records into groups of 150

                         

                         

                        GetNthRecord ( fieldName ; recordNumber ) // Get nth record of FIELD::fieldname; FIELD::recordNumber -- this basically gets the recordNumber from the fieldname currently on

                         

                        this is out of context, but it gets the value of the field named fieldName in

                        the record specified

                         

                         

                        Calc = "GetNthRecord ( ChargeFoundSet ; let ( $n = $n + 150 ; $n ) )&  $rc&" ; // ok here i am lost. Calc is a new variable that GetNthRecord ( OUR current calc field; // why does she add 150 to $n(-1)&  adds carriage return to this)

                         

                        again, because the process chunks the records into groups of 150; note that

                        Calc is a string containing an expression; nothing will happen until it is

                        evaluated; when it is evaluated below, it may be evaluated multiple times, in

                        which case each time it is evaluated, $n is incremented by 150 before it is

                        used the next time (a clever trick, but rather abstruse)

                         

                         

                        Eval = Case ( End>  0 ; Substitute ( ( 10 ^ end ) - 1 ; 9 ; Calc )&  "\"\"" ) ;

                        //She is evaluating here . If End is greater than 0 ( found set i guess) then she Substitutes result of 10 to the power of End then puts the -1 back on>  WHY? if there is a 9 found, put in the Calc result and \ i think?

                         

                        the evaluation doesn't happen until the next step … first a trick is used to

                        duplicate the string in Calc a certain number of times; this works by creating

                        a long string of "9" characters by subtracting 1 from a power of 10, then

                        replacing the 9s with the contents of Calc; the process supports up to 60000

                        records, so End is at most 40, so the result will be a list between 1 and 40

                        copies

                         

                         

                        CFSResult = Evaluate ( Eval ) // Defines a new variable CFSResult and evaluates result above on the fly..

                         

                        Eval contains several copies of a calculation, the results of which will be

                        separated by returns when evaluated; so the result is a "list" containing what

                        was in the field at each of several records, but

                         

                         

                         

                        ];

                        Case ( Filter ( CFSResult ; ¶ ) = CFSResult ; "" ; CFSResult ) ) ) // here she is filtering the CFSresult to only find ¶ characters and then put an blank space if true, else it just returns CFSresult

                         

                        if CFSResult contains only return characters, an empty string; otherwise the

                        contents of CFSResult …

                        >

                        // Below here i think is where i go crazy as to what the heck this is?

                         

                        … is appended to the following …

                         

                        Case (

                        Get ( RecordNumber  ) ≤ 1 or Mod ( Get ( RecordNumber  ) ; 150 ) = 0 ; FieldNameList ;

                         

                        if this is the first record or the record number is an exact multiple of 150,

                        then just return FieldNameList …

                         

                        GetNthRecord ( ChargeFoundSet ; Get ( RecordNumber ) - 1 )&  Case ( not IsEmpty ( FieldNameList ) ; ¶&  FieldNameList )

                        ) ) )

                         

                        … otherwise get the previous record and prepend it to the current value;

                        *BUT* remember, getting this record triggers an unstored calc, so it

                        effectively fires this whole calculation again, which gets the previous record

                        and so forth; this fires off a chain that retrieves up to 149 more records in a

                        sequence, and, since this is fired off up to 40 times by the Eval() trick

                        above, the effect is to divide what would be an effectively-recursive

                        60000-pass calc into 40 150-pass recursions

                         

                        okay that's pretty cool, Agnès!

                        • 9. Re: Breakdown of a MASSIVE calculation
                          ian.moree

                             Hey Steve;

                           

                          Upon putting this up, i sent Agnés a few email messages as suggested by Ninja and she explained exactly as you have above so kudos to you and thank you for taking the time to decipher this as well. I definately understand a lot more and i am sure this will help others as well.

                          two things she pointed out thought which were important was when using GetNthRecord( because it is grabbing the nthRecord) from an unstored calc, unindexed we need to use this formula>

                           

                          GetNthRecord ( ChargeFoundSet ; Get ( RecordNumber ) - 1 )

                           

                          Also for the limitation on NUmbers which i found the link here:

                           

                          http://help.filemaker.com/app/answers/detail/a_id/7061/~/technical-specifications-of-filemaker-pro-10-and-filemaker-pro-10-advanced

                           

                          so she does this trick which she says she loves:

                          Substitute ( ( 10 ^ n ) - 1 ; 9 ; "]" ) // if n > 404 the result are "?", for 3, the result are "|||"

                          Very cool thing she did here.. She didnt think others would find this very exciting, but i am a fan and i think you may be as well.

                           

                          -i

                          • 10. Re: Breakdown of a MASSIVE calculation
                            sporobolus

                            on 2012-03-21 1:13 ian.moree wrote

                            Very cool thing she did here.. She didnt think others would find this very exciting, but i am a fan and i think you may be as well.

                             

                             

                            well, there are a few independent "cool tricks" in this example, which is part

                            of why it's both hard to analyze and not the best instructional example; i

                            expect all of these tricks have had a solid discussion separately somewhere:

                             

                            • 10 ^ X - 1 --> string of X 9s --> replicate another string X times

                            • applying GetNthRecord() across a whole found set

                            • quasi-recursion by an unstored calc field referencing itself in the calc

                            • evaluating a string of repeated calcs to map an expression onto a set

                            • breaking operation on a found set into a "tree" of calls (40x150 in this

                            case) to limit stack depth