How to retreive data from other table?

Hi,

I have two tables, Class table and Students table.

Class table has 4 columns, class name, year, teacher, and row ID.
Because class name, year, and teacher alone cannot specify any row data I need row ID.
All three columns are needed to specify a specific row.

Now I have Students table.
Each class has 10 students and students table has mid term score, final term score and attendance.
I guess Students table should have Class row ID and relations column for this.

I want to put data with the following sequence.
STEP 1: Click form button and create row data for Class table.
STEP 2: Submitting in STEP 1 form leads to a screen where row data for Students table can be created.
STEP 3: In the screen, I need to retrieve the latest row ID created in Class table and put that into Class row ID column in Students table.
STEP 4: Create 10 row data in the screen for Students table repetitively with each of student’s mid score, final score and attendance, and the relations are matched automatically as I retrieve the latest row ID for Class table in STEP 3.

I want to create one Class row data and 10 Students row data one at a time.
I am struggling with STEP 3 and would like to request help here.
Would appreciate any advice from you guys!

Hi

I’m not sure you need to use ROW ID. Can you clarify how the end solution should work From what I understand, the following may work for you.

  1. create a “student” sheet and an “options” sheet.
  2. in the options sheet have 3 columns class name, year and teacher. Under each column add in the respective list of class names, year and teachers.
  3. Create the form for students and use 3 x CHOICE component. The choice component will let you select any sheet, so select your source as the options sheet and the data column as class name, year and teach for each of them. Add in the components for the other data needed (scores and attendance)

Then you can add in as many students as you want. Will that work ?

Is there a specific need to restrict to 10 students for the combination of class/year/teacher ?

You shouldn’t approach this with the “latest rowID”. You can just do a form inside each class’ details view and add the students there. You can retrieve the rowID of said class with a “screen column value”.

2 Likes

Thanks, ThinhDinh!
The issue with the approach you suggested is that I cannot use the form repetitively.
After submit, I want to remain in the form. Is submit supposed to exit the form always?
I tried to attach a button to the form and add row with the data in the form. But, strangely, the button in the form only allows to use data in the screen not in the form.
How can I add row repetitively in the form without closing it? Is it not how form is supposed to do so?

Why do you want to remain in the form after the submission? I have always had the impression that you should finish a form submission cycle by going back to the previous screen (where you can see your submissions) before starting a new cycle.

The data you input in the form is not supposed to be accessible. It is only handled internally in Glide.

Try this instead.

The reason why I want to remain in form is I want to add 10 rows for Student table for each row in Class table. If I have to go back to the screen for Class table I have to add another row for Class. The problem with the custom form is that I cannot retrieve the Row ID for Class table. Can’t understand why screen cannot work just like form and form working similar to screen.

Well again, I have to state this.

If you’re doing it within the class’ details view then you should be able to get that class’ rowID, whether you’re using a form or a custom form. With a custom form, you add it through an add row action.