Discussions

Expand all | Collapse all

Cannot Dynamic Filter in Many to Many Relationship

  • 1.  Cannot Dynamic Filter in Many to Many Relationship

    Posted 12-31-2019 16:05
    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
    ------------------------------


  • 2.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 12-31-2019 16:28
    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
    ------------------------------



  • 3.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 12-31-2019 16:55
    But dont I need a many to many relationship here?  A procedure is going to be related to a few departments.  Each department will have a bunch of procedures.

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



  • 4.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 12-31-2019 16:45
    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
    ------------------------------



  • 5.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 12-31-2019 16:54
    The way I am carrying over the names is a summary combined text field.  Since it is one to many.  Am I not exposing the right field somewhere?

    This is a screenshot of the relationship of the join table:

    This is a screenshot of the field connections between the join table and the parent table procedures.  


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



  • 6.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-01-2020 09:49
    Ivan,

    The Department Name will be in the other relationship of the Join Table.   Procedure Title passes to the Join from the one in your screen shot.


    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 7.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-01-2020 15:24
    Happy New Year everyone.  This is the other side of the relationship screenshots.  I guess what I am not fully understanding is how I get the data from reports to procedures.  I have built the report on the procedures table.  So I am not sure how to get through the many to many relationship to the other side.

    Or did I do that wrong and should the report be built on the join table?  Maybe that is the entire issue?


    And the settings on the department name field


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



  • 8.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-01-2020 16:42
    Re:

    Or did I do that wrong and should the report be built on the join table?  Maybe that is the entire issue?

    Exactly. The join table us a child of both tables, so you lookup any fields you need from both Parents down to the join table.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-01-2020 21:27
    So that fixes the filtering issue except now I get the same procedure listed 4 times because it is associated with 4 different departments.  I want these to show as one row.



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



  • 10.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-01-2020 21:50
    Perhaps make a summary report.  That will solve the repetition.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 11.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-02-2020 07:18
    Ivan,

    What does the one row of your desired report look like? 

    From you first question, I imagine you want to Group procedures by Department. 

    What is the Anchor of the report Departments or Procedures?

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------



  • 12.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-02-2020 14:13
    Don, ideally it would look like the report I posted initially with my first request.  Looking for a single line with the Company Procedure, a listing of the departments it affects, the date it was modified last (so we can watch for old procedures) and who modifiied it.

    But I want to be able to filter it by department.  This way a Project Manager can just filter out procedures that affect them.

    I tried the summary table but that didnt work either as it doesnt let me add the other columns I need.

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



  • 13.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-02-2020 17:31
    Ivan.  Let's go back to the summary report.

    You can have up to 6 levels of group by, so that is 6 columns. If you actually need more you can make a formula to combine 2 or more columns. Isn't that enough columns??

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 14.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-02-2020 08:12
    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
    ------------------------------



  • 15.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-02-2020 14:16
    Jason, correct me if I am wrong but won't this always show the report filtered by the department the current user is in?  I dont want that as it would mean managing directors etc. that are not part of certain departments wouldnt see everything.  I only wanted the filter to be an option, not to inherently hide the data.

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



  • 16.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-02-2020 14:49
    Here is how the filtering turned out when only using the Departments Affected field and you can see some  procedures had more than one department but the departments split out singularly. Below that image is the how the field is setup. Honestly I just never expected the filter to only show the separate departments because in some fields it gave all the options I made.  This didn't require relationships to work, I tested with another table and it worked without issue.




    ------------------------------
    jason johnson
    ------------------------------



  • 17.  RE: Cannot Dynamic Filter in Many to Many Relationship

    Posted 01-08-2020 10:25
    Jason, thanks for the response!  I didnt want to ignore you here, just have not had a chance to try this out.  Once I do on my end I will post back for sure!

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