Brian Dunning's FileMaker Custom Functions

ConditionalSummary ( TypeField ; Type ; SumField )

Sum a SumField for records where TypeField matches

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

Koji Takeuchi   Koji Takeuchi
TonicNote, Inc.
https://tonic-note.com

Share on Facebook Share on Twitter

  Sample input:
ConditionalSummary ( cat ; "a" ; num )
  Sample output:
30

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

ConditionalSummary ( TypeField ; Type ; SumField )
2011.07.13, Koji Takeuchi

description:
Sum a SumField for records where TypeField matches "Type" param.
Original concept by TypeSumField ( TypeField ; Type ; SumField ; Counter ) function by Bruce Robertson , thanks Bruce and David.
This function is "no Counter param" version.
2012.03.13 Updated to solving a bug spotted out by Bruce Robertson. Thanks, Bruce.
ex1:
FoundCount = 3
record 1: cat = "a", num = 10
record 2: cat = "b", num = 5
record 3: cat = "a", num = 20
ConditionalSummary ( cat ; "a" ; num )
-> 30
ex2:
List ( child::cat ) = "a¶b¶a"
List ( child::num ) - "10¶5¶20"
ConditionalSummary ( child::cat ; "a" ; child::num )
-> 30

 

Comments

Guillermo Dewey   Guillermo Dewey, Monterrey, Mexico
Jan 18, 2012
After hours and hours spent trying to Sum() in with a condition and not being able to acomplish the task, this function made my day :) thanks a lot for sharing!!

For some reason I was not able to make the Bruce Robertson's function.
 
Bruce Robertson   Bruce Robertson
Mar 4, 2012
This function will not return accurate results if there are "holes" in the data. (Empty values in the sum field).

That's why I used the counter in my function; and why I required supplying the found count or related count so that you count down from the max found or related record. There may be ways to write it without the counter but I don't think this function works as written.
 
Koji Takeuchi   Koji Takeuchi, TonicNote, Inc.
Mar 12, 2012
Thanks for your comment, Bruce.
That's true. You really hit the spot!

I revised this function. It's terribly complex but I think this version works fine...
 
Lisa   Lisa, Frankfurt
Mar 16, 2013
I insert the function in a calculationfield, place the field on my total summary layout part and it always shows all results not the results from foundset.

I check in the calculationsettings "not stored" and it shows only the value of one record in the foundset.

???????

Please help.

Lisas4@hotmail.com
 
Koji Takeuchi   Koji Takeuchi, TonicNote, Inc.
Mar 28, 2013
Hi Lisa,

Please make sure the calc field is "unstored".
 
Jay Gui   Jay Gui, Singapore
Apr 18, 2013
Hi,

I tried using the Custom Function above, but I cant seen to get it to work.. As when I click ok: I get this Error at this part of the code

"An operator (e.g. +,-,*,...) is expected here"

Case (
$$ConditionalSummary_counter > 1 ;
val + ConditionalSummary ( TypeField ; Type ; SumField ) ;

Let(
$$ConditionalSummary_counter = "" ;
val
)
)
)

Please advise what I can do, as this is a very good thing to have in reporting function.
 
Ignacio   Ignacio, Sydney
May 23, 2013
Works flawlessly for me. Thanks Koji.
 
Michael   Michael, USA
May 29, 2014
Is it possible to modify this two have two conditions?
 
Koji Takeuchi   Koji Takeuchi, TonicNote, Inc.
May 29, 2014
Michael, You can do it.

1. Add TypeField2 param to this function and recursive call part in last "Case".
2. Modify "val" variable as following.

org:
val =
Case (
Type = "Total" or
Type = GetNthRecord ( TypeField ; $$ConditionalSummary_counter ) ;
GetNthRecord ( SumField ; $$ConditionalSummary_counter ) ;

0
)


OR version:
val =
Case (
Type = "Total" or
Type = GetNthRecord ( TypeField ; $$ConditionalSummary_counter ) or
Type = GetNthRecord ( TypeField2 ; $$ConditionalSummary_counter ) ;
GetNthRecord ( SumField ; $$ConditionalSummary_counter ) ;

0
)


AND version:
val =
Case (
Type = "Total" or
(
Type = GetNthRecord ( TypeField ; $$ConditionalSummary_counter ) and
Type = GetNthRecord ( TypeField2 ; $$ConditionalSummary_counter )
) ;
GetNthRecord ( SumField ; $$ConditionalSummary_counter ) ;

0
)
 
Sam   Sam, san jose
Jul 8, 2014
Hello Koji,

For using the two variables I am not sure about 'recursive call part in last Case'.

Could you please post a custom function and post it here.

Thank you
 
Renee   Renee, Naperville
Apr 8, 2015
Hi,
I'm working with this function and it almost does what I need it to. It actually does what I want, but gives me a pop up saying that the "specified field cannot be found". The reason it says this is because, for the last last parameter, instead of inserting a field, I insert Count(field). Like I said, it gives me the right results in the viewer/watcher, but gives me that popup error message first. I don't know enough about Let statements to be able to modify it for myself. Basically if field A = cat, I need to count field B. So Something like

ConditionalCounter ( Field A; "cat"; Field B).

Can you help?
 
Aleksandar   Aleksandar, Macedonia
Jan 29, 2016
Thank you. A life saver
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.