Accessing a field from differnt table than datasource

[bertc]

Hi all, I am a bit of a novice at Caspio & databases in general, so I'm hoping you may have some guidance for me here.

I have a table of High Schools with data relating to the schools, such as SchoolName, Address, City, County and more
I have a table of students with their Name, Email, Gender and more.
The HighSchools Table has a unique key called "HighSchool_ID", which is in a one to many relationship with foreign key HighSchool_ID in the students table.

Now, I have a datapage where I use the students table as the datasource, and can access all the student data fine, and also stamps the HighSchool ID in the table.
Problem is I didn't realize I also need to display the HighSchool-> County field in that datapage. Data has already been entered for all schools, and some students.
So, wondering what best remedy may be:
1) Create a view joining Students & HighSchools table and changing datapage to use that as datasource?
2) Add new field in Students table that can get the HighSchool -> County info? (Kinda tried this but did something wrong so would appreciate instructions )
3) Some other method?

I hope I explained this well enough to make sense.

Thanks in advance for any help!

[SunakoChan]

Hi @bertc, As I understand that you need to get the Country associated with that record from your table students.  What I can suggest is to join the table, afterwards create a view.  For more information in joining the tables, you may check this helpful articles and video:  https://howto.caspio.com/tables-and-views/what-are-views/creating-a-view-to-join-tables/ and https://howto.caspio.com/getting-started/create-a-one-to-many-relationship/.

Regards,
Sunako 

[cheonsa]

Hi @bertc,

What I understand with your workflow is that, you would like to display the County information to you DataPage also. You can create a View that will join two tables and use this as Data Source. In that case, you will be able to pull up this field on the DataPage. 

Hope this helps.

 

Regards,

kristina

[bertc]

Thank you both. I went ahead and created the view joining schools & students tables. I had to change a few things as I was using the students tables for authentication but changed that to use the view. Caspio made it simple to update the needed datapages.