6 Replies Latest reply on Mar 4, 2014 6:05 AM by CourtneyHart

    create a field that averages data based on a shared ID in another field

    CourtneyHart

      Title

      create a field that averages data based on a shared ID in another field

      Post

           I have a table that will ultimately contain entries with a measured value in a field and I need to be able to display a mean for entries that share the same ID number.  Basically I want a field that will find the entries with a matching ID (matches the entry in the parent table) and then displays the average for that field.    Is this possible?  How?

        • 1. Re: create a field that averages data based on a shared ID in another field
          philmodjunk

               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 )

          • 2. Re: create a field that averages data based on a shared ID in another field
            CourtneyHart

                 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?  

            • 3. Re: create a field that averages data based on a shared ID in another field
              philmodjunk

                   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.

              • 4. Re: create a field that averages data based on a shared ID in another field
                CourtneyHart

                     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.

                • 5. Re: create a field that averages data based on a shared ID in another field
                  philmodjunk

                       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.

                  • 6. Re: create a field that averages data based on a shared ID in another field
                    CourtneyHart

                         That's very helpful, I will try it immediately.  Thank you!