My apologies for asking a question similar to ones that others have posted on the forum, but I can't seem to apply the solutions that I have read about to make our FileMaker Pro 12 database work the way that we want it to.
Background: I have been asked to re-build a database that was originally set up to track student participation and completion in various research programs. Some programs allow students to apply multiple times and they get a new record with each application. We now need to more closely track the participation and success of faculty members by pulling data from the student application records. I know that portals and reports can do most of this work, but I am having issues getting the data for the faculty records to correctly pull information from the various program tables (red, purple, and green tables in the attached screen shot).
Problem: When I try to pull similar information for faculty advisors related to Soph Jr Research, it does not pull all of the records for the faculty member, even if I try to filter by a field that exists in both tables and has the same data. It is strangely also pulling data from student records who may have also applied multiple times and with a different faculty members each time.
What works: I set up a dummy database and re-upload the data so that serial numbers would be assigned to the records and I created a new layout with portals to pull the relevant data for a particular faculty member by program. The portal for the Pres Scholar records seems to be working fine.
What doesn't work: I've tried creating various join tables (see bottom left of attached relationship graph), but maybe I'm not setting them up correctly. I've tried linking multiple occurrences of a table (e.g. faculty data) to the individual programs. I tested a theory that the issue was because there are multiple records in Soph Jr Research for the same student with different faculty advisors, so I created a duplicate student record in Pres Scholar and gave it a different faculty advisor. The faculty portal for Soph Jr Research still pulled records for other faculty members while the Pres Scholar one did not.
I imagine that I am missing something simple, but I've spent a lot of time trying to figure this out. Please help!