Using Same Field Definitions in New Table

[charlesoutcalt]

Hello All. I'm sure this question is ridiculously easy for some of you, but I am stumped.

I have a series of CSV files (enrollment information for multiple years). The tables have about 70 fields each, with each in the exact same format.

I have imported one table, taking the time to define the fields before I did so. I would like to use these field names and types when I import the rest of my CSV files. I know that I could do so easily if I imported the rest of the files into the same table, but I want to create separate tables from each file, then relate these tables to one another when I'm finished. Because the files tend to have the same students, I really don't want to complicate things by importing the CSV files into any one table.

How can I use my field definitions again, without redefining them? I have a lot of files to import, and it would take forever to redefine those fields every time.

Thanks! I always get great advice from these forums.

[jteich]

Rethink the design of your database. Same things belong in one table. Imports don't create new tables when executed as user action repeatedly. Creation of tables is a developing action.

-jens

[Søren Dyhr]

but I want to create separate tables from each file

Oh no! - There must be something in the water?? This comes popping up more than I wish to know of - conceptually is this wrong, instead put a attribute that distinguise one year from the other. This is done by a straight forward number or textfield ...the good thing is whenver you make an import are the latest arrived records the found set, so you can make a Replace...

http://www.filemaker.com/help/Script-Steps52.html

...in this categorizing extra added field. It's better you experiences yourself why your wish have flaws, by reading how many threads there have been the last couple of days seeking help to the problems your vanity apparently dictates, but goes against relational theory as such:

http://www.fmforums.com/forum/showtopic.php?tid/181639/post/227079/#227079

http://www.fmforums.com/forum/showtopic.php?tid/181551/post/226929/hl//

http://www.fmforums.com/forum/showtopic.php?tid/181488/post/226684/hl//

http://fmforums.com/forum/showtopic.php?tid/181499/post/226500/hl/fenton/

--sd

[charlesoutcalt]

Hello, and thanks for your comments.

I do want to go ahead with separate tables for each file import. Here's my reasoning: these imports contain records of student enrollments. Each table will contain information on enrollment for a particular term. After I create the tables, I want to relate them together via the student ID variable.

If I import all the files into one table, then won't I end up with a very confusing mess, with some students represented more than once (students can and do enroll for many different terms over their careers at my university).

Is there any way to repeat a table's definition from one import to the next?

Thanks!

Charles

[Ender]

Is there any way to repeat a table's definition from one import to the next?

With FM8 Advanced you can copy and paste tables and fields. Or in FM8 you can use the New Table option in the Import dialog.

But I too would caution you against such a structure. All of the Enrollment years (terms?) should reside in the same Enrollment table, with a School Year field to distinguish between them. Any data that is common from year to year, like information about the Student should reside in a separate Student table, where there's only one record per Student.

This may not sound like the best solution if you're used to a flat structure, but this normalized design is much less of a hassle for maintenance, data entry, and reporting. The trick of relating amongst Enrollment records is easily done with self-joins.

Edited October 30, 2006 by Guest

[charlesoutcalt]

Hah--I remember you being extremely helpful with some of my questions from months ago! Thanks for speaking up again.

I am afraid I have a couple more questions. Sorry to go on and one, but I find this discussion very, very useful.

Here's the background information: I had been using SPSS to store records downloaded from my university's mainframe. This turned into a nightmare, as SPSS just isn't set up to handle data manipulation tasks. In addition, SPSS won't run on Intel Macs. That in mind, I would like to turn the entire data maintenance process over to FileMaker.

I have two types of files for each of approximately 40 terms, going back a few years.

File 1 contains demographic information for each student: ID, GPA, home address, program, etc. Students tend to appear in many of these files, since they tend to re-enroll term after term. Some of the information in these files changes (GPA, for example), other information does not change (ID, name). There are about 60 fields in this type of file.

File 2 contains enrollment information for each student, as of the term in question. This file contains student ID, in addition to the number of credits students took in each of the hundred or so terms that had occurred until that point. For the most part, information in these files does not change--if a student had 12 credits in spring 1985, she will always have those credits. However, each of these files contains slightly different information, as each file captures a new term. Students appear in these files if they appear in the File 1 file for the term in question. There are about 100 fields in this type of file.

In addition, I have various other files, all arranged by student ID: degree completion, etc.

My goal is simple: I would like to be able to see, instantly, what any one students' enrollments patterns were in light of other fields. For example, I would like to be able to define a set of students who were first enrolled in Fall 1998, resided in Arizona, were female, and had GPAs above 3.0. After I define this set of students, I would like to be able to look at outcomes: Did these students re-enroll the next term? The next year? Did they earn a degree?

I'm still trying to get my head around the advantages of a relational database. Should I create a separate table for each file import, then join these tables via relationships? Or try to fit all these records in one table (which would be horrendously complex)?

Any and all advice you can give would be amazingly appreciated!

[Stuart Taylor]

You would do very well to Listen to Jens and Sorens advice. The description or the data model you are discussing is possible the greatest mess you could ever get into.

You could save yourself alot of work by redesigning the structure of your database and get alot out of it.

As i see it all you would need to do is have a few databases.

1. Students

2. Courses

3. Course_Terms

4. Terms

5. Enrollment

6. Years

Enrollment.fp7.zip

Edited October 30, 2006 by Guest

[charlesoutcalt]

Stuart, you're reading my mind, or (more likely!), I'm reading yours. I'm drawing up a list right now of the tables I'll need. My list is short, and looks a lot like yours.

Am I right in thinking that some of the tables should be more or less lists of unique things, such as student IDs and term names, and other tables should be places to look at the intersection of these unique things? So, the 'students' table would contain a list of all unique student IDs, and the 'terms' table would contain a list of all terms?

If this is the case, then how do I handle the way these tables work together?

[charlesoutcalt]

Sorry--I just noticed your attachment! Wasn't looking for it.

It's a thing of beauty. If only I could create something like this, I would be EXTREMELY happy.

Studying your work, it seems that the layout called Student brings together information from the various tables. I will need to study this to try to sort out how you did this. I can't imagine being able to put something like this together--and you did it so quickly! Any tips on how you did so would be really great.

[Stuart Taylor]

Check out Define Database... / Relationships and Define Value Lists ... pick them apart and you will get it after a while ... after you have entered new students/courses/course terms ... you can just give enrollment student IDs and course term IDs and import it all in one go.

Can you see the advantage of relational databases now?

[charlesoutcalt]

I'm getting it, slowly. Pretty exciting stuff!

I'll puzzle over this for a bit, and see what I can sort out.

Thanks for all your help!

[Stuart Taylor]

Uniqueness is the key.

If you have to add it twice you went wrong somewhere.

You should very rarely have to duplicate tables if ever.

best

Stuart