Jump to content

Creation of new and redundant record


geleng

This topic is 4825 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

asd.png

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

rs%201.png

rs2.png

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!

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 4825 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.