Calculate field based on value(s) in related table
FileMaker Pro 8.5
I am working with two tables:
1) Elementary Education Assessment
These tables are related by Student ID#. The Elementary Education Assessment table has about 120 data points for each student, many of which can be calculated based on data points from student records in the INTASC table. Only one record can exist for each student ID# in this table. The INTASC table includes fields for the student’s ID#, class, class#, instructor, and 18 fields (1a, 1b, 2a, 2b, 3a, 3b, ……) for the entry of scores applicable to each respective class. Some classes are required to report data in only one field, while others are required to report scores for all 18 data points.
The problem I am facing is that I am unable to figure out how to pull the correct data from the INTASC table over to the Elementary Education Assessment table. For example, a student might have separate records in the INTASC table for each of the following classes: HPER 340, MUSC 301, and ED 424. Each of these classes reports on 1a, but for the data point in the Elementary Education Assessment table I only need the value from 1a for ED 424. How do I bring the 1a value for ED 424 over to the respective field in the Elementary Education Assessment table?
Thank you for your time and assistance!