I used to know how to do this, but. . .

[charlesoutcalt]

I find that FM is like a foreign language: If I don't use it every day, it fades away.

I have what I believe is a simple project, and maybe one I could have done back in the day when I was more fluent in FileMaker.

I have four kinds of data:

1. Information on students (names, grades, etc.)

2. Information on schools (addresses, admissions requirements, etc.)

3. Information on associations of schools (northeast, south, etc.; all schools belong to at least one association, some belong to several)

4. Information on scholarships. Some are available for certain types of students (for example, athletes), others are available at certain schools, or in certain associations.

So far so good. I can build those tables.

Here's what I can't do:

I would like to build master tables that bring all of these fields together.

If each school has a different type of application, requesting different parts of information about the student, I would like to be able to popular applications with data from students. The complication here is that each school has a different format. Would this be a new table for each application, or a new layout within the schools table, or something else entirely?

There are some other ways I would like to bring the tables together, too. For example, I would like to be able to track the school(s) to which a student has applied and the deadlines for those applications.

Alternatively, I would like to be able to look at a school to see all the students who have applied there, or to look at all the scholarships to see which students might be eligible (depending on their qualifications, state, etc.), and on the school to which they have applied.

I don't think this is hard for advanced users, but I just can't get my arms (or mind) around it. Any examples would be appreciated. Once I see the general concept of being able to bring fields together in this complicated way, I think I can take the general idea and run with it.

[bcooney]

I have what I believe is a simple project,

Perhaps, but not for a beginner!

and maybe one I could have done back in the day when I was more fluent in FileMaker.

Actually, with FM9 it'll be easier. You seem to have FM8, any chance you can upgrade to FM9 Advanced. A project of this size will benefit greatly from the new script manager, and debugger.

I have four kinds of data:

1. Information on students (names, grades, etc.)

Well, Students will be one table, but grades? Sounds like an opening to other entities or is it just a GPA?

2. Information on schools (addresses, admissions requirements, etc.)

Hmm, Schools is another entity, yes. But Admission requirements might be its own table, a library of admission requirements maybe that many schools share? If so, you'd need a join table btw Schools and Admission requirements. This will allow you to look from an admission requirement screen and see which Schools have that requirement.

3. Information on associations of schools (northeast, south, etc.; all schools belong to at least one association, some belong to several)

Associations is another table, yes. You would need a join table that captures SchoolsAssoc combinations.

4. Information on scholarships. Some are available for certain types of students (for example, athletes), others are available at certain schools, or in certain associations.

I do see scholarships as its own table. Some of these data choices may become their own value list tables, ie StudentType. It looks as if you will assign some schools to scholarships, so you'll need a join table there.

So far so good. I can build those tables.

Here's what I can't do:

I would like to build master tables that bring all of these fields together.

Relationships will bring the table data together, but not into "Master Tables". I think you're subconscious is suggesting join tables!

If each school has a different type of application, requesting different parts of information about the student, I would like to be able to popular applications with data from students. The complication here is that each school has a different format. Would this be a new table for each application, or a new layout within the schools table, or something else entirely?

This is a bit tricky. Are you really looking to generate completed applications from FM? Will they be acceptable to the schools?

There are some other ways I would like to bring the tables together, too. For example, I would like to be able to track the school(s) to which a student has applied and the deadlines for those applications.

That's easy enough. Each student record would have a portal to the schools that they have applied, which is a join table btw student and schools. This join table would have the application deadline.

Alternatively, I would like to be able to look at a school to see all the students who have applied there,

Use the same join table btw Student and Schools, just looking at it from the other direction. A portal on schools, listing the students that have applied.

or to look at all the scholarships to see which students might be eligible (depending on their qualifications, state, etc.), and on the school to which they have applied.

Have a relationship between scholarships and students that includes the key fields that narrow down the student list. So, the relationship for example would match on student type (from scholarship table to student). Each table has a student type field that uses the student type value list (another table for student types would be best).

Edited April 27, 2008 by Guest
Oh well, don't know why quotes didn't work...

