Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Cannot Dynamic Filter in Many to Many Relationship

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
------------------------------
Many-to-Many
Relationships
reports and charts
  • JasonJohnson2's avatar
    JasonJohnson2
    Qrew Cadet
    6 years ago
    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
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      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
      ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    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
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      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
      ------------------------------
      • DonLarson's avatar
        DonLarson
        Qrew Elite
        6 years ago
        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
        ------------------------------
  • jasonjohnson1's avatar
    jasonjohnson1
    Qrew Cadet
    6 years ago
    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
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      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
      ------------------------------
      • jasonjohnson1's avatar
        jasonjohnson1
        Qrew Cadet
        6 years ago
        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
        ------------------------------