Average-if custom function: possible?

[kirvis]

Hi all,

I am currently using a wonderful custom function called TypeSumField which I found here. In short, it makes it possible to sum a certain field from records that meet a certain condition. (i.e. if category = "Bread and pastries" then Sum( amount ) )

The source code for this function is:

//This function will sum all SumFields whose TypeField = Type

//Start should =0 or blank

Let([

End = Count(TypeField);

counter = Start +1;

total = If(GetNthRecord(TypeField;counter)=Type; GetNthRecord(SumField;counter) ; 0 )

];

Case(counter

))

What I need now is a comparable function, but then with average. So, that it calculates the average of the field "amount" of all records where the field "category" has the value "Bread and pastries".

Is it possible to do this with a custom function that is comparable to the TypeSumField custom function?

[bcooney]

You really don't need a CF for this*

Just use average (relationship:numfield) or sum (relationship:numfield).

Use a multi-predicate relationship to filter by the type that you are looking for.

If you are not looking from one table to a related table, use a Summary field. Summary fields operate on the found set in the current table. If placed in a sub-summary part, they will display a value by break field.

*in fact, a recursive CF fails on high record counts.

Edited January 18, 2010 by Guest

[David Jondreau]

Are you working on a related table or the same table? It's a little easier (unfortunately) to work on related tables.

If it is a related table then divide the result of TypeSumFiedl() by

ValueCount(FilterValues(List(related::TypeField); "text"))

If it's the same table, I can post a modified CF.