Help with relations and data structure - Glide Pages

[Wes_Nderi]

Hey everyone,

I’m putting together a data platform on Glide Pages that a school can use to manage information about students, parents, emergency contacts etc and seem to have hit a snag when it comes to adding information using a form.

I input the existing mock data in the Google Sheet(but I have also tested using only data that is input by the form and run into the same issue)

Data structure and relationships

Students are at the centre of the app.

Parents → Each student can have one or more parents. Similarly, a parent can have one or more students.

Emergency contact → Each student has one or more emergency contact. Similarly, a student can have one or more students.

For existing data

In the Student table, I am using arrays for Parent names(Parent 1 and Parent 2) and similarly, Emergency Contact Information(Emergency Contact 1 and Emergency Contact 2)

These arrays are then used for a relation to the Parent and Emergency tables respectively.

For data added through the form e.g Adding a Parent or Emergency Contact on the Student Details screen

The Student Row ID is being passed as a column value and later used for a relation in the Students table. This relation is then used as a source of data for a Collection.

Issue: Adding an additional student to a parent or emergency contact through their respective details screen.

Is the only way to set up another collection within the student details screen e.g by setting up a relation using one of the column values?

I want to be able to from the Parents Details or Emergency Contacts screen add another Student. (Because a parent can have multiple students at a school and I want to limit the chances of having duplicate records)

Additionally, for any students who have more than one parent, I then want to be able to see any students under Parent 1 show up as students under Parent 2.

I think I may have set up the relationships in my data incorrectly but would really appreciate any ideas.

Link to the app: https://ariel-academy-ua1h.glide.page

[Jeff_Hager]

Can you share some screenshots showing what your data looks like and how your relations are set up? Just trying to get a better visual of the problem you are running into.

[Wes_Nderi]

Yeah sure @Jeff_Hager !

Let me know if this helps. There are two main relations, for existing data on the Google sheet which uses an array on the name field and for data input via the form which uses a Row ID.

[Wes_Nderi]

On the Details screen, I then use each of the relations as the source of data for the collection.

This means there are two collections(if there is no data, nothing shows) on each Details screen.

[Wes_Nderi]

@Jeff_Hager or @Robert_Petitto Any ideas?

[Jeff_Hager]

Sorry, still trying to fully comprehend your setup and the problem. I’m getting there…slowly…

First of all, how are you creating your arrays? Are you creating them using the sequentially numbered column method in a google sheet, or are you using a Make Array column?

I think what I’m struggling with, is why you have two parent relations, two emergency contact relations etc. I think I may be understanding it, but I’m not sure yet.

Here’s my idea though:

• The parent and emergency contact tables won’t need any sort of array to do this.
• Since students are the core of the database, I think the ultimate goal would be to have a comma delimited list of parents and emergency contacts in the student table. Then use a split text column to turn that comma delimited list into an array. Using those arrays, create a relation that links the student row to the related parent/contact tables.
• In the parent table, create a relation that links the parent to the parent array in the student table.
• Likewise, create a similar relation in the emergency contact table.
• Now if you are viewing the parent or contact details, and you want to add another student, what I would do is create a user specific column to hold a student ID. You can fill this student ID with a text entry or a choice component.
  • Then create a single relation linking that user specific ID to the student table.
  • Then create a Lookup column that returns the comma delimited list of parents.
  • Then create a template column that joins the lookup value and the parent name/ID that you are currently viewing. (including the comma)
  • Finally create a button with a Set Column action that will overwrite the comma delimited parent list in the student table through the single relation.
• Use the same steps above in the emergency contacts table.

That should allow you to keep all of the connection data purely in the student table, while allowing you to still add students through the parent or contact screen.

Removing a parent or contact from a student is a little bit of a different matter, but let’s get through the steps of adding a connection, and then we can look at the steps to remove a connection.

If I’m way off on my assumptions, let me know. I’m sure we can get it worked out. It just takes me a bit to fully wrap my head around it sometimes.

[Wes_Nderi]

Hey @Jeff_Hager,

Thanks for getting back to me. Completely understand!

@Jeff_Hager

First of all, how are you creating your arrays? Are you creating them using the sequentially numbered column method in a google sheet, or are you using a Make Array column?

Yes, I am creating an array by sequentially numbering columns in the Google Sheet

The reason why I did this was because there was already data existing in this format in the google sheet. For existing student data, I created a relation(Parent 1 relation) based on the Parent Array to the Parent Sheet where they match by name.
However, when new data is added through a form e.g. to add a Parent from the Student Details page, as the destination is the Parent Sheet, the only option is to pass the Student’s Row ID and use that as the basis of a new relation. Hence, a new relation(Parent 2 relation).
Parent 1 Relation based on Name in array.
Parent 2 Relation based on Student Row ID.

Initially, when I had the destination as the Student Sheet when adding a Parent, it was not possible to add to the Parent Array. The only options are to add to the column Parent 1 or Parent 2, but at this point, there is no way to check if either is empty. (Hmm…just thought of creating a custom action and use a condition and then set Column values - Would that work? Check which column is empty and update it?).
But the problem with this approach, would it not be impossible to have the Parent Sheet kept updated and as a result, it would not be possible to have a Parent screen.

I’ll definitely try your approach but similarly, how would you keep the Parent Table updated if during the addition, the Parent details are stored in the Student sheet?

[Robert_Petitto]

In the school app I built for my teachers, parent emails are indeed their own columns in the student sheet. However, I have limited functionality surrounding parent contact/information. It’s purely for display: parent name, title, email, phone on the student record…there’s no separate parent screen.

[Wes_Nderi]

Gotcha @Robert_Petitto !

I see. And so that way, you only had the Student sheet as the source and destination of your data?

In my case, it would be really valuable for the user to be able to have a Parent Screen but willing to consider this a last resort.

I’m curious though, from your app, how would you handle a situation where one parent has multiple children at the school? Just enter the Parent’s details twice?

[Robert_Petitto]

I see what you mean…yes, in my case, I’d have to enter the parent info multiple times…but to be honest, my app is fed from a completely different CRM/SIS.

If I were building my own independent CRM, yes, I’d want a separate parent table that has parent/guardian ids. In the student table, create multiple columns for the 2-4 parent ids. Relate those to the parents table and then bring in name, email, etc using lookups.