viewing other info from the same row in a db join....

[Pena]

it sounds confusing, and maybe i didnt explain it quite clearly enough in the title of this thread but here we go.

using:
Fabrik 1.x
joomla 1.0x

i have 2 tables, one with student details, and one with results.

i have joined the "student_number" to the results table, but i would like to automatically display (in the results table) the appropriate "student_name" that resides in the same row as the "student_number" on the Student details table.

can i do this with a hidden display text that holds some php code and then display it in table view?

i would also like to know how to use other form data as variables.... its all getting a little confusing and overwhelming!

 

[cheesegrits]

When you say you have joined, do you mean you've created a table join from Students to Results, or a database join element from Results to Students?

-- hugh

 

[Pena]

oh, i have only created a join element.

i think i hear what your saying..... i need to create a table join instead, but i would like to be able to use an element to select the student number in the form.....

i dont really understand how the database join works, i tried it and it created a whole new set of elements that scared and confused me further....

is there any chance you could write an idiots guide to the table join feature?

 

[cheesegrits]

OK, take a step back. Make sure I understand what you want to do.

So you have two tables, Students and Results. You want to be able to create multiple Results records for each Student.

When displaying a Results form / table, you want the Student name to show. I also assume you want to be able to view (in table form) each Results record associated with a Student. And an easy way to create a new Record, automatically linked to a Student.

If so, then there's two ways to go about it, which have pros and cons. The easiest is:

Get rid of any table joins.

Just leave the database join element you already have in your Results table. Make sure it's a "simple join", and select the Student ID (whatever is used as the Primary Key on the Students table) as the key and the Name as the value for the dropdown.

Then in your Table, at the bottom of the Data tab, is a section about "Forms and tables linking to this table". Set both Table and Form links to Yes, and give them names.

You'll then see two new columns automatically added your Students table (just display columns, not elements or db table columns), with headings set to whatever Names you gave above. Clicking on "Add" in the Form column will take you to a new Results form, with the join back to the Students table already filled in (with the appropriate ID of the Student row you clicked on).

Clicking on View in the Table column in the Students table will take you to a Results table page, with the table filtered to only the Results for that Student.

There is also a way to have each Student row (using Detailed View) to link to a page which shows that Student's form (in either read only or edit mode), and a table of their related results. But lets get the above working first.

-- hugh

 

[Pena]

ok thanks for your help, i will give it a crack later on today and let you know how i go.

 

[Pena]

actually, I don't need to worry about that last question right now.

what i am now looking for is a way to filter tables when they are inserted into content.... without seperate table filters.

here is the deal:

We want the students to be able to see their results (once published). so the plan is to make the student username act as a filter, so that they only see their results.

but the results/report page will contain 2 tables. 1 with their student info (a single row). and one underneath that, for results (multiple rows).

the filter needs to be a "get username" type of element, but it needs to filter the student ID field on BOTH tables at the same time......

i thought that including the tables as modules in a dynamic content item would. I made a filter form (like the tutorial) for testing the filter, but it doesnt seem to filter anything....

this would be perfect, as we could print results using this method..... grrrr (frustrated)

 

[cheesegrits]

If you want to PM me a superadmin backend login, I'll take a look at what you've got. Please include a link back to this thread in your PM.

Short answer is Yes, what you want is possible. But the trick will be to index by the userid of the Student, rather than some arbitrary "student number". You can still store the student number in the Students table, but we'll need to link the Records rows to the Student row by userid.

I'm not sure how you are entering the Student data? Is it by hand, or by CSV import?

-- hugh

 

[Pena]

at the moment we have staff entering it by hand as we are compiling information as it comes.

the userid will be the same as the "student number",

sending a pm now

 

[cheesegrits]

Ah, cool, that makes things a lot easier.

-- hugh