Your example runs this data together, making it very difficult to read. What value do you want to use to separate each set of scores and years?
A calculation field, cScoreYear, that returns text can be defined in test as:
Year & "-Score" & Score.
Then a calculation field can be defined in Students as:
List ( Test::cScoreYear )
This will produce the list you specify, but with a return between each year-score combined value. You can use the substitute function to replace the return with / like you have in your example:
Substitute ( List ( Test::cScoreYear ) ; ¶ ; "/" )
Great, that works super. Use a "-" for the separator.
Now they want a set of fields for each year, with the score in the field if the Student took the test that year.In the Student table.Example would be If the Student took the test in 2008 there would be a score in the 2008 field, etc. If the Student did not take the test in 2009 the field would be empty.
Use a relationship that matches by student ID and by year and then the same method will work, but returns a list for the specified year.
Students::gSelectedYear = TestByYear::cYear AND
Students::__pkStudentID = TestByYear::_fkStudentID
TestByYear is a new occurrence of your Test table so that existing relationships remain unmodified. gSelectedYear can be a global number field and cYear can be a calculation field that uses the year function to extract the year from your date field.
Ps. and if you are using FileMaker 12, you may want to investigate using calcualtion fields with the ExecuteSQL function instead of this approach.
I am not quite following. I setup the new relationship and if I enter a year on the Student side it does work. But how do I enter all 6 years into a single field for the relationship to work for all years?
What I spelled out provides you with the list for one selected year.
Using this approach for multiple years, you'd need one relationship and one year field in the students table for each such year.
This is why I mentioned ExecuteSQL as an alternative approach as it does not require adding stuff to your relationships graph to get these lists for different years at the same time.
We are in FM11A and i do not know SQL
So it appears we will need 6 relationships? 2007, 2008, 2009, 2010, 2011,2012
It would seem so.
You might use calcualtion fields for the other 5 so that you enter one year in to the global field and each of the others add 1, 2, 3 etc. to produce a range of years in the other fields.
Thanks, up and running