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