I have a database that manages team rosters for a school district’s athletics program. Users are able to view students attending their schools and also teams associated with their schools. Generally, students play on their school’s team. However, this is not always the case. For example, a student enrolled at School A will play on School B’s baseball team if School A does not have a baseball team. In these situations, I’d like users at both School A and School B to see the student’s personal information. This means that permission levels for a parent table could come from either the grandparent or a sibling table (through a shared child). How can I achieve this?
Attempted Solution and Unexpected Quickbase Behavior:
My attempted solution was to create a calculated field in the child table “Students on Rosters” that would evaluate to TRUE if a user should have access to a record based on either access pathway (through the “Teams” or “Student-Athletes” table). I then passed this field up the lineage to “Student-Athletes” and then “Aspen Students”. This does allow users to view records that they have access to through either pathway. See this post and helpful response for more context (https://community.quickbase.com/quickbase/topics/filter-records-in-a-parent-table-based-on-a-user-list-field-in-a-child-record).
However, while this solution works for records, it does not work for fields associated with those records. When users view an “Aspen Student” record, they can view data on all fields. But when users view a related record on the “Student-Athletes” table, they can no longer see data for fields that are descendent from the “Aspen Student” table. The graphic below may help illustrate the situation and how I believe the permission flows are working.