Jump to content

many to many relationship between 2 occurrences of the same table via a join table


Recommended Posts

  • Newbies

At the beginning I had a "customer file" database made up of :
- 1 "CUSTOMER" table (= companies...)
- 1 "CONTACT" table (=people who work there).
- 1 join table " customer contacts " (allowing a relation of several to several between the 2)
- 1 CUSTOMER model with a "contacts" portal (which lists the contacts)
- 1 CONTACT model with an "employers" portal (list of clients to which the contact is linked)
so far nothing very complicated

In the real life of a VSE, 1 contact often comes down to a name + 1 mail + 1 such which "turns" sometimes into 1 professional or private customer but he can also place an order for 1 third party (an association or his wife hairdresser ...). This is usually specified at the time of invoicing.
In short, a client and a contact are very similar (they can both have a name, addresses, e-mails, telephones...) it occurred to me that a single CUSTOMER table that serves both as a contact and a client could do the job.
So I replaced the "CONTACT" table by a second occurrence of the "CUSTOMER" table which I renamed "Contact".

The problem is that the portals of the 2 models no longer work as desired!
I first thought it was a context problem, so I cut the link graph into 2 different parts, which are used by each model, following the anchor/buoy logic :

The "CUSTOMER" model is based on:
- 1 occurrence of the CLIENT table "CUSTOMER_view" (the anchor)
- 1 occurence of the joint table "contacts_of_customers"
- 1 occurrence of the CUSTOMER table "contacts".

The "CONTACT" model is based on :
- 1 occurrence of the CLIENT table "CONTACT_view" (the anchor)
- 1 occurrence of the join table "customers_of_contacts".
- 1 occurence of the CLIENT table "customers"

At this stage the portals of both models are "half working":
- the CUSTOMER model displays the first contact, but not the following ones (?!!!)
- same thing for the CONTACT model who displays the 1st customer to which she is affiliated only

I've already tried a number of things but I'm blocking because I don't understand why it doesn't work!
Please help !
Thanks to the good souls who will enlighten me :)

customers-customers.fmp12

Link to post
Share on other sites

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.