So you have this relationship?
ParentTable::__pkParentID = ChildTable::_fkParentID (Of course your table and field names are likely different...)
You have a number field defined in ChildTable and you want the average of that field for all child table records with the same ParentID value?
The simplest approach is to define a calculation field in the parent table as:
Average ( ChildTable::YourNumberFieldHere )
ok, so lets say my parent and child tables are related by a field for color. I want to display an average of the brightness value (why not..) for a given color on parent entries with that color. so I would define a field in the child table as: =Average(ChildTable::color)? In that cases if the parent color=blue then it would display the average for all child entries where color=blue?
You would need to define the calculation field in the Parent table, not the child table. And color must be of type number. You can't average text values.
I don't think I'm explaining this very well. Let me start over:
The parent::child relationship is a one-to-many relation via an ID field. That is, one ID# in the parent table has many entries in the child table. Each entry in the child table has multiple fields containing number values that describe different characteristics of that replicate of the parent ID. Essentially, I need to write a function in a field that will find all child entries that share the same ID as the parent table entry and return an average from one of number fields for all of those entries in the parent table. I need to automate finding all the child entries with an ID matching the parent entry, and then return the average of a different field from those entries.
That is exactly what I am describing. The "find all child entries that share the same ID" part is why you define this calculation in the parent table. The relationship between parent and child which is based on that ID field will match that one Parent record to all child records that "share the same ID value". The average function can then compute an average of the field in child that you select for that average. The fact that this field is defined in the parent table does not prevent you from placing this field on a child layout if you need to do that to show the computed average.
It is possible to define the calculation in the child table, but then you'd need to define a self join relationship between two occurrences of the child table, but since you already have a Parent to child relationship, there's no need to do that.
Your other option is to put the aggregate function inside a SQL query in an ExecuteSQL function call, but since you already have a Parent to Child relationship, there's no need to do that either.
That's very helpful, I will try it immediately. Thank you!