Discussions

Expand all | Collapse all

Using a Dynamic Filter from Many to Many Relationship

  • 1.  Using a Dynamic Filter from Many to Many Relationship

    Posted 11-09-2018 17:54
    I have 2 tables, Table A and Table B connected via a third Table C to create a many to many relationship.


    In Table A, there is a text field, called Field X, that forms part of the relationships.


    I want to use Field X as a Dynamic Filter in a report over Table B.


  • 2.  RE: Using a Dynamic Filter from Many to Many Relationship

    Posted 11-09-2018 17:58
    I have a way to do this, but unless you are a real whiz with URL formuals, I would need to set it up for you.  It takes about an hour to set up.

    Are you just looking to filter on one selection or multiple selections?  Also does this need to work in a multi user concurrent environment?


  • 3.  RE: Using a Dynamic Filter from Many to Many Relationship

    Posted 11-10-2018 18:13
    Wow. I had not realised it would be that complicated.
    I was looking to filter on multiple selections. I had thought to add the field to the default dynamic filters you can use on a stanrd report.
    As to the multi user environment, we do not have many users, so the likelihood of users utilising the same report at the same time is very low.


  • 4.  RE: Using a Dynamic Filter from Many to Many Relationship

    Posted 11-13-2018 19:59
    Have you had the chance to review this?


  • 5.  RE: Using a Dynamic Filter from Many to Many Relationship

    Posted 11-13-2018 21:17
    You can make a single record in a new table.  It would have a field, perhaps a multi select field to allow you to select the filters for table A.

    You would then build a relationship to lookup those selected values down to table A by a reference field formula of 1 - because the selection record is a tavble with just 1 record, so it will be Record ID# of 1.

    Then use a formula in table A to flag record which qualify which calculates to "1".
    Then lookup that down to the middle table.

    Then summarize up to the B table of the max the join table lookup field.

    This any record flagged with a 1 in table B qualifies.

    Then to make it nice for the users, put a report link field on the selection record to show a Report Link of all table B records, but then use a report filter for the form of only qualifying records.

    So user enters their selections and then saves the selection record and instantly sees the result of qualifying table B records