Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

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

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...
Relationships
IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago
Yes my "Team Members" table has the user as the key field.  It is basically an Employees table

------------------------------
Ivan Weiss
------------------------------
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
6 years 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
------------------------------
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years 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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years 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
      ------------------------------
    • JasonJohnson2's avatar
      JasonJohnson2
      Qrew Cadet
      6 years 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
      ------------------------------
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years 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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years 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
      ------------------------------
      • IvanWeiss's avatar
        IvanWeiss
        Qrew Captain
        6 years 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
        ------------------------------