ConditionalSummary ( TypeField ; Type ; SumField )
Sum a SumField for records where TypeField matches
Average rating: 4.5 (33 votes) Log in to vote
|
Koji Takeuchi - Show more from this author
TonicNote, Inc. https://tonicnote.com |
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
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, 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 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, 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, 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, TonicNote, Inc. Mar 28, 2013 |
|
| Hi Lisa, Please make sure the calc field is "unstored". |
||
|
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, Sydney May 23, 2013 |
|
| Works flawlessly for me. Thanks Koji. | ||
|
Michael, USA May 29, 2014 |
|
| Is it possible to modify this two have two conditions? | ||
|
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, 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, 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, Macedonia Jan 29, 2016 |
|
| Thank you. A life saver | ||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.