I am creating a database that will be use to track changes in the status of a company. Status changes are triggered by actions of agents (and I need to be able to track which agents caused which change in status).

I managed to link the company and agents table together using a seperate table; then, I figure that to relate a specific company's status to an agent, I will need to create a multikey relationship (as seen in the screenshot below) between the CA.CompanyID/CA.AgentID and Status.CompanyID/Status.AgentID. I've also designed the value list in the status table such that only existing AgentID (corresponding to the selected CompanyID) will appear.

The problem I am facing is this:

Although a record of a specific CompanyID and AgentID instance already exist in the CompanyAgent table, whenever I create a new status with that same CompanyID and AgentID, another instance of that CompanyID and AgentID will appear in the CompanyAgent table. I do not want that to happen.

Any help in pointing out where I went wrong would be deeply appreciated.

whenever I create a new status with that same CompanyID and AgentID, another instance of that CompanyID and AgentID will appear in the CompanyAgent table.

Are you doing this in a portal placed on a layout of the Companies table?

Are you doing this in a portal placed on a layout of the Companies table?

Yes I am.

On the layout of the companies table, I have a Tab Control with 2 tabs - one with a portal of the companyagent table showing the Agent's information (such as their name address etc) and another with another portal of the status table showing the status of the company (with the corresponding agent).

Is that causing the problem; is yes, why and how what would you recommend I do instead?

Thank you for your help!

Hi,

I've recreated the layouts and now have 2 layouts:

1. Layout of company with a portal that is associated with CompanyAgent table

2. Layout of CompanyAgent with a portal that is associated with Status table

Now, when I create a new status using layout2, I don't see a duplication of record in the CompanyAgent table anymore. So yes, it is probably a problem with the layouts.

But when I was playing around with the database, I realise when I delete a status from the status table, the corresponding company is deleted from the company table as well. I've checked that the relationship between CompanyAgent and Company only allows deletion on the CompanyAgent side and that the relationship between CompanyAgent and Status only allows deletion on the Status side. Weirdly enough deleting a status does not affect the Agent table.

I'm extremely confused now. Is there something wrong with my relationship? (want to get the relationship part down before moving on to the layout woes)

Thank you for helping!

Yes I am.

...

Is that causing the problem; is yes, why and how what would you recommend I do instead?

The problem is that a company has many agents, and therefore many child records in the CompanyAgent join table. When you want to create a grandchild record in the Status table, Filemaker doesn't know which join record is to become the parent of the new status record - so it creates a new one (if it is allowed to do so - otherwise it would pick the first related join record as the parent).

You could define a new relationship between Companies and (another occurrence of) Status, matching on CompanyID only, allowing creation of new status records. Use a portal based on this relationship to create new status records directly from a layout of Companies. (You must, of course, pick the AgentID for any new record that you create.)

You could define a new relationship between Companies and (another occurrence of) Status, matching on CompanyID only, allowing creation of new status records. Use a portal based on this relationship to create new status records directly from a layout of Companies. (You must, of course, pick the AgentID for any new record that you create.)

That worked. Thank you very much!