A summary field, the average function, and ExecuteSQL might all be used to compute such a value.
The average function in the calculation for the field is what i have been trying to use. But it just leaves me a blank field, I don't understand what i am doing wrong.
All aggregate function follow specific rules for how they evaluate as should be documented in Help.
Average ( Field1 ; field2 ; Field3 ) will compute the Mean of the nonblank values of these three fields in the same record.
Average ( RepeatingField ) will do the same, but over the repetitions of this field in the same record.
Average ( RelatedTable::Field ) will do the same over the set of related records.
Note the reference to "nonblank".
Average ( 1 ;3 ; 0 ) yields: 4/3 or 1.333333
Average ( 1 ; 3 ; <empty> ) yields 4/2 or 2
"Average ( RelatedTable::Field ) will do the same over the set of related records." This is the calculation I went to first to give me the average and it is blank. It should not be. I just need it to average four records they all have numbers in the field.
I don't see how to do this as a summary as it is a related table and I don't see anyway to summarize from a related table. It may be there, but it is not obvious in the dialog.
1 of 1 people found this helpful
And does your current record match to any records in the related table?
In otherwords, if you placed a portal to the related table on this layout, would you see any records?
The result that you are getting suggests that you don't have any related records for the current record at the time this calculation evaluates.
If you want the average of all records from another table, then you need a Cartesian Join relationship:
EstimateTable::anyfield X PersonnelTable::anyfield
with X instead of = as the operator in the above relationship, Average ( PersonnelTable::Rate ) will produce an average over all the records in PersonnelTable. Also, if you defined an "Average of" summary field in PersonnelTable, you can refer to this summary field from the context of EstimateTable and you'd get the same value.
One way you could do this is to add a Summary field to the RelatedTable itself (I assume this table holds employees and their pay rates), which produces an Average of the pay field for all records.
When you then display this Summary field on a layout based on the parent layout, the relationship acts like a filter and the average shown will be the average only for related records.
I have tried this too, and still I get nothing in that field. I have checked and rechecked the relationship to that table and I do not see how it is any different then the relationship to the line item table which does work. I have also tried using the average field from the employee table as a look-up for this table, but that does not work either.
Please describe in detail how you are trying to use this average. This could be part of a calculation field, an auto-entered calculation or in a script step.
Please note that both context and relationship must be correct.
Finally figured out what you were saying here. This solved it, thank you.