Expand all | Collapse all

Report Filtered By User On A Different Table

  • 1.  Report Filtered By User On A Different Table

    Posted 01-02-2020 19:13
    The primary table on my app is 'Events'.  The Events table has a child table 'Positions', which joins to another parent table called 'Staff Members'.  Essentially, the Positions table is a middle table that joins Staff Members and Events in a many-to-many relationship.  Positions defines the job description, other notes, compensation rate, etc.

    On the Staff Members table, each record has a 'user' field, connecting the staff member to their user / login for the app. So I can easily create a report filtering Staff Member records by the 'logged in user'. 

    Here's where I'm stuck.  I need the Events record to be visible to the Staff Member, only if they are connected to it via a 'Position'.  I have a couple user fields in the Event record, and it's very easy to only allow those users to view Events they are associated with. However, since the User Field for the Staff Member is a couple tables away, I'm having difficulty figuring out how to allow those Staff Members, when they log into the app, to see a report on their home page that contains a table with all Events where they are associated via a Position. 

    I feel like I'm missing something easy here, and if so, please forgive the dumb question. Any help would be greatly appreciated.

    Joe Hargrave

  • 2.  RE: Report Filtered By User On A Different Table

    Posted 01-02-2020 20:09
    This can be done as ling as it is being done as a report filter and not actual Role Permissions.

    You can make a summary field on the relationship between Events and Positions to count up the # of Positions where the current user is the User on the Position record. Call this perhhaps [# of Positions for this Event for Current User].    I suggest make a formula user field on the Position table called [Current User] with a formula of

    That is just in case the summary field does not let you match on the current user, then you will be sure you have a field to match to.

    Then you can filter an Events Report on where [# of Positions for this Event for Current User] > 1.

    As an aside, if you tried to control Role Permission access to Events using this technique it will not work as you cannot control access Role Permission access to parents based on children - it's too circular for Quick Base to handle.

    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach

  • 3.  RE: Report Filtered By User On A Different Table

    Posted 01-03-2020 15:55
    You rock. Very creative solution, and it appears it did not actually require the formula user field to work properly; just fyi. Put that one on my bill!  :)

    Joe Hargrave