Forum Discussion

BrittanyScheid's avatar
BrittanyScheid
Qrew Cadet
5 years ago

Filter Report Based on User Role

Hi there,
I'm still relatively new to Quick Base, but I was wondering if there is a way to filter a report based on the user role.  We have two different levels of staff - Reviewers and Team Leads, and I've created roles for each.  There's also a field for each in our reviews table.  Each review has both assigned to it.  However, Team Leads can also be assigned as a Reviewer, with someone else as the Team Lead.  I want to create a report that excludes anyone from the Team Lead level.  I know you can use filters such as where Reviewer <> the user listed in Team Lead, but as I mentioned above, a Team Lead could be assigned as the Reviewer with someone else as the Team Lead, so that wouldn't work.  Is there any way to filter based on their role?  Thanks!!

------------------------------
Brittany Zane
------------------------------
Formulas and functions

8 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 months ago

    The only function available to us is UserRoles( .....). That returns the Roles of the Current user.   I don't know how to get the Role for a particular user in a field by formula

    The only way would be to maintain a table of users and role names where the key field is the Userid. 

    Then make a relationship to the details table based on the user field and pull in that User's role name into the details. 

  • MalcolmMcDonald's avatar
    MalcolmMcDonald
    Qrew Cadet
    2 years ago

    Chiming into an old thread for future people searching for similar solutions. Building on Mark's posts, I have a table with a couple dozen user guides. Some user guides are for general audiences, some restricted to admins etc.

    Created a multi-text selection field where the options were <Role ID # > - <Role Name> eg 10 - Viewer. Then a checkbox formula field using this formula:

    If(Contains(ToText([Role ID]),ToText(UserRoles("ID"))),true)

    It works to set the report filters to where the checkbox formula is checked, but I prefer to set it at the table permission level with a custom rule on what users can view.

    Don't know that I would go to this kind of trouble for a big table - but for small ones, really works nicely as a quick row-level security by user role workaround.



    ------------------------------
    Malcolm McDonald
    ------------------------------
    • SherriLarge's avatar
      SherriLarge
      Qrew Member
      2 months ago

      MalcolmMcDonald  or MarkShnier__You I have a similar problem I am trying to solve. I must be missing a step above from both your instructions. I do not see a reference to the user field in either of your formulas. 

      In my app, we track bid opportunity records. The field "Owner" indicates the user who is responsible for the opportunity. The user selected in the Owner field can be one of two types of user roles: "Estimator - Project Takeoff" or "Estimator Technical Sales."

      I need to create a report that returns opportunity records entered within a certain date range, but only if the Owner's user role is "Estimator - Project Takeoff."

      How do I write my report formula so that the Owner's associated user role = Estimator - Project Takeoff, and filter my report by only those users?

      In plain English, what I am trying to tell my report is this: 

      If the user in field "Owner" has the role "Estimator - Project Takeoff" then include that person's opportunities in my report. 

      Thank you!

  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Brittany,
    Even for an experienced user, that is a very interesting question, so no apologies for being new to Quick Base. 

    There is a recent enhancement we got in the form of a new function which allows, for the first time, for a formula to "see" the User's Role.  Previously only form Rules could do that.

    The function's Help is here.  It's a bit confusing to understand the Help but the examples help.

    https://login.quickbase.com/db/6ewwzuuj?a=dr&r=gj&rl=dme


    If you use the function UserRoles("ID") and the user was in Roles # 10 and #11 (you get the number by clicking on the role name and observing the URL),  then the function would return  10 ; 11

    ie the Role numbers separated by space ; space

    You can also get the names to return with
    UserRoles("Name") which would return
    Participant ; Administrator 

    or UserRoles("") will return
    11/Participant ; 12/Administrator

    The advantage of hooking onto the number of the Role is that if you change the name of the Role it will not break your formula.

    The disadvantage of a number is that it's a bit of a pain to parse out.

    But in practical terms the roles will start to number at 10 (so at least two digits) and heck, if you create an app with more than 99 Roles, you deserve what you get.  So let's assume you have a two digit Role.

    So, with that long preamble here is your answer for say a formula checkbox field called perhaps
    [Is a Team Lead?], where the Role ID# of a Team Lead was 12.

    IF(Contains(UserRoles("ID"),"12"),true)

    Then just run your report filter where that field is unchecked.


    ​​​

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • BrittanyScheid's avatar
      BrittanyScheid
      Qrew Cadet
      5 years ago
      Thanks!  One question, is there a function/way to check the role assigned to a specific user?  Like could I run a function on the Reviewer field (which is a user field) to find out what role they are assigned to?  Thanks!

      ------------------------------
      Brittany Zane
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        Hmmmmm, no I do not think there is a way. The only way, is to have a table of users to use in relationships to lull down the user.  Then you can add any other fields to that table to be looked up down to the detail records.

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