8 Replies Latest reply on Nov 12, 2012 12:13 PM by jwshelton22

    Results of Child Records in Parent Record

    jwshelton22

      Title

      Results of Child Records in Parent Record

      Post

           I have a Students, a Student can take a test once a year and could have taken it from this year to each of the past 6 years. In the Test table, I have a record for each time the Student took the test and that Score. So if the Student took the test 3 times, there are three Test records for that Student.

           Each record has _kf_StudentID and there is a relationship of Student to Test by Student::__KP_StudentID = Test::_kf_StudentID.

           I need a field in Student that shows gives me the year taken and score for every time the Test was taken. So the result will be something like: 2007-Score77/2008-Score81/2009-NotTaken/2010-Score91/2011-NotTaken/2012-Score81

           I do not feel a portal will work as I need to export the contents of this new field as a single field showing all results.

           I need this on the single record for that Student.

            

           Thanks,

           Jim

        • 1. Re: Results of Child Records in Parent Record
          philmodjunk

               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 ) ; ¶ ; "/" )

                

                

          • 2. Re: Results of Child Records in Parent Record
            jwshelton22

                 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.

                 thanks,

                 Jim

            • 3. Re: Results of Child Records in Parent Record
              philmodjunk

                   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-----<TestByYear

                   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.

              • 4. Re: Results of Child Records in Parent Record
                jwshelton22

                     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?

                • 5. Re: Results of Child Records in Parent Record
                  philmodjunk

                       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.

                  • 6. Re: Results of Child Records in Parent Record
                    jwshelton22

                         We are in FM11A and i do not know SQL

                         So it appears we will need 6 relationships? 2007, 2008, 2009, 2010, 2011,2012

                    • 7. Re: Results of Child Records in Parent Record
                      philmodjunk

                           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.

                      • 8. Re: Results of Child Records in Parent Record
                        jwshelton22

                             Thanks, up and running