Relationship Based On Specific Data

[Josh Gagnon]

Hello,

 

I am very much a newbie to FileMaker and I appreciate the help in advance and i'm sorry if this is a very confusing question (or if I am so new I don't know how easy it is).

 

I have one database "StudentData" that holds, essentially, contact information for our students (biographical information, whether they are active or alumni in the program, etc.). I need to create a new database/table "AnnualReport" that takes biographical information from "StudentData" IF they are labeled as active in a specific field in "StudentData" and also has space for new values to be entered. I'd also like information the record in the "Annual Report" to be deleted if the student is deleted from "StudentData." Is this possible?

 

"StudentData" ------------------------> "Annual Report"

Biographical Info                                 Has records w/ Bio. information from "StudentData" IF active

                                                            New record created when a new student is added in "StudentData" IF active

                                                            Can create new values in these records to run full annual report

[eos]

You don't need another table – and you (probably) shouldn't delete Students from the existing table, just set their programme status to inactive.

 

To create your report, just search for Students that are flagged as active and show them on a list/report layout that holds the desired fields.

[comment]

I need to create a new database/table "AnnualReport" that takes biographical information from "StudentData" IF they are labeled as active in a specific field in "StudentData" and also has space for new values to be entered.

 

What exactly is included in "new values"?

[Josh Gagnon]

Thanks eos - I think that would work if we didn't need to keep the annual report each year & the data changes. So if we don't create a new database, I would just be creating new fields each year and that seems to be overwhelming after a certain number of years...does that make sense?

Hi comment. For the annual report we need to track let's say course names, gpa, items like that. So I would want to be able to enter that into this new database on top of the biographical information. I know i could do this in the same database as the biographical information but we need to save the record each year so I thought a new database would be the way to do that on a yearly basis...or should I just create new values/fields for each years information?

I thought maybe I could create a relational database using a student ID that is auto generated and then have a script that grabs biographical information IF they are active?

[comment]

For the annual report we need to track let's say course names, gpa, items like that. So I would want to be able to enter that into this new database on top of the biographical information.

 

I am afraid that doesn't sound right. You should have a table for each type of object (entity) you track - for example, Students and Courses. Then you need to ask yourself what is the relationship between these entities. For example, the relationship between Students and Courses is almost always many-to-many (each course can have many students, and each students can take many courses) - so this would require an additional join table of Enrollments to resolve the relationship.

 

Under no circumstances should you be required to add new fields (or tables) periodically to your solution, only new records.