Discussions

Expand all | Collapse all

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

    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

    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
    Ok I might try something like that. Thanks for your reply.


  • 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.