I took the big leap and invested in the Maker Plan. I am getting a little lost in the trenches right now with my data goals. I am a School Counselor working on a Graduation Tracking App for School Counselors to better communicate graduation progress and tracking with their students and parent/guardians. Can someone please please help me set up my collections and data the right way? Here is an infographic of how I want to set up who see’s what.
I am using Google Sheets for my primary collection. In summary, I need:
Myself (App admin) access to all user data
Counselors within a school (School team) access to view/edit/upload student data at their individual school.
Individual students - access to view/suggest updates to data (AKA the student Dashboard). (final data only edited by counselor (Credits earned, credits remaining etc.)
Individual parents - access to only their own students data. Read/view only/suggest updates to data (final data only edited by counselor)
I need someone to help me understand if I need all this info in one data sheet or can it be multiple and I set up relationships? What Roles, Reader View, Row Owners, Array Columns do I need to set up?
User ID: Unique identifier for each user (Counselor, Parent, or Student).
Role: Role of the user (e.g., Counselor, Student, Parent).
School ID: For all users, links them to their school.
Children ID: For Parents, lists the Student ID(s) of their child/children (in cases of multiple children, this can be a comma-separated list of Student ID values).
3. Graduation Progress
This sheet tracks the academic progress for each student.
Student ID
Semester
Credits Earned
Credits Remaining
Credits Completed
Suggested Updates
Counselor Approval
Notes
1001
Fall 2023
10
10
180
None
Approved
Doing well this semester
1002
Spring 2024
20
40
140
Needs more electives
Pending
Needs to meet with counselor
4. Roles & Permissions
This table defines the permissions for each role.
User ID
Role
View Data
Edit Data
Suggest Updates
1234
Admin
Yes
Yes
Yes
5678
Counselor
Yes
Yes
Yes
1001
Student
Yes
No
Yes
P001
Parent
Yes
No
Yes
You can do relation - lookup to retrieve this info back to the Users table, and use that to configure component visibility. It helps to have a centralized table where you can just change it here, and it changes for all relevant components. You can use boolean instead of Yes/No.
5. Updates
Tracks updates suggested by students or parents and the approval status by counselors.
@ThinhDinh -this is amazing feedback, thank you for your help and outlining the various data tables. You listed out the 5 areas (Schools, Users, Graduation Progress, Roles and Permissions, and Updates). Just wanted to get clarification that these 5 would be there own data sheets, and I can use relations to bring data together? And by doing so counselors can see sheets for the students within their school but not other schools (Based on the school ID). THANK YOU AGAIN!!!
You can use the School ID as the “Role” column in your setup, and then record the School ID in any other table, so you have a “security wall” for each school.
Alright I will give his a go. That is a good video - thank you for sharing. I happen to see that there were no tables with Row Owners assigned. Is that something I would not need assigned? Instead just User ID’s/school ID etc…?