Granting Access to a Parent Table Through Two Different Lineages

  • 1
  • 1
  • Question
  • Updated 4 weeks ago
  • In Progress

Problem:

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.






Photo of Adam Dowell

Adam Dowell

  • 140 Points 100 badge 2x thumb

Posted 1 month ago

  • 1
  • 1
Hi Adam,

Are you using lookup fields to pass the values from Aspen Students to Student Athletes?


Photo of Adam Dowell

Adam Dowell

  • 140 Points 100 badge 2x thumb
Yes. Aspen Students is the parent and Student Athletes is the child.
Excellent.  So to further clarify the issue you are contending with, lets say for example that you have a field in Aspen Students called Emergency Phone Number and you have created a lookup field in the Student Athletes table to pass the Emergency Phone Number field to child records in the Student Athlete table. Finally lets assume that you have add the Aspen Students - Emergency Phone Number field to a form or report visible to your end users.

Is the issue that your end users can see the Emergency Phone Number field when viewing the Aspen Students record, but cannot see it when viewing a related child record in the Student Athletes table?  

If the above is true, then lets determine whether as the application administrator you can see the fields in both records or if it is also "hidden" . If the administrator role can see the field, then we have confirmed that it is in fact a permissions issue for the end users rather than a database relationship or form design issue that is preventing your end users from seeing the information.




Photo of Adam Dowell

Adam Dowell

  • 140 Points 100 badge 2x thumb
To your first question, yes. The issue is that my end users can see the Emergency Phone Number field when viewing the Aspen Students record, but cannot see it when viewing a related child record in the Student-Athletes table.

However, this is only the case for some child records on the Student-Athletes table. Users can see data in the Emergency Phone Number field for student-athletes that they have access to through the Schools --> Aspen Students --> Student-Athletes pathway. But they cannot see data in those fields for student-athletes that they have access to through the Schools --> Teams --> Students on Rosters --> Student-Athletes pathway. In either case, data in the Emergency Phone Number field is consistently visible/non-visible on all forms and reports.

To clarify a little, users are associated with specific records in the Schools table through a field called Assigned User. I'm trying to trace that field through the schema to the Student-Athletes table using lookups or summary fields as appropriate so I can use it to determine permissions on the Student-Athletes table. I believe I'm making an error in how I'm doing this since one pathway works and another does not.

And yes, as an administrator I can see data for all fields and all records in both tables.

Thank you for your help!



You're welcome Adam,

I wish I could provide some additional insight to help you track down the permission conflict. 
Adam,

In my experience, you can never grant Role Permissions to view a Parent by virtue of having permissions to view the children. Ie by using a summary field and then Tying the Role Permission for the parent to that summary field. Quick Base calculates Permissions from the parents down to the children, so conceptually (based on my experience) if it can’t see a parent, then it can’t see the relationship down to the children so it can’t see the summary fields in that relationship.

The work a round might be to have to allow access to all Students, but then to use form rules to hide huge sections of the form, if the user should not be seeing that student because that student is not in the usual school. Then you need to also ensure that users cannot modify reports at all so they can make their our personal dump of all students at all schools.
Thanks for providing this insight Mark.
Photo of Adam Dowell

Adam Dowell

  • 140 Points 100 badge 2x thumb
Thank you Mark. I had a feeling that I was getting too creative and attempting something that's not possible.

I'll consider your alternative solution.