5 Replies Latest reply on Oct 4, 2014 2:26 AM by erolst

    Calculating Avg based on date...

    alinicjones

      I have a database where i need to calculate the examiner's average only within 2 years. I have a table named Oral Exam and one name Facilitator.

      Oral Exam Fields:

      - OralExam ID

      - FacilitatorID (match field)

      - Oral Exam Date

      - Exam Score

      - Days Since Exam (Unstored, Calculation: Get (CurrentDate)-Oral Exam Date )

       

      Facilitator Fields:

      - Facilitator ID

      - Overall Oral Exam Avg (Unstored, Calculation: ( Average ( facilitator_ORALEXAM::Score ) ) )

      - Oral Exam Avg Criteria (Global) this is the amount of days that we want to see their examiner average from. Right now it is set for 730 days, which is 2 years.

      - 2Yr Oral Exam Avg this is the field i need to figure out how to calculate.

       

      I'm trying to do this though a relationship. But I must be doing something wrong. I made a copy of my Oral Exam Table Occurrence (TO) and connected it to my Facilitator TO. Matching the Facilitator ID and then connecting 2 fields I just created.

      Facilitator Table – Include in 2Yr Avg (Text)(Global, Auto-enter Calculation) “Yes”

      Oral Exam Table – Include in 2Yr Avg (Calculation) (Unstored, = If ( ( Days Since Exam <= oralexam_FACILITATOR::Oral Exam Avg Criteria ) ; “Yes” ; “” )

       

      I matched those two fields to = each other.

       


       

      I need to be able to use this field in a number of layouts so i can't just use a summary report option. I also need it to update itself. Any ideas and/or help you could provide would be greatly appreciated. Let me know if you need any further information. Thank you in advance.

        • 1. Re: Calculating Avg based on date...
          erolst

          In Facilitator, create a calculated date field as

           

          Get ( CurrentDate ) - Oral Exam Avg Criteria (number of Days)

           

          and create a relationship where

           

          Facilitator::calculatedDateField <= Oral Exam::date

          Facilitator::FacilitatorID = Oral Exam::FacilitatorID

           

          Now calculate your average via that relationship.

          • 2. Re: Calculating Avg based on date...
            erolst

            alinicjones wrote:

             

            I'm trying to do this though a relationship. But I must be doing something wrong. I made a copy of my Oral Exam Table Occurrence (TO) and connected it to my Facilitator TO. Matching the Facilitator ID and then connecting 2 fields I just created.

            Facilitator Table – Include in 2Yr Avg (Text)(Global, Auto-enter Calculation) “Yes”

            Oral Exam Table – Include in 2Yr Avg (Calculation) (Unstored, = If ( ( Days Since Exam <= oralexam_FACILITATOR::Oral Exam Avg Criteria ) ; “Yes” ; “” )

             

            I matched those two fields to = each other.

            As to this approach: you cannot use an unstored field in the “right” side of a relationship – and it needs to unstored for Get ( CurrentDate) to update correctly.

             

            That's why in my suggestion the date manipulation is performed on the frontend, instead of the backend.

            • 3. Re: Calculating Avg based on date...
              alinicjones

              Awesome!! Thank you so much!!!

              • 4. Re: Calculating Avg based on date...
                Mike_Mitchell

                "That's why in my suggestion the date manipulation is performed on the frontend, instead of the frontend."

                 

                I guess depending on which way you're viewing the relationship ... ?  

                • 5. Re: Calculating Avg based on date...
                  erolst

                  Mike_Mitchell wrote:

                  "That's why in my suggestion the date manipulation is performed on the frontend, instead of the frontend."

                  I guess depending on which way you're viewing the relationship ... ?  

                   

                  Right  … thanks for the catch.