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

# Breakdown of a MASSIVE calculation

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.

-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

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

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

• ###### 3. Re: Breakdown of a MASSIVE calculation

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

Or link to Agnes' original example file.

• ###### 5. Re: Breakdown of a MASSIVE calculation

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

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

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

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

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:

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

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