How to implement many to many relationship?
I have a contacts table and an organizations table.
An organization can have many contacts, and a contact can work for many organizations.
I created a OrgContacts table with a column Active (Yes/No) in it.
Now I like to pull the data from the OrgContacts table into the Contacts table (see screenshot), so that I can create a form for Contacts with a subform listing all the organizations the person works or worked for in it.
It is not working. Why? How to resolve this?
Many thanks for your help.
When a filter does not work the first thing I look for is the data type of the arguments I am comparing. Here you have OrgContacts.Contact that has an icon next to it indicating it a a row reference. Thisrow.Contact however is a text value.
These are different data types and will not yield a result when compared.
Can you change your filter to remove Contact from thisrow.Contact making both sides row references and see if that works.
Thanks for responding mallika, I tried your suggestion.
It doesn’t work.
I’m really puzzled, I can’t seem to find anything in the community or in the instruction videos.
This N:M (many-to-many) relation with attribute(s) in the linking table should be a very common pattern, isn’t it?
Contacts ----< OrgContacts >---- Organizations
For filters to work you generally need
[List of things].Filter(expression comparing things that are part of the list
In your example,
OrgContacts.Contact is not a part of
Your formula looks like this
[Table].[Column 1].Filter([Table].[Column 2]...)
But it should be like this
[Table].Filter([Column 2]...).[Column 1]
Maybe this works as intended:
Thanks for your help @Dalmo_Mendonca ! Highly appreciated.
I am learning!
One more question: How do I get rid of the highlighted numbers in this screen?
Did I implement this correctly, can someone review this please?
It’s been a while so you may be ahead of this already. From your solution there I’d just add one more step to show the desired values instead of meaningless IDs:
Note the added
Text column, all blank, is a hack to prevent displaying the “highlighted” info you pointed out. But in truth you shouldn’t need it if you refer to the org names, looks alright to me: