4 Replies Latest reply on Jun 27, 2017 9:01 AM by deanster2u

    Field based on summary field of saved search?

    deanster2u

      New to FM. Suck at scripting. Thanks in advance for your help. Have looked around and gotten snippets of answers to what I'd like to do but looking for a tried and true streamlined way to do it.

       

      I have a table of 900 medical records. Two of the fields are birth year and birth weight. I have a saved search showing records of only each birth year along with a summary average field of birth weight for that particular year.

       

      On a form view layout I display all the vital stats in fields. I'd like another field that would get the birth year of the current record, and get the average birth weight from the summary field of the saved search for only children born in that particular birth year.

       

      What's the best way to approach this calculation? Should I even bother with the saved searches?

        • 1. Re: Field based on summary field of saved search?
          philmodjunk

          Can you explain why you are saving the search?

           

          And what do you want to do with the average birth weight once you have it?

           

          And how does the current record mesh with a saved find anyway?

           

          I think that you want to find the average birth weight for all records with the same year as that of the current record?

           

          That can be done with a self join relationship, no script, no added field, just set up the relationship and add your summary field from the related table occurrence. I can describe that in more detail, but first please confirm that I've guessed correctly as to what you want to see.

          • 2. Re: Field based on summary field of saved search?
            deanster2u

            philmodjunk thanks for replying. I may not need to save the search at all. It was one route I was taking to get the result of the average for any given birth year over time.

             

            What I need to do is compare the weight of the current record with the average weight of all records with that birth year for any given year as they age.

             

            The current record would be one record out of many in the saved search.

             

            "I think that you want to find the average birth weight for all records with the same year as that of the current record?" Correct both at birth and as they age, for comparison and charting purposes.

             

            I'd appreciate more detail if you can and hope I've answered your questions clearly.

            • 3. Re: Field based on summary field of saved search?
              philmodjunk

              Say your table is named "statistics" and you have a table occurrence "box" with that name in your relationship graph that is specified as the basis for your layout's context. (In other words you picked it from "show records from".)

               

              Click on that box in the relationship graph to select it and click the duplicate button (two plus signs). This makes a new occurrence of the same table. It does not duplicate the actual table. Now drag from birth year to birth year to link the two occurrences by birth year. You can double click the new occurrence in order to open a dialog where you can rename the second occurrence to be something like: Statistics|SameYear instead of Statistics 2.

               

              Now, on your layout add your average birth weight summary field from Statistics|SameYear to your layout and it will show the average birth weight for all records in the table with the same birth weight as the current record. You could also define a calculation field in this table as: Average ( Statistics|SameYear::Birth Weight ) and it would calculate the same value.

               

              Note that averages from either method omit nulls. If you have a record with no birth weight, it will not be included in the calculation to compute an average.

              1 of 1 people found this helpful
              • 4. Re: Field based on summary field of saved search?
                deanster2u

                philmodjunk Exactly what I wanted to do. Thank you!