How can I filter Contacts using a many-to-many relation?

[zoul]

I have a custom Events entity that is connected to Contacts through an intermediary table called Event Registrations: Contacts ↔︎ Event Registrations ↔︎ Events. The idea is to represent which contacts went to which events, but I can’t simply use a direct relation between Contacts and Events because each event registration has extra metadata like `status` (was invited, attended, did not attend, …).

Now I would like to filter Contacts that went to a particular event, ie. I want to select Contacts for which there is an Event Registration with given event and status. Is that possible using the standard UI? So far I have a workaround:

1. Open all Contacts
2. Add a filter for Event Registrations (is any of…)
3. Open the list of all Event Registrations and filter out relevant ones by event and status
4. Select all filtered Event Registrations for the Contact filter

This works but is kind of hacky if there is a larger number of event registrations since it’s not possible to select them all, I have to manually click the _Show more_ button.

What are my other options? I thought about creating a custom filter on Contacts (is it possible to have a filter that requires multiple config values?) or adding a custom bottom panel to Events with relevant Contacts.

Thank you!

[victor]

You may need the Report Filter functionality: https://docs.espocrm.com/user-guide/...report-filters.

To create and use it, you must have the Advanced Pack. In any case, you can test this functionality on the free 30-day Espo Cloud, or on our Demo: https://www.espocrm.com/demo/.

You may also need the Report Panels functionality: https://docs.espocrm.com/user-guide/...#report-panels.

In any case, you can find more information besides the documentation on this forum.

[zoul]

Thank you very much! Reports look very close to what we need. Or, to be more precise, it looks like a list report with runtime filters would work great. We could specify the event and attendance status through runtime filters and get a list of contacts. But I don’t see how to query the data – the report only seems to be able to filter on contact’s properties, but we need, in pseudo-technical terms, to filter on “contacts JOIN event registration”. Joint grid reports look a bit closer to that, but they don’t support runtime filters. So is it possible to do what we need using the Advanced Pack as is, or would we still need some hand customization? Thank you again!

[victor]

Each type of report has its own characteristics and only you can choose the functionality that suits you best in practice.

As for the Report Filter, it works on the basis of the List Report, in which the necessary filters are specified in advance. And you get in the form of your custom Report Filter a certain set of filters from the List Report, which is taken as the basis of the Report Filter.

Grid Report - allows you to group and filter data, it is also possible to use temporary filters (as in List Report).

Joint Grid Report - combines two Grid Reports with already filtered data.

Most likely, you will be able to implement your query using several tools from the Advanced Pack. In any case, you need to test this or that functionality personally and then check with the opinion of the community, which will tell you whether it is possible to optimize this or that tool for a specific query, whether you need to use another, or whether you need to use several tools.

[jeffreysgrossman]

zoul I was looking for this functionality before, and found this nice little extension. It at first did not work on many to many relationships. But i contacted them and they updated it. This way is not free but does save you from having to figure out how to do it yourself.

Predifined Filter Management - OSOM CRM