AnsweredAssumed Answered

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

Question asked by illumine on Sep 23, 2013
Latest reply on Sep 24, 2013 by philmodjunk


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.