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

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
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
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
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.
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
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.
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb
Ok I might try something like that. Thanks for your reply.
Photo of Alex Gale

Alex Gale

  • 1,124 Points 1k badge 2x thumb
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.