I have a number of clients for whom I distribute literature to industry agents. While the "pool" of agents is constant, each client has preferred agents which they catagorise as A, B, C, D, E or F.

Each client catagorises the agents differently (eg. agent one may by catagorised as "A" by one client and as "D" by another client.

I have created two databases:

Clients.fmp holds a record for each of my clients.

Agents.fmp holds a record for each agent.

In Clients.fmp I have created a layout with a portal displaying the agent's names from Agents.fmp. So when I browse though the clients records it displays a complete list of agents avalable for each client.

What I need to do is add a field to the portal records which will allow me to enter a category letter for each agent which is specific to the client.

I've tried to add this ina number of ways without success. If someone could point me in the right direct I'd really appreciate it.

Thanks

This is a many to many relationship, meaning that you need to create a join file which will have an agent ID field, a client ID field and a category field. In it, you create a record for each instance of a client/agent category. You can then display this in either agent or client file with a portal.

Thanks Bob,

Does this mean I need to create a join file for every client?

No, there is just one join file. If there are 3 agents (X, Y, and Z categorised A, B, and C respectively) for client 1, that means 3 records in the join file:

Client, Agent, Category

1,X,A

1,Y,B

1,Z,C

and the same for client 2, 3, etc.

As you can see, there will be a lot of little records in this file. You should be able to script the creation of them though.

[ April 10, 2001: Message edited by: BobWeaver ]