Relation inside a relation, inside a relation

Here is my idea: I would like the home page to have a list of schools, touch on a school and see a list of grade levels. Each grade level will open a list of teachers that teach that grade level. Then touch the teacher name to go to a form to be completed and submitted. Also, I would like the form to capture the school name, grade level, and teacher name when the form is submitted.
Is this possible?
google sheet link
app link:

any help would be great.

@rex_threatt I looked at your spreadsheet and in my opinion you have to many tabs. From what you describe you only need 4 sheets. Schools; Grade Levels; Teachers; FormSubmission.
Schools would relate to Grade Levels, Grade Levels to Teachers. I would create unique keys for each of the tabs.
Here is a start with the basic structure. Make a copy and play with it. The relations are done in Glide/Data but the lookups are done in the Spreadsheet. You can do the lookups in the Glide environment but I wanted to show you that way as well.

1 Like

This is great. Thank you for starting this. I have been adding to the data to show my district’s schools, teachers, and grade levels. Can you help me with making the app work the same with the new data?

I can help if you have a specific question or issue. What are you stuck on?

I am working on the teacher tab. I am trying to match the teacher to their campus and grade level taught. I am trying to use the array formula you used in columns (F, G, and H), but cannot get it to match.

Will the tab with Grade Levels work in the app like this?? with all the white spaces?

I have 8 schools with grade Kinder - 5 (elementary schools)

3 schools with 6 - 8 (middle)

1 school with 9 - 12 (high)

I would like only the elementary schools to show grade K - 5, middle school to show only grade 6-8, and high school only show (9-12)

You have to key in the proper links in the various columns that relate back to the others. Each sheet has an ID column that is unique to the entire sheet of rows. School is the root sheet. Grade Levels is the next one down. It has a column called Schools ID. That ID has to match the appropriate ID in the Schools sheet. Similarly the Teachers has a column called GradeLevels ID. That column holds the ID’s that link it back to the Grade Levels sheet.
What you should do is use the app to add the Grade Levels and the Teachers when you are at the details level in the app. Then all of those matches would have happened automatically if you saved off those various ID’s to the matching columns that hold the ID’s. It’s a bit hard to explain in text.

I see now in your sheet those original 3 columns that you want to match up. I’ll have a go of it to get the lookups to work. Stand by.
FYI I alway color any row 1 columns a blue color so I remember that they have arrayformulas in them.
Edit 1: OK I added a few columns with some formulas for you. Use those as examples of how you would do all of those lookups. As I previously mentioned you can do those lookups in Glide, and for the somewhat inexperienced Sheets user it is easier in Glide.
Edit 2: Actually it is much better to do all the relations and lookups within Glide, because arrayformulas and vlookups don’t play well together.
Edit 3: Well I changed the original demo app to use only Glide relations and lookups. I also added Form Buttons to add new Grade Levels and Teachers so the relationship carry forward. I did think of a problem however with the Teachers possibly teaching multiple Grade Level / Subjects in @rex_threatt actual case. This has to be thought through and most likely you should not use Forms for adding Grade Levels or Teachers.

been working with the app you sent me. I almost have it down. I am stuck on getting the teachers part.

it starts with schools, then grade level, then subject - - but then all the teachers for the subject show on the list. How do I get it to only show the teachers on that campus, that grade level, that subject…

some examples to help:

Western Hills - 3rd grade - math should only show Brooke Guerra and Kelsey Luna

kennedy powell - 5th grade science - should only show Samantha Patterson and Julie Rape

Once that can be worked out, the app will be ready…

Is the spreadsheet you shared in a prior post the same one you are using in the app?
If so, which sheets are you using for your pages?
Are there 4 levels then? 1-Schools; 2-Grade; 3-Subject; 4-Teacher ?
Can one teacher be a teacher in more than one Grade and/or Subject?
Will there ever be more than one teacher assigned to a single Grade/Subject? (maybe like a primary teacher and a teacher assistant)