GetDynamicStat ( Function ; TableOccurrenceName ; Field )

Get a Dynamic Aggregate result by chosing either the function, the related Table Occurrence and the field

Average rating: 4.1 (33 votes) Log in to vote

Ugo Di Luca - Show more from this author
DLSYSTEMS
http://www.dlsystems.fr

Sample input:

GetDynamicStat ( "Sum" ; "IN_Invoice" ; "AmountDue" )
GetDynamicStat ( $function ; $ToName ; $field )
GetDynamicStat ( scriptParameter1 ; scriptParameter2 ; scriptParameter3 )

Sample output:

20 if 3 records related with 10, 5 and 5 in the 'AmountDue' field.

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Let ( [

FunctionListToUse = 
Case ( 
Get ( ApplicationLanguage ) =  "Dutch" ; "Count¶Sum¶Max¶Min¶Average¶Last¶StDev¶StDevP¶Variance¶VarianceP" ;
Get ( ApplicationLanguage ) =  "English" ; "Count¶Sum¶Max¶Min¶Average¶Last¶StDev¶StDevP¶Variance¶VarianceP" ;
Get ( ApplicationLanguage ) =  "French" ; "Nombre¶Somme¶Max¶Min¶Moyenne¶Derniere¶EcartTech¶EcartPop¶Variance¶VarianceP" ;
Get ( ApplicationLanguage ) =  "German" ; "Anzahl¶Summe¶Max¶Min¶Mittelwert¶Letztes¶StAbw¶StAbwG¶Varianz¶VarianzG" ;
Get ( ApplicationLanguage ) =  "Italian" ; "Conteggio¶Somma¶Max¶Min¶Medio¶Ultimo¶DevSt¶DevStP¶Variazione¶VariazioneP" ;
Get ( ApplicationLanguage ) =  "Spanish" ; "Count¶Sum¶Max¶Min¶Average¶Last¶StDev¶StDevP¶Variance¶VarianceP" ;
Get ( ApplicationLanguage ) =  "Swedish" ; "Count¶Sum¶Max¶Min¶Average¶Last¶StDev¶StDevP¶Variance¶VarianceP"  
) ;

Type = MiddleWords ( GetFieldType ( TOName ; Field ) ; 2 ; 1 ) ;

Concat = TOName & "::" & Field ;
CountValues = Evaluate ( "Count" & " ( " & Substitute ( Concat ; "¶";";" ) & " )" ) 

] ;

Case ( Type = "Number";
Case ( 
Function = GetValue ( FunctionListToUse ; 6 ) ; Evaluate ( "Last" & " ( " & Concat & " ) " );
Function = GetValue ( FunctionListToUse ; 7 ) or Function = GetValue ( FunctionListToUse ; 8 ) or Function = GetValue ( FunctionListToUse ; 9 ) or Function = GetValue ( FunctionListToUse ; 10 )  ; 
Case ( 
CountValues < 2;  "Error" ; 
Evaluate ( Function  & " ( " & Substitute ("\"" & List ( Evaluate (Concat ) ) & "¶"  & "\" "; "¶" ; "\" ; " & "\"" ) & " )"
) ) ;
Evaluate ( Function  & " ( " & Substitute ("\"" & List ( Evaluate (Concat ) ) & "¶"  & "\" "; "¶" ; "\" ; " & "\"" ) & " )"
)
)
 ;

Type = "Date" ;
Case ( 
Function = GetValue ( FunctionListToUse ; 2 ) or Function = GetValue ( FunctionListToUse ; 7 ) or Function = GetValue ( FunctionListToUse ; 8 ) or Function = GetValue ( FunctionListToUse ; 9 ) or Function = GetValue ( FunctionListToUse ; 10 );  "Error" ;
Function = GetValue ( FunctionListToUse ; 6 ) ; Evaluate ( "Last" & " ( " & Concat & " ) " ) ;
Function = GetValue ( FunctionListToUse ; 1 ) ;
Evaluate ( Function & " ( " & Substitute ("GetAsDate ( " & "\"" & List ( Evaluate (Concat ) )  &"¶" & "\" )"; "¶" ; "\" ); GetAsDate ( " & "\"" ) & ")" ) ;
GetAsDate (
Evaluate ( Function & " ( " & Substitute ("GetAsDate ( " & "\"" & List ( Evaluate (Concat )  ) &"¶"   & "\" )"; "¶" ; "\" ); GetAsDate ( " & "\"" ) & ")" ) 
)
)
;

Type = "Time" ;
Case ( 
Function = GetValue ( FunctionListToUse ; 7 ) or Function = GetValue ( FunctionListToUse ; 8 ) or Function = GetValue ( FunctionListToUse ; 9 ) or Function = GetValue ( FunctionListToUse ; 10 );  "Error" ;
Function = GetValue ( FunctionListToUse ; 6 )  ; Evaluate ( "Last" & " ( " & Concat & " ) " ); 
Function = GetValue ( FunctionListToUse ; 1 )  ;
Evaluate ( Function & " ( " & Substitute ("GetAsTime ( " & "\"" & List ( Evaluate (Concat ) ) &"¶" & "\" )"; "¶" ; "\" ); GetAsTime ( " & "\"" ) & ")" ) ;
GetAsTime (
Evaluate ( Function & " ( " & Substitute ("GetAsTime ( " & "\"" & List ( Evaluate (Concat ) ) &"¶"  & "\" )"; "¶" ; "\" ); GetAsTime ( " & "\"" ) & ")" )
)
) ;

Type = "TimeStamp" ;
Case ( 
Function = GetValue ( FunctionListToUse ; 7 ) or Function = GetValue ( FunctionListToUse ; 8 ) or Function = GetValue ( FunctionListToUse ; 9 ) or Function = GetValue ( FunctionListToUse ; 10 );  "Error" ;
Function = GetValue ( FunctionListToUse ; 6 )  ; Evaluate ( "Last" & " ( " & Concat & " ) " ); 
Function = GetValue ( FunctionListToUse ; 1 )  ;
Evaluate ( Function & " ( " & Substitute ("GetAsTimeStamp ( " & "\"" & List ( Evaluate (Concat ) ) &"¶" & "\" )"; "¶" ; "\" ); GetAsTimeStamp ( " & "\"" ) & ")" ) ;
GetAsTimestamp (
Evaluate ( Function & " ( " & Substitute ("GetAsTimeStamp ( " & "\"" & List ( Evaluate (Concat ) ) &"¶"  & "\" )"; "¶" ; "\" ); GetAsTimeStamp ( " & "\"" ) & ")" )
)
) ;


Type = "Text" ;
Case ( 
Function = GetValue ( FunctionListToUse ; 7 ) or Function = GetValue ( FunctionListToUse ; 8 ) or Function = GetValue ( FunctionListToUse ; 9 ) or Function = GetValue ( FunctionListToUse ; 10 );  "Error" ;
Function = GetValue ( FunctionListToUse ; 6 ); Evaluate ( "Last" & " ( " & Concat & " ) " ) ;

Function = GetValue ( FunctionListToUse ; 1 );
Evaluate ( Function  & " ( " & Substitute ("\"" & List ( Evaluate (Concat ) ) & "¶"  & "\" "; "¶" ; "\" ; " & "\"" ) & " )" ) ;"Error" )
)
)

