Value List *Plus*

[Shannon C.]

Thank you to everyone who keeps these forums so lively, interesting, and informative.

I'm working on a system which feels much more like a "problem" than a "solution" right now; I suspect that there's a relatively simple way to achieve the results I want, but I also suspect I'm using the most complicated paths possible.

I have tables for the following:

• Programs
• Program Application Components
• Students
• Student Applications

Each of 100+ programs has a slightly different list of required application components (Application Form, Letter of Recommendation, Deposit; or, Application Form, Statement of Interest, Deposit; etc.).

It's been simple enough to create a portal in a layout based on "Programs" to show the required application components for each program.

It's been simple enough to connect each "Student" record to a particular "Program."

However, I'd now like to create a portal (or something with a similar feel) to automatically list the application components associated with a student's given program -- but I also want to be able to assign other fields to that record. . . i.e., the date an application form was received, the staff member who logged it in, etc.

I know how to create a conditional value list that would allow me to create a checkbox set and thus tell when a student's application was complete (when all components had been received and checked off). However, I'm having trouble creating a set of "Student Application" records which will include fields mirroring the application components specific to the particular chosen program and also allow the completion of additional fields.

I'm hoping for a layout something along the lines of:

----------------------------------------------

Student: Jane Smith

Program: Summer Algebra

Application:

[color:blue]Component_______________Date Received

Application Form__________10/31/2006

Deposit__________________11/15/2006

----------------------------------------------------

Keeping in mind that the component entries are being pulled from the "Program Application Components" but the "Date Received," etc., fields are being completed on this layout.

Does this make any sense at all? Any feedback about setting up the relationships, or any other ideas, will be most appreciated.

Edited December 7, 2006 by Guest

[Fenton]

It sounds to me as if you're missing one table. If you want to put "student" information, such as a date into a Program Application Component record, well, you can't; you need a join table, Student_Components. It would be populated with all the components of each program the student was taking, multiple records, with a Program Component ID and a Student ID; probably an Import at the time of program registration, if they are to have all components; then fill in dates afterwards; or one at a time, if they just get some.

I'm rambling, because though I profess to understand FileMaker relational design, I don't really know what a "component" is. Basically once you know what your entities (things, objects, events, whatever it is you're tracking) are, and you give them a table, and tie them together correctly, then everything falls into place.

[Shannon C.]

I think you're onto something with the missing table.... I'm hoping that the morning will bring some clarity on this one.

One thing that complicated my above post is that the industry vocabulary related to the system I'm creating doesn't mesh nicely with database language -- in my example, "application" refers to the dossier students assemble when hoping to participate in a program; "program" refers to an educational experience rather than, well, a program as we know it; "component" is the term I was using to describe the pieces of an application dossier. . . . After a while it all started to feel like an extended (and rather bad) pun of some sort. . . .

Thanks for your input!