4 Replies Latest reply on Mar 25, 2010 10:17 AM by philmodjunk

    How to display a range of values in a related table

    IDM

      Title

      How to display a range of values in a related table

      Post

      Hi All,

      We currently use FileMaker Pro Advanced 10.0v3 running Windows XP Professional SP3.

       

      We have a small database with 2 tables. One table (Schools) holds student data with fields such as School, Grade Level, Student ID, Fall Score, and Spring Score. The second table (Scores) holds student scores with fields such as Student ID, Fall Below score, Fall At Level score, Fall Above score, and Spring Below score, Spring At Level score, and Spring Above score. In the Schools table we have the Fall Below, At Level, and Above score fields linking to the Scores table to display Fall and Spring scores related to each student. However we are not exactly viewing the results we want.

       

      We would like the Fall and Spring score fields to display those range of values specific to the Student from the Scores table. For example a student may score Below, At Level, or Above based on a score of ≤3 (Below), 4 (At Level), or ≥5 (Above). We would like to see (if possible?) thier  ≥5 score  (whether it is 5, 6, 7, or 10) to appear in the correct field named Fall Above score.

       

      Thanks in advance for your help! :smileywink:

      IDM

        • 1. Re: How to display a range of values in a related table
          philmodjunk

          IDM said:

           

          "We would like the Fall and Spring score fields to display those range of values specific to the Student from the Scores table. For example a student may score Below, At Level, or Above based on a score of ≤3 (Below), 4 (At Level), or ≥5 (Above). We would like to see (if possible?) thier  ≥5 score  (whether it is 5, 6, 7, or 10) to appear in the correct field named Fall Above score."

          Do you want to see a list of scores, an aggregate calculation such as an average, or just a single score for each student?

           

          Option 1: you could create a summary report that groups your students by how their scores fall in these categories. This report would be created on a layout based on the Schools table. Summary fields can be used to compute aggregate values like a total or average score.

           

          Option 2: You can define a series of relationships between Scores and Schools that are based on inequality operators to only link to records with scores in a specific range.

           

          Example:

          Define a calculation field, cSpringAboveKey in Scores to simply return the number 4.

          Create a new table occurrence of Schools, SchoolsSpringAbove and link cSpringAboveKey to Schools::Spring Score.

          Double click the relationship line and change the default = operator to <.

          Add the Student ID fields to this relationship

          This will give you: 

          Scores::cSpringAboveKey < SchoolsSpringAbove:: Spring Score AND  

          Scores::Student ID = Schools::Student ID

           

          You can use this relationship in a portal to show multiple scores in a portal, a Single score just by placing the field from SchoolsSpringAbove on a layout or you can create a calculation field that uses a function like sum or average to compute an aggregate.

          • 2. Re: How to display a range of values in a related table
            IDM

            Hi ,

             

             PhilModJunk said: "Do you want to see a list of scores, an aggregate calculation such as an average, or just a single score for each student?" Just a single score for each student. For example, if a student scores 4, we would like the Fall field to automatically look across the Scores table that contains the Fall Score column to see this number 4 should go into the Fall At Level field.

             

             

            Option 1 is something we would use going forward.

             

             

             

            Option 2 makes sense and am hoping after creating the calculation to return the number 4 and after updating the default relationship line to an = or < operator we have the students score appear in the Fall At Level field.

             

             

            I will try Option 2 today and let you know how we pan out tomorrow. Thanks for a quick reply!

            IDM

            • 3. Re: How to display a range of values in a related table
              IDM

              Hi PhilModJunk,

               

              Sorry I could not get back to you yesterday. We are coming to the end of month long testing at our district busy times indeed.

               

              Option 2 worked well only thing is we became concerned on whether we needed to create a cSpringAboveKey calculation for every value we want to return !!! :smileysurprised: While it is easy to setup the new calculation the concern was actually finishing the daunting task of creating these calculations for every value we wanted to return. Imagine doing the calculation for each Grade Level and all Scores (Below, At Level, Above) and then for both Fall and Spring....we simply did not have the time.

               

              Ultimately we decided to quickly create a script that would return the equivalent text value of student scores. For example if a student scored 5 or greater this means they are Above level. We wrote a simple script to return the text "Above", "At Level", or "Below", rather pull the score based on a range of numbers. Fact is hundreds of students score in groups of "Above", "At Level", or "Below" as these groups support the possible range of numbers we wanted to pull in the 1st place! :smileyvery-happy: Instead of displaying a student score based on a range of values we simply displayed the students score based on where they "fell" in these groups as text. Not only was the script easy to do it also allowed us to do Option 1 quickly.

               

              Here is our script:

              Enter Browse Mode

              Go to Layout ["Scores View"(scores)]

              Perform Find [Restore]

               - - - -

              double click on Perform Find to set Criteria:

              ensure Action is set at Find Records

              in Fiind records when, select your Table and select field to search

              in Criteria box to right enter your search criteria (we entered ABOVE)

              then select Add and OK back to the script screen

              if prompted Save Script

               - - - -

               

              That's all! and this works like a charm too! Many Thanks PhilModJunk your ideas were very much appreciated and indeed helpful,

              IDM

              • 4. Re: How to display a range of values in a related table
                philmodjunk

                I assumed your values were standardized. If they change, you could use a global number field in place of the calculated keys and simply select/enter different values into this field.