Skip to main content
Question

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

  • August 14, 2018
  • 0 replies
  • 44 views

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

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.

  • Author
  • Quickbase Alumni
  • September 20, 2018
Update: I figured out a solution to this problem, to an extent. 

The idea is that users can navigate to my report in A 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 C 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.