RepMatchSum ( dataField ; matchField ; matchValue ; reps ; recNum )

Sum repeating field for reps where label field = matchvalue

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

Bruce Robertson - Show more from this author

Sample input:

RepMatchSum ( "Qty"; "ProductID"; "X123"; 20 ; 6)

Sample output:

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

/*
RepMatchSum ( dataField; matchField; matchValue; reps; recNum )
For Nth record recNum, sums repeating field dataField
for all reps where repeating field matchField = matchvalue

If dataField = matchField then get count of occurrences of matchValue
*/

Let([
df = datafield & "[" & reps & "]" ;
mf = matchfield & "[" & reps & "]" ;
matchResult =  GetNthRecord( GetField(mf)  ; recNum) ;
dr = Case(matchValue = matchResult and (df=mf); 1;
matchValue = matchResult  ; GetNthRecord( GetField(df) ; recNum) )
];
 dr  +  Case ( reps > 0;  RepMatchSum ( datafield ; matchField ; matchvalue;  reps -1 ; recNum ) )
)

Ack! Summarizing repeating fields!

For nth record, get a total for a repeating numeric data field where a label field such as productID = matchValue.

When combined with CustomList or with another custom function, you can summarize repeating fields across a found set.

UPDATED 7/31/08
New feature to count matches if datafield and matchfield are the same.

Comments

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

RepMatchSum ( dataField ; matchField ; matchValue ; reps ; recNum )

Comments

Support this website.

Support this website.