Many to many relationships: why is this .filter not working?

Hi all,
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.
Best, Koen

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. :upside_down_face:

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 OrgContacts.Organization.

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: OrgContacts.Filter(Contact=thisRow).Organization

1 Like

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: