I have 3 tables listed:

1.State Appts

2.Cert Footprint

3.Qualified Cert Footprint

1. contains fields "state" and "carriers"

2. contains fields "state" and "agent"

3. contains fields "state" and "agent" and "carriers"

I have created relationships of each of these to a 4th table which acts as the portal window. I have two portals currently. The relationships for 2 and 3 are based on "agent" so when I select a particular agent in the 4th table I get results showing the states they are licensed in the portal connected to 2. and I get the carriers the agent is appointed with in the portal connected to 3.

My challenge is to create a portal that will based on the states the agent is licensed in will show all the carriers in those states which is in table 1. Each agent is licensed in various states and not all agents are licensed in the same number of states so the data in the portal for 2 would always vary. This data however needs to be tied to table one somehow so each carrier will show up. this way I can see in one portal which carriers they are appointed with and in another portal all carriers available.

The final result is to take this data,compare and show which carriers the agent has yet to be appointed with (carriers available - carriers appointed with= carriers yet to be appointed with)

I hope this explains things well enough. I am using the latest version of Filemaker Pro and I work in a windows Vista enrivonment.

I'm not sure that I understand how you have structured your file.

You state that table StateApps has fields for state and carriers. Does this mean that you have a number of records in this table for a particular state, each one showing a different carrier? And in your CertFootprint and Qual CertFootprint tables, a similar situation with several agent records, each with different states, and several carrier records with different agents and states, respectively? If so, this structure seems to have a lot of redundant/duplicated data.

Or have I misread how your file is structured?

Is the attached of any help? I have structured it with what I have assumed are the three entities in your file - agents, states and carriers.

DJR.zip

So you really want a portal on an occurence of Table 1.

You'll need to start from your Table 4, join to Table 2 (by agent, as you've said) and then a join to an occurence of Table 1 (by state).