Hey all you pros. I've been self teaching myself FMP for the last year or so and havn't been able to figure this problem out.

I'm building a database to keep track of customer policys and the agents who sell them.

My hierarchy goes like this:

Agency

-Agent

--SubAgent

Each policy has a main agent, but that agent could be either an "Agent" or "SubAgent".

I'd like to be able to run a report listing all agents & subagents and their policys.

Also, I'd like for the agent to be selected from a drop down and have their information to fill in automatically for each policy so the user won't have to type in an agent's information for each policy record.

Each policy also has:

-dependents

-payments

-notes

-etc.

The problem is that I can't get the Policys to relate to the agents and subagents. I've spent the last week working on this one problem and my deadline is coming up. Take a look at the file and you'll understand much better. Thanks.

policy_tracking.zip

What's the distinction between an "Agent" and a "SubAgent"? Couldn't they be based in the same table? Does every policy have both an Agent and a SubAgent? Is there a relationship between the Agent and its SubAgents, or is the relationship only in terms of each Policy?

"What's the distinction between an "Agent" and a "SubAgent"? "

SubAgents have a different commission payout rate than Agents.

"Couldn't they be based in the same table?"

I'll eventually be running reports to list agency, agents under that agency, and then every agent's subagent. The problem is that, subagents could eventually become agents. I've tried to figure out how to add a record to the Agents table everytime I add a subagent but havn't been successful. Also, an agent can have many subagents.

"Does every policy have both an Agent and a SubAgent?"

Not every policy has a subagent, however, every policy has an agency.

"Is there a relationship between the Agent and its SubAgents, or is the relationship only in terms of each Policy?"

A subAgent's policy will also count as an Agent's policy. However, an agent's policy does not appear on his subAgent's. It's like a pyramid scheme of sorts i guess.

Thanks for taking a look. Much appreciated.

Edited March 28, 200718 yr by Guest
forgot to add that an Agent can have many subagents

I am in agreement with Ender here. I believe that using one table of agents would make more sense here. There could be a designation of a title. Also a join table would be used to assign multiple subagents to an agent.

So basically the join table will have fields

MainAgentID SubAgentID

both field will related to the one Agents table.

If a SubAgent could only be a sub of one Agent, and a Policy's SubAgents are always the same set as the Agent's SubAgents, then I'd skip the join table and just use a ParentAgentID with a self-join. See attached.

I haven't added anything to the interface, but it shouldn't be too hard to select an Agent for a Policy, and from there, know who all the Sub Agents are too. Or if you want to allow the SubAgent to be the Agent for a policy, you can easily see who that Agent's parent Agent is.

policy_tracking.fp7.zip

I agree. To keep things really simple and foolproof, an Agent is someone who does not have a ParentAgentID. Entering a parent agent ID makes them a SubAgent. Clearing it makes them an Agent.

I concur with both of you. Not knowing too much about the insurance industry, I had just assumed that a subagent could be assigned to multiple agents and therefore suggest the join table. As Ender said, if each subagent is associated with one agent then a self join is the way to go.

Thanks for the help ender. I guess that does make more sense. I had to give up a few features i would've liked, but am so relieved its at least working.