9 Replies Latest reply on Mar 20, 2017 8:54 PM by kaotishe

    Look up Average


      I am setting up a database to streamline product pricing. One of the things it will do is estimate labor cost as part of COGS. The thing is the labor can very dependent on which employee is doing it. As this is just a front end estimate that we use internally for pricing I would like for it to use the average pay for all of the employees. I know if I we getting the information for a single employee I could have it preform a look-up for that rate, but I would like it to determine the average rate for all employees. I am guessing a need to use a calculation to do this but I have not figured out how to write that.

        • 1. Re: Look up Average

          A summary field, the average function, and ExecuteSQL might all be used to compute such a value.

          • 2. Re: Look up Average

            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.

            • 3. Re: Look up Average

              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

              • 4. Re: Look up Average

                "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.

                • 5. Re: Look up Average

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: Look up Average

                    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.

                    • 7. Re: Look up Average

                      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.

                      • 8. Re: Look up Average

                        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.

                        • 9. Re: Look up Average

                          Finally figured out what you were saying here. This solved it, thank you.