Using a Dynamic Filter from Many to Many Relationship

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
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.
Photo of Nick

Nick

  • 150 Points 100 badge 2x thumb

Posted 2 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
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?
Photo of Nick

Nick

  • 150 Points 100 badge 2x thumb
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.
Photo of Nick

Nick

  • 150 Points 100 badge 2x thumb
Have you had the chance to review this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
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