1 Reply Latest reply on Sep 24, 2013 6:53 AM by philmodjunk

    Is a Self-Join Relationship the Answer to This Problem?



      Is a Self-Join Relationship the Answer to This Problem?


      QUESTION: What means are available in FileMaker to display one field from multiple records (21), in one row on a List View Layout?

      BACKGROUND: I have hundreds -- eventually to be thousands -- of records which track a variety of risk factors relating to special education students.  Each record contains a score the student achieved in a certain risk factor area.

           The primary table of this system of tables contains various fields such as StudentName, TestDate, __IDRecord, EarnedScore, MinimumPassingScore, etc.  It also has one field which contains the risk factor title, "AssessCategory" (which stands for assessment category) , which was measured on that date for that particular student.  In total, there are 21 measured risk factors and so each student has a total of 21 records for each date they were tested.

           Normally, I would have created 21 different fields, one for each of the 21 risk factor areas, and placed the student's score for each area in a corresponding field.  However, after discussion with experts on this forum I decided to create just one dropdown field per record to do the job and populate it with the particular risk factor title and then I have a separate field for the actual score that goes with that risk factor title.  The risk factor title field is populated from a value list coming from a related table.

      PROBLEM: I want to display each record in both a Form View and a List View.  If I had created 21 separate fields, one for each of the risk factor titles (a more clumsy approach, indeed), I would have been able to simply place all 21 fields side-by-side and view/edit them in a row.  However, I can't do this since there is only 1 field handling all 21 titles.  I have been doing some reading research and I am wondering if a portal in a self-join relationship is the way to overcome this problem. True?

           Thanks, as always.



        • 1. Re: Is a Self-Join Relationship the Answer to This Problem?

               Option 1:

               Create (or use if you already have one) a table with one record per student linked to your table of test results. Set up a list view layout based on that students table and use a series of one row portals each with a filter that filters to a specific assessment category to get your columns of test results. If a student is tested repeatedly on different dates, you can add a global field to students and filter all your portals by both asessment category and also by date.

               Option 2:

               Set up your self join relationship matching by a student ID. Set up a list view layout based on the test results table and make each row a sub summary layout part "when sorted by" the student ID. Then the same set up of one row filtered portals can be used for the columns of test results. With this option, if you test your students repeatedly, you can perform a find for a date or range of dates to limit the records shown, but then you still need to filter your portals by the same date or date range in order to match to the correct records. (you may be able to include the test date as part of the self join).

               What you see on your table will look much the same in either case.