TypeSumField ( TypeField ; Type ; SumField ; Counter )

Sum a numeric field for records where another field matches input value.

Average rating: 4.2 (43 votes) Log in to vote

Bruce Robertson - Show more from this author

Sample input:

TypeSumField(
Invoice_Items::Category;
"Labor";
Invoice_Items::Total;
count(Invoice_Items::InvoiceID)
)

Sample output:

325.50

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

/*
TypeSumField( TypeField; Type; SumField; Counter )


This function will sum all SumFields whose TypeField = Type
Counter should = count of related records or
Count of found set depending on use
You may specify type as "Total" to get total of all records

Adapted from original concept and discussion by David Jondreau on FMForums:

http://fmforums.com/forum/topic/50854-typesumfield-typefield-type-sumfield-start/

Bruce Robertson
Concise Design
v1.1 
2/3/2011
Added ability to have multiple return-separated values for Type


For example:
TypeSumField( Line_items::Category ; "Parts¶Labor"; Line_Items::Extended_total ; 50 )

*/


Case( 
counter;

Case(
ValueCount( type) > 1;
TypeSumField( TypeField; GetValue( Type; 1); SumField; Counter ) + 
TypeSumField( TypeField; RightValues(Type; ValueCount( Type)-1); SumField; Counter ) ;

Let([

Type= getvalue( type; 1);
total = Case(

Type = GetNthRecord(TypeField; Counter) ;
GetNthRecord(SumField; Counter) ;

Type ="Total";
GetNthRecord(SumField; Counter) ;

0 )
];

total + TypeSumField(TypeField;Type;SumField; Counter - 1 ) 

)))

Sum a numeric field specified by SumField across multiple records, for records where field specified as TypeField matches a value such as "Labor" or "Parts". If you enter "Total" as the type then you get a total for all records.

Another example:
Let( RC = count(Invoice_Items::InvoiceID);
List(
"Labor: " & TypeSumField(
Invoice_Items::Category;
"Labor";
Invoice_Items::Total;
RC
);
"Parts: " & TypeSumField(
Invoice_Items::Category;
"Parts";
Invoice_Items::Total;
RC
);
"Total: " & TypeSumField(
Invoice_Items::Category;
"Labor";
Invoice_Items::Total;
RC
)
)

Comments

		Chris, Happyville Apr 22, 2011
Hello, thanks for providing this custom function. However, I don't understand what the counter is for? How can I use the script without providing a counter?

		Bruce Robertson Mar 4, 2012
As written, you can't use it without a counter. The instructions and example both show how to use the counter; which is either the count of related records or found count depending on how you are using the function. The function collects data by capturing data from the last record and iterating down to the first. Bruce

		Jerome, Milan Oct 29, 2014
Using the function, it runs properly at first. However, havgin experimented with it, gradually adding groups of records, it stops adding to the sums, somewhere between 91 and 113 records.

		Clayton King Sep 20, 2021
Is this function supported in FM19? I'm a total noobie at custom functions, so not sure what I'm doing... I've pasted the definition above into the the custom function dialog in FM, but I get an error that "The specified parameter cannot be found" referring to the counter ("Case(counter;).

		Bruce Robertson Sep 20, 2021
It works fine in 19. You need to learn how to use the custom function dialog and the tool it contains for entering parameters. Tutoring you on that is beyond the scope of what I can do in a reply.

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

TypeSumField ( TypeField ; Type ; SumField ; Counter )

Comments

Support this website.

Support this website.