2 Replies Latest reply on Oct 14, 2010 11:43 AM by nanders

Calculate field based on value(s) in related table

Title

Calculate field based on value(s) in related table

Post

FileMaker Pro 8.5

I am working with two tables:

1) Elementary Education Assessment

2) INTASC

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!

• 1. Re: Calculate field based on value(s) in related table

You apparently have this relationship:

ElementaryEd::StudentID = INTASC::StudentID

Any chance you can define this calculation in INTASC instead of Elementary ED? That would be pretty simple to set up and different display techniques can be used to show this data for all subjects on an ElementaryED layout.

If not, there's another approach, but it's a lot of work to set up:

You can make as many relationships between these two tables as you need, using additional table occurrences so that the relationships can differ.

Using your example, you can define a field in ElementaryEd that contains "ED" and another with "424"--these can be data fields or calculations--which option's best depends on what you need here. Now you can create this relationship:

ElementaryEd::StudentID = INTASC 2::STudentID AND
ElementaryEd::EDField = INTASC 2::Class AND
ElementaryED::424Field = INTASC 2::Class #

To create INTASC, select it in your relationship graph and then click the button with two green plus signs.

Now you can define a calculation in ElementaryED that refers to field 1a in INTASC 2 and it will refer to the desired field.

Note: Given the complexity of adding so many additional relationships, you may want to define just two of these fields in ElementaryEd as data fields and use a script to set different values in them, calculates the values needed and then stores them in the appropriate data field in ElementaryEd.

• 2. Re: Calculate field based on value(s) in related table

PhilModJunk,

Thanks so much for the prompt response!  I think it would be possible to define the calcuation in INTASC so I will try to approach it from that angle. I really appreciate your help.