/*
Custom Function by Ugo Di Luca - DLSystems - http://www.dlsystems.fr
September 2006
Demo file soon available at http://www.dlsystems.fr

This is a FM 8.5 only function as it uses List ( ) - You may substitute List ( ) by one of the recursive function making use of GetNthRecord, as for example GetValueSet ( Field ; StartValue ) by Ray Cologon in order to make it work for FileMaker 8

--------------------------------------------------------------------------------
Objectives:
- Dynamically Evaluate the result of the following Aggregate Functions : Max , Min, Average, Count, Sum, StDev, StDevP, Variance, VarianceP + the Last ( ) function...
- Chose any Table Occurrence and Field on which the evaluation is based.

Example:
GetDynamicStat ( "Sum" ; "IN_Invoice" ; "AmountDue" ) will evaluate the Sum of Ithe field 'AmountDue' in the Invoice Table based on the 'IN_Invoice' Table Occurrence.

Type Cast :
This function can treat text, numbers, dates, times and TimeStamps.
The result is Text and no rounding is set by default for number types imputs

Regional Language :
This function evaluates the content of a formula, based on the Aggregate Functions according to the result of the Get ( ApplicationLanguage ) - Japanese not covered -

Usage :
All variables can be entered from global fields, any field or better script parameters or script variables, giving the developer a way to optimize coding.

Other Custom Function :
This function use another Custom Function, GetFieldType ( TOName ; Field )
Custom Function By Ugo Di Luca - DLSystems - http://www.dlsystems.fr
Let ( [
TOFieldJoin = TOName&"::"&Field ] ;
Evaluate (
"FieldType ( " & " " & "Get ( FileName) " & " ; " & "\"" & TOFieldJoin & "\"" & " )"
)
)

Limitations : At the moment, the evaluation take place on the current File Only + Evaluation is dependant of the Context

Comments

		Jim Randell, San Francisco Apr 26, 2010
just stumbled over this. looks kind of old, but will test, if works with 11, this is solution I have been thinking about, for aggregating filtered portals. setting a global as the portal filter criteria, then using same in this calc.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

GetDynamicStat ( Function ; TableOccurrenceName ; Field )

Comments

Support this website.

Support this website.