Return multiple searches in organized lists

[kalavinka]

Hello, it's been a long time since I've done anything complicated with FM and now I want to do something beyond my level. There are several possible ways to accomplish the goal (perhaps a combo of search, relational db, calculated fields) but I can't seem to even get close. That said, I have an existing database but I'm willing to start fresh and approach this in a different way to reach the goal.

I have a database of students with their various information, including their top 10 preferred advisors.

Example: student_name

advisor1_name

advisor2_name

etc

I would like to have a list of all the students interested in working with an advisor, but listed according to their preference.

Example: advisor_name

first_choice_students

second_choice_students

etc

Currently in a very simple way I can perform a search in the field advisor1_name for a given advisor's name and return all the students whose first choice it is to work with that advisor. If I add multiple requests in find mode, the resulting list of students will not be sorted with the information to know the hierarchy of the student's advisor preference. Thus, to currently achieve the goal I have to perform 10 separate searches. I would rather display all of this information on one page in an organized fashion.

An elegant solution, but beyond my scope at the moment, would be to have several databases. One containing advisor information only (we have ~400). Then, a database for each year's crop of students.

I feel like this must be a common enough problem in school administration but need help with a solution. My google results were poor. Thank you.

[comment]

I have a database of students with their various information, including their top 10 preferred advisors.

Example: student_name

advisor1_name

advisor2_name

etc

This is not a good starting point. You should be using three tables:

Students

Advisors

Requests

with the Requests table having fields for:

• StudentID

• AdvisorID

• Rank

This way you can easily show which students have requested an advisor, by placing a portal to Requests on a layout of Advisors. This portal could be sorted by Rank, or you could use another relationship - filtered to show only requests of a specific rank.

[kalavinka]

Thanks!

I wasn't sure how best to approach it and with your suggestion, I looked up tutorials and created some test files and it's working well.

I was actually trying to convince co-workers to start utilizing FM by helping to create a solution for this time consuming task of theirs and I think it has worked.