# Discussions

View Only

## Filter report by whether records are linked over a many-many relationship

• #### 1.  Filter report by whether records are linked over a many-many relationship

Bronze
Contributor
Posted 08-13-2018 21:27
I have two tables, _A_ and _C_, which are linked in a many-many relationship by a join table, ___B._

I also have a report in _A_ that currently just lists all records.

What I want to do is filter this report to include/exclude records in _A_ which are linked to a specific record in _C_. How could I do this?

Thanks

• #### 2.  RE: Filter report by whether records are linked over a many-many relationship

Top
Contributor
Posted 08-13-2018 21:30
Why not simply develop the report in the B table.

It has the ability to have any field which exists in A or C as it is a child of both of them.

• #### 3.  RE: Filter report by whether records are linked over a many-many relationship

Bronze
Contributor
Posted 08-13-2018 21:32
Due to how the report is used and my app is structured it unfortunately has to live in the A table. I agree it would be much easier to have it be in the B table.

• #### 4.  RE: Filter report by whether records are linked over a many-many relationship

Top
Contributor
Posted 08-13-2018 21:40
Well maybe you need a less generiac solution.

you said you need ot filer by

"which are linked to a specific record in C"

So just go to the relationship between A and B and make a summary count of the # of B's which are children of that particular C.

Then just to make it easier for reporting in A, make a new formula checkbox field in A called [Linked to Particular C?] with a formula of

[# of Bs which are children of a particular C] >0

Then filter your A report where

[Linked to Particular C?] is checked.

• #### 5.  RE: Filter report by whether records are linked over a many-many relationship

Bronze
Contributor
Posted 08-14-2018 14:08

• #### 6.  RE: Filter report by whether records are linked over a many-many relationship

Bronze
Contributor
Posted 09-19-2018 21:05
Update: I figured out a solution to this problem, to an extent.

The idea is that users can navigate to my report in from any one record in C. I want to exclude from that report all records in A which are already joined with the record from C

The key to the solution is in the fact that when navigating from table to A, my users always use a formula URL I have set up that navigates them to the report. I configured that formula so that in addition to directing the users to the report, it also stores the Record ID# of the record in C to a secondary table I set up for storing data.

On table A I then set up two fields:
1. A lookup field to grab the record ID# from the storage table. Let's call it CID#
2. A summary field, which displays the number of related records in the join table B where Related C is equal to CID#. This is going to be equal to 1 or 0. I named the summary field Track Existing Joins
(Note that Related C is a reference field on the join table B)

Finally, I filtered the report in A to only show records where Track Existing Joins = 0

I hope this is helpful. I recognize a lot of it is pretty specific to how I have my app setup, but I have been stuck on this problem for a month now and I wanted to help out anyone else in a similar position.