Discussions

Expand all | Collapse all

Help on a report filtering data based on a "Team"

  • 1.  Help on a report filtering data based on a "Team"

    Posted 21 days ago
    I have an app with a structure where I have Tasks related to Team Members (A Team member can have many tasks).
    In this same App Team Members is related to Departments (A Department can have many team members).

    In a report for tasks I need to generate a task listing for all tasks associated with one team.  I can do this manually for each team, but I was hoping to do it dynamically based on who is logged on.

    So for example, lets say I am part of the Strategic Accounts Team.

    I want my report to filter all of the tasks to show all open tasks assigned to anyone on the Strategic Accounts Team.

    I know how to dynamically filter by user.  But how do I do it by "Team Name" which is a field in the departments table?

    Thanks!

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


  • 2.  RE: Help on a report filtering data based on a "Team"

    Posted 21 days ago
    You need to add that field as a lookup in your relationship. Then you can use it in the dynamic filters. Here is an example where[ID - Workstreams] is a lookup field that is used as a dynamic filter:





    ------------------------------
    Adam Keever
    ------------------------------



  • 3.  RE: Help on a report filtering data based on a "Team"

    Posted 18 days ago
    Adam,

    I can see the Team name of the Assigned To user via lookups.  How do I gain access to the Team name of the current logged in user dynamically?  That is the part I cannot sort out

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



  • 4.  RE: Help on a report filtering data based on a "Team"

    Posted 18 days ago
    Ivan, do you have a table of users where the Key field is User?  If you have that then there is a way to do what you want.

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



  • 5.  RE: Help on a report filtering data based on a "Team"

    Posted 18 days ago
    Yes my "Team Members" table has the user as the key field.  It is basically an Employees table

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



  • 6.  RE: Help on a report filtering data based on a "Team"

    Posted 17 days ago
    I will assume that you have a table of Teams and that One Team has many Team Members.  (i was confused as to your reference to Departments as that seemed to be not relevant to anything.) 

    Make a checkbox formula field called [Current user is Team Member (=1)]
    The formula will be if(User() = [userid],1)

    Summarize the maximum of this field up to the Teams table.  Call it [Current user is Team Member (=1)].   It will now equal 1 if the current user is on that Team.

    Now lookup [Current user is Team Member (=1)] down from Teams down to Tasks.  Now all the tasks are identified as to if they belong to the Team that the Team member is on.

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



  • 7.  RE: Help on a report filtering data based on a "Team"

    Posted 16 days ago
    Edited by Ivan Weiss 16 days ago
    Slightly different structure Mark.

    I have a Departments Table.  It lists both the Team names and our internal departments.  I do not explicitly have the Teams in a separate table.  There is a column in this table that identifies each record as either a department or a team, but they are housed in one place.

    But I think I follow the logic and can apply it to be the same method.  Only question is if multiple people are logged into the system at one time wont there be multiple #1's in the table?  Or is it pulling somehow locally on each users machine?​

    And also my tasks are not connected to my Departments table.  The Tasks are related to the team members table.

    So the structure goes:
    • A department has many team members (This relationship is called Related Department)
    • A department has many team members (This relationship is called Related Teams)
    • A team member has many tasks.

    The department table has both internal departments as well as Teams so it has two relationships.

    Does that make sense?  perhaps that was built wrong and I need to split off departments and teams, but it still doesnt flow down to tasks.  Unless I make another relationship that is automatically set based on who is assigned to the task.

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



  • 8.  RE: Help on a report filtering data based on a "Team"

    Posted 16 days ago
    Ok, I think I see what you mean that your department table is a mixture of real Departments and Teams.  Strange, and yes should have been a separate table but not a problem big enough to rework it.  We will just work with Relationship where One "Department" (ie Team) has many Team members based on Related Team.

    As for two people logged in and conflicting, no not a problem - that is the proprietary magic of Quick Base.  Each of the two users actually has different data at the same time.  I doubt that any other normal database can do that.  If you have 100 simultaneous Users, then they sort of need 100 copies of the app in memory, but that is thankfully "their" problem, not yours.  They must buy memory on chips by the truckload.

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



  • 9.  RE: Help on a report filtering data based on a "Team"

    Posted 16 days ago
    Create a User Access table then have a user field.
    Setup a relationship between User Access and Teams 
    One Team can have many Users
    Create a summary field (UserAccessCount) in the relationship for Teams that counts the users only if the user is the current user for that team. 
    Create a field in Teams that is a formula checkbox called 'User Allowed Access' and make the formula
    If([UserAccessCount]=1,true,false)

    If you have a Teams - Tasks relationship setup where a team can be selected for each task then you can create a lookup field of the checkbox from the Related Team. you can filter reports using the field or even set permissions on the task table for the roles that need this filter.  As for departments you already have related teams so you can create the lookup. 

    Think of the number 1 as binary for yes that user can have access if they are the currents user. It will always be null or 1 no more no less.

    ------------------------------
    Jason Johnson
    ------------------------------



  • 10.  RE: Help on a report filtering data based on a "Team"

    Posted 16 days ago
    So I tried this and something didnt work right.  Here is what I did.
    1. I created a separate Teams Table and populated it with the Team names (5) records in total.  I associated the team members with the teams appropriately.
    2. I created a formula-numeric field in the Team members table and put in the formula If(User() = [User],1)
      1. I could not use checkbox because it wanted a bool, so I could not use 1.  I tried it with true but I couldnt do the max summary field with a bool in there
    3. Teams is related to team members
    4. On the teams table I did a summary field of the above formula numeric with a max
    5. I related the teams table to the tasks
    6. I lookedup this "maximum" summary field
    7. I filtered my report to only show entries that include a "1" in that field.


    Report is blank :(

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



  • 11.  RE: Help on a report filtering data based on a "Team"

    Posted 16 days ago
    It should work. Can you make this change here to have the else condition be a zero

    If(User() = [User],1, 0)

    And then look at that summary field as it flows up to Tm's and then down to projects and let me know if it is showing as a zero or is it showing as blank. If it is showing as blank then maybe there's something wrong with the relationships.

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



  • 12.  RE: Help on a report filtering data based on a "Team"

    Posted 15 days ago
    Okay so with that fix in the Teams table I now have a 1 or 0 appropriately on the team that is logged on.

    But the report still does not work.  So to your point something must be wrong with how that data filters to the task.  Currently I have tasks related to both the team members table and the teams table.  But I never assign a task to a team.  So I am wondering if that is the break?  I wouldnt want to manually do that, it should be intelligently done based on the "Assigned To" field which is the relationship to team members.  Could that be the issue?

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



  • 13.  RE: Help on a report filtering data based on a "Team"

    Posted 15 days ago
    Edited by Mark Shnier (YQC) 13 days ago
    I don't have a verbal picture of all your relationships, but I think we are at the point where the Teams are identified that 

    [User Allowed Access] is 1 if the current user is in that Team.

    You can look that up down to Team Member assignments to have a field called

    Team Member Assignment is on a Team with the Current User.

    Then flow that up as a summary field up to the Team Members to have a field called.

    Team Member  is on a Team with the Current User.

    Then look that up with the same Name from Team Members down to Tasks

    then filter a task report where

    Team Member  is on a Team with the Current User =1

    What I suggest that you do is to look at your relationship diagram (you know that is a crazy useful built in tool, right) and tidy it up, if you have not done that already so that you can visualize your relationships.  Then using this technique you can snake you way up and down through relations, with summary Max in those "1" fields going up and simple lookups on the lookups doing down.

    by the way a compact way to explain relationships is like. Ie One Team has Many TM Assignments 

    Team < TM Assignments > Team Members < Tasks

    Project < Tasks > Team Members   (Assuming a task is just assigned to a single Team Member )

    It saves  a whole lot of typing if you can explain your relationships that way.








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



  • 14.  RE: Help on a report filtering data based on a "Team"

    Posted 14 days ago
    Edited by Ivan Weiss 14 days ago
    I think I see what you are doing but I am struggling with the logic here.

    So basically the flow of tables is Teams < Team Members < Tasks

    I do not have a Team Member Assignment Table

    So basically I understand that I need a value to be 1 on the Team Members to represent the user currently logged in.  I have that.  I max summarize that up to Team Tables to get the team that is on the project.  But are you saying I use lookup fields to bring it all the way back down to tasks?  Or do I need a direct relationship from Teams to Tasks?

    I guess what I am missing here on the logic is the Tasks table has no connection to Teams as a task is never assigned to a team.  So I am missing how that data grabs the right tasks.  What on my Task Table tells me what team it is associated with since all I have is the "assigned to" field which is a Team Members relationship

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



  • 15.  RE: Help on a report filtering data based on a "Team"

    Posted 13 days ago
    re: But are you saying I use lookup fields to bring it all the way back down to tasks?
    Yes, just walk that field down to down to tasks.

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



  • 16.  RE: Help on a report filtering data based on a "Team"

    Posted 13 days ago
    That did it, guess I had to sort out in my head what that was doing.

    But I loved that tip on the how to describe the relationships.  It helped in my head process what you were saying.  Thanks Mark!  Have a good weekend.

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