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.