I'm building a DB to manage distribution of my clients product into a network of retail agents.

To do this I've created two files. File one contains my six clients. File two contains the details for the retail network of 5,000 agents.

I've created a relationship between the two by adding a "key" field on both files which auto adds the letter "A" to the field for all clients and agents. This allows me to create a portal on each agent (file two) record which displays a list of the clients from file one.

While all the retail agents are used by all clients they categorise them independantly (A, B, C, etc.) based on sales performance.

My problem is this. Is there a way to add a field to each client record displayed in the portal on each agent record which allows each client to categorise each agent independantly?

I've tried to do this but changing one field in the portal affects all the portal fields.

Any help much appreciated.

TIA

This problem is not so big really, depending on how you want your database to look and work. The trick here is to create a new file called ratings to store each rating from each client for each agent. You can then relate to this from your agent file to display client name and rating in a portal.

To explain this here would take too long. I’ve created a very simple version for you, which you can then take apart to see how it works (I just sent it to your email). I created it in FM 4.1, so if you are using 5 you will have to convert it. I think it’s pretty self explanatory, but if you have problems with it, just let me know. However, I’m going on holiday today until the 18. September.

I’ve left the structure pretty open, i.e. All fields displayed etc. and have not created any scripts or user interfaces. With some good script work here, I think you can get what you want.

Good luck,

Rigsby

Fantastic!

Now lets see if I've got this right. As i have 5,000 agents and 6 clients, the ratings file will end up with 30,000 records (1 record for each agent by each client).

Next question.

Is there a simple way to duplicate an new agent or updated agent record to the ratings file by the number of clients (every client rates every agent )?

Also can I automatically create an agent "set" in the ratings file when I add a new client?

Sorry to pick your brains so much but i can't get my head around the relational database stuff .

TIA