What is the best way to do this?

[gnarlydude]

The data base that I am trying to put together is for a life insurance agency. I pretty much have the client stuff figured out but I am wonder what the best way to track agents is.

In my Agent DB I will track basic demographics and personal info. But I also need to be able to track/view "downline/upline" agent info. Downline just means the agents an Agent has working under him/her (he gets a piece of thier action through commision splits). Upline is just the oposit, except there will only be 1 upline agent. In other words each agent can have many other agents working downline from him but works for only one other agent. Kind of sounds like a pyrimid scheme!

Anyway I am thinking that I would have a tabbed layout style with the first tab named "Agent", the second tab "Downline/Upline" and the third would be for company apointments (the companies this agent represents). I am imagining that when the "DL/UL" tab is clicked the layout would somehow display through a portal all the agents the Agent has working under him (with a self joining relationship?). The downline agents would be linked to the Agent by his unique agent number. This downline can/does extend more than one level deep, depending on the agents contrac. For example: Agent "A" has ten agents working for him numberd 1-10. Agent 5, has only 7 agents working under him numberd I-VII. So Agent VI is a downline agent of agent 5 and also of agent A and would have to appear on the "Downline" layout of both Agent A and Agent 5. I hope I am not making this overly complicated. What is the best way to track this?

To complicate matters each agent can and is "Apointed" with multiple companies. On his "Appointment" layout I need to track each current Appointment and what state(s) he is appointed in with any particular company. So let say that Agent A is appointed with Insurance Companies 1, 2 and 3. He is appointed with Company 1 in MN, ND, IA, SD and WI. With company 2 he is only appointed in MN, ND and SD. With Company 3 he is appointed in MN, ND, SD, and NE. How should I go about doing this one? Unlike my first question, I don't even know where to start here.

Any help would be greatly appreciated.

Joe

[LiveOak]

This is a tough one. I've written an MLM application in FM. It (or any standard database program) is far from ideal for this purpose. Linked list (graph) structures are best implemented in programs made for this purpose. The problem I had was reliably driving commission calculation up through the list. Your application is made not typical an even more difficult by having multiple links to the same agent from one or more agents. The only method I think will work to drive a calculation up through the graph is to use lookups starting with the lowest level, but in your connection an agents level is somewhat indeterminate if he is connected to uplines in two different levels.

If you don't need commission calculations, use a (self) join file with an entry in each record for an upline agent and a downline agent. For your insurance company connections, use a join file in which each entry is an agent/company/state.

Creating linked list structures in FM is really a research grade problem. If this was client work I would either turn the job down or get a signed contract that charged for effort, but disclaimed any guarantee of a successful result. I took the MLM contract AFTER spending an hour trying to talk the client out of FM, disclaiming any guarantee of result, having the client beg me to make an attempt, and finally being stiffed for a couple of grand based upon a partially successful result. Not a pretty picture.

-bd

[gnarlydude]

Thanks for the Reply Brent. No, I don't need to track commisions. I only need to track downline and upline agents. Could you elaborate a little on how you suggest I should do this? I am doing this for a friend/former employer so I am not under any pressure to get it done anytime soon.