not sure if portals are the way to go but...

[FM_n00b]

I am designing a database for a university that professors will use to log courses that they have taught.

I have three tables: Staff, Courses and Course Records.

I would like the professors to be able to open the course records table and select their name from a drop-down list, then be able to narrow down the list of courses by selecting a department (eg biology), a year (eg 3) and having the results displayed in such a way that he can then select the course that applies to him and continue filling out the form.

I have managed to get the drop-down list using a list of values that takes from the name column in the Staff table, but i am having trouble with the courses.

Using a portal and a relationship I now have a dropdown list that lets you select the department, and this displays all of the courses that fall under that department in the portal. this is great, except selecting the course that i would like to be added to the record simply lets me edit that record in the portal. how do i make it so that selecting a record in the portal will insert the course code associated with it in a field on my layout?

also, is it possible to have a portal use more than one relationship? I would like to be able to also just have a dropdown list of course codes that would show the single result in the portal so that the professor can view the associated information, but i can't figure out a way to do this without using two portals that show data from the same table but using different relationships

thanks a bunch!

[FM_n00b]

I have managed to figure out most of the above:

- I turned off the ability to enter the fields in browse and field mode. this made it so that selecting a record in the portal would select the entire record

- I then drew a box inside of the portal record and set it as a button that inserted the course code associated with the portal record into my desired field

However, now i just can't figure out this one thing:

I want to be able to display a list of publications a professor has written over the past TWO years.

for example: If i type 2008 into the Year field I would like to display all publications by a professor for both 2008 and 2007.

I tried to do this by creating a calc field called Previous Year that is = Year - 1

the calculation result is correct, so i created a second instance of the publications table in the graph and related it so that Report Year = Previous Year and created a portal to display..... nothing

I have one portal showing the publications from the current year and one for the previous year, but only the current one gets populated with results.

any ideas what i'm doing wrong?