[charlesoutcalt]

Hey! Thank you SO MUCH for your quick and helpful reply. I will spend some time reading and reflecting on what you said asap. I want to take it all in before I come back with more questions.

Thanks again!

[charlesoutcalt]

Barbara, thanks again. I have spent some time working on this (aka beating my head against it!), taking your good advice into account.

I have created a very simple version of the file, as far as I can figure it out. I'm not trying to anything fancy here in terms of formatting, valid records, etc. Instead, I'm just trying for a template with valid architecture that I can tweak.

As you will see, I have created the Student/Family, School, Scholarship, Association and Application tables, and given each of them a dummy record. I have established relationships between these tables, too, but nothing is working yet.

I am OK to the point of creating entries for each data type: students, schools, associations, scholarships. . .

What I'm still not doing right is establishing relationships between those entries. The 'Applications' table should be populating itself with the student info (GPA, SAT scores), as required by each school (some of which require HS GPA, others SAT, others essay), etc.

In addition, I would like to have a list--you called it a portal--in each student record to show the schools to which that student has applied, and the mirror image of that list within each school to show the students that have applied to it. I tried creating this portal within the Student/Family table, but it's not working yet.

Oh, by the way, I am running FM 9 Advanced. I just forgot to change that in my profile.

Thanks again for your help. I find these forums infinitely more useful than all my FM books.

students_and_schools.fp7.zip

[bcooney]

Glad to have started you off down a good path. I will take a look when I can, but anyone else is certainly free to jump in or "comment"!

[charlesoutcalt]

Thanks, Barbara.

I am certainly open to any and all suggestions!

I have the tables and fields more or less as I want them, but it's the relationships that are stumping me :

[bcooney]

Your file didn't have any key fields that I saw, so the basic info you're missing is how to relate tables.

I've created a starter for you to take apart.

Topic_195014.zip

[charlesoutcalt]

Barbara, this is tremendous. I am going to spend the evening studying this.

I really owe you. Do you have a favorite charity? If so, I'd be glad to make a contribution in your honor. Just let me know :

[charlesoutcalt]

You are going to think I'm such a dunce. Maybe I am.

I spent a few hours with this last night and today. I think I understand what you did well enough to make it work, but I can't quite add new features.

For example, I would like to add a portal on the student page showing the scholarships for which a student is qualified. I did manage to create a scholarship requirements table, and reflect those requirements in the scholarships layout. However, I could not get the qualified scholarships to appear on the student's layout--and then the relationship between scholarship requirements and scholarships broke down completely.

I am thinking now that I need to keep this as simple as possible, and let one table contain both application and scholarship requirements. Having two tables for essentially the same info seems redundant.

If I do make the application requirements table do double duty, how do I set up those relationships?

I _know_ this is possible, as it's more or less an extension of the qualified schools portal that's on the student layout now.

I just don't get it. . .

THANKS!

scholarshipsfp7.zip

Edited April 30, 2008 by Guest

[bcooney]

What I sent you certainly was a proof of concept, and will give you how to do some of the mechanics, but I still have many reservations that any system that you create will ultimately solve your business needs.

For example, you mentioned that you wish to produce school applications from the database. I just don't think that you will be able to do that. There are too many formats, with too many different fields that don't necessarily map.

Secondly, are you planning to enter all admission requirements for all schools (in USA!). Aren't there online database already that categorize schools? It's easy to map SAT scores, but what about qualifications that aren't so cut and dry or numerical?

And..if two schools have 5 different and unique to the school qualifications, does a student need to have all 10 qualifications in their record in order to have those two schools appear as schools for which they are qualified?

Can you restate what the business need is here? Again, to make FM into an AI system will take a huge amount of data. Perhaps just a FM student file that you can search, comparing to an online school search, then cross-ref the two lists.

[charlesoutcalt]

This has all been tremendously helpful. If we could discuss this in more depth, that would be terrific. You can reach me at

charles.outcalt at gmail.com

Thanks!