Skip to main content
Question

Cannot Dynamic Filter in Many to Many Relationship

  • December 31, 2019
  • 0 replies
  • 160 views
Forum|alt.badge.img+11
I am creating a table for all of my Company Procedures so that we can work on documenting them for new and existing employees.  As this list can get a bit long I want to be able to associate a Department with them so if I work in Accounting, for example, I can dynamic filter all of the procedures just affecting Accounting.

Here is my structure:

Departments < Department Procedure Join Table > Procedures

I created the join table because each department can have many procedures and each procedure can have many departures.  On the report I can only do the following.  I cannot seem to expose the department field as a dynamic filter for some reason.  Not sure what I did wrong here or what is wrong with my logic.



------------------------------
Ivan Weiss
------------------------------

  • JasonJohnson2
  • Registered
  • December 31, 2019
I would have made procedures a stand alone with the Departments Affected By field being a multi-Text Select but make the selections come from the Department Name field in the Departments table. Then you could make the role show only records that contain the users department in the Departments Affected By field.

------------------------------
Jason Johnson
------------------------------
MarkShnierYou
Forum|alt.badge.img+24
Your design is correct. Check if the department field on your join table is set to searchable.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Here is  what I did.
I have a User table where the user field is the key field.
There a relationship with a Departments table that is One Department to Many Users and for each user they will have selected Department that they belong to.
The next relationship is One User to Many Procedures with a lookup of the Department. One change must be made, the Related User field must be changed to a Formula - User field. The formula is as follows: User() This will make that relationship always choose the current user and lookup their department.
With a Departments Affected field being a multi-select text field where the values coming from the Department Name field in Departments and a lookup of the current user department we can build a formula-checkbox. Call the field 'Allowed to View Procedure'. The formula is as follows:If(Contains([Departments Affected],[User- Department Name])=true,true,false)

Now you can go to the role and custom filter to 'Allowed to View Procedure' being true before a user can view the procedure. 

I have a User table to do 2 special items in my pmo app and in that design this would be the easiest solution and  avoid a many to many relationship. I wanted to answer this in 2019 but needed that New Years day off. Hope this helps.

------------------------------
jason johnson
------------------------------
Terms & ConditionsAccessibility statement