I have a table of Students records related a table of their Grades. There is a one Student record to many Grades records relationship (e.g. a record in the Grades table for each subject a Student studies). Each Grade has a field for "year".

I have a layout based on my Students table which displays related grades for that student in a portal. However, when I select the Grade year to be the current year in my find request (set in the portal showing the Grades), it displays all the Grades records relating to that student, including those from previous years.

Q: How can I filter my portal to display only the student's grades from the current academic year?

Thanks in advance.

Do you ALWAYS only want the current academic year? Or would you like the ability for the User to enter an alternate year so the portal displays those results instead? From your various threads, I assume the latter, since you've been trying to use a find. Here's one way to filter that pesky relationship portal:

Create a cartesian join (using the 'X' join operator) from your Constants::(any standard field) to any standard field in Students (they don't have to match or even contain data). The 'X' join is the last operator in the relationship popup. Since you only have one record in Constants, this will work. Then create a global (same data-type as your Grades::Year and your Constants::AcademicYear) field called gYear in your Students file. Set it to Auto-Enter via calculation and enter this:

If(IsEmpty(gYear) ; Constants::AcademicYear ; gYear)

As you leave the calc box, be sure to uncheck 'Do Not Replace Existing Value if any'. This will allow Users to type a different year in the global and see different year Grades, but if they remove a year (or type the current academic year), only the current year will display. One more step to finish it ... Modify your existing portal relationship by adding to the relationship (point to relationship connection and in the relationship box), your final relationship should look like:

Students::StudentID = Grades::StudentID

AND

Students::gYear = Grades::Year

Each time you change that constant single record AcademicYear, your popup will adjust accordingly the next time its contents are changed. There are many ways to approach this, but I can see from the posts that you've been struggling with this one from different directions. This method is easy to implement and also pulls in your Constants::AcademicYear as you've indicated you want. Oh. And with this method you can forget the find.

LaRetta

Many thanks for that - I'll give it a try.

J

BRILLIANT!!

I've got radio buttons which allow the user to see a pupil's list of marks from various academic years and it works beautifully! Thanks a million.

Now I just have to get it all working smoothly and looking good in IWP...!

How do I get the same effect when I'm viewing records in a layout based on my Grades table when I'm not using a portal? i.e. select a value from my gYear field and it displays only that year's Grades?

Oh, and why are my finds much slower now?

Edited October 9, 200520 yr by Guest

I have a table of Students records related a table of their Grades. There is a one Student record to many Grades records relationship (e.g. a record in the Grades table for each subject a Student studies). Each Grade has a field for "year".In order to filter my portal to let users switch between students' grades from different years, the *fantastic* LaRetta suggested I created a cartesian join (using the 'X' join operator) from my Constants:(any standard field) to any standard field in Students (they don't have to match or even contain data). Then I created a global field called gYear in the Students file, setting it to Auto-Enter via calculation:

If(IsEmpty(gYear) ; Constants::AcademicYear ; gYear)

This allows Users to select a different year in the global and see different year Grades, but if they remove a year (or type the current academic year), only the current year will display. One more step to finish it ... Finally, I set the relationship to:

Students::StudentID = Grades::StudentID

AND

Students::gYear = Grades::Year

This method dispenses with a find - many, many thanks LaRetta. However...

Q: How do I get the same effect when I'm viewing records in a layout based on my Grades table when I'm not using a portal? i.e. select a value from my gYear field and it displays only that year's Grades?

Oh, and why are my finds slower now?