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 Departm...
Many-to-Many
Relationships
reports and charts
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
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      6 years ago
      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
      ------------------------------