I'm new to Filemaker Pro, and trying to develop a database to track political party activities, just as a learning experience. See attached ERD. I have created a layout from point of view of "Roles" table, which includes a portal for "DemocratsinRoles" records. For example I've defined "Precinct Chair" as a Role, and when you view that Role you see a list in the portal of all the people in the Democrats table who have been assigned the role of Precinct Chair. It's very important that I display the Senate District ID that each precinct chair is from, and be able to sort on that field. The problem is that the Senate District ID is in the DistrictID field, way, way over in the Districts table, and each precinct is in multiple districts (Senate, House, Congressional, etc.) When I include the DistrictID field in the DemocratsinRoles portal, it displays the first DistrictID it finds, which happens to be the Congressional District. I have a DistrictType field in Districts which indicates whether the district is Senate, House, etc. Is there a way I can tell the DistrictID field in the portal to show only DistrictID where the DistrictType = "Senate District"? I've concluded that somehow I need to make a new table occurrence that relates the DemocratsinRoles table directly to the Districts table... but I'm not sure how to make that work right.
Thanks for any hints or suggestions!