How to setup layout to accept an EXCEL import

[TaiChi56]

Everytime I think I have it I get confused. I have an EXCEL spreadsheet with 500 students listed on it. It has their grades by what period and who the teacher is. Not all students have all 7 periods. Some have one some have more than one. This report is only for failures. The periods are listed under the column Period.

I am not sure how to set up the layout to be able to accept the import correctly. In other words I know when I import I am importing the column with the period but that stduent my have more than one period. How does the layout know?

I have attached a sample of the EXCEL spreadsheet. It has two students, one has only one class failing the other one has muliple so I need to be able to show it. Hope this makes sense. Thank you.

FMForums.zip

[comment]

What are these:

H1 H1 H3 H4 Fin Avg Fin

[TaiChi56]

What are these:

H1 H1 H3 H4 Fin Avg Fin

Sorry, those are the nine weeks. Suppose to be H1, H2, H3, H4, Fin, Avg Fin

Which is 1 nine weeks, 2nd nine weeks, 3rd nine weeks, and 4th nines weeks, then Final, and Average Final.

Sometimes there is a Fin grade because the student dropped that class and went into another one.

[comment]

So which of these six columns do you need in your solution?

[TaiChi56]

So which of these six columns do you need in your solution?

Everything you see in the EXCEL sheet I need. I am just confused how to put it in a layout.

[comment]

It's not a question about a layout - it's about tables and fields. You need at least two tables here: Students and Grades.

First, you import the student data (StuID, LastName, FirstName, DOB, Fin, Avg Fin) into the Students table (updating matching records +add remaining). Then you need to do 4 imports into the Grades table, the first one importing StuID, Period, Class, Teacher and H1, the second one StuID, Period, Class, Teacher and H2, etc.

If you need to do this often, it would be best to import all data as is into a temp table, then script a series of imports from the temp table into the final tables.

[TaiChi56]

It's not a question about a layout - it's about tables and fields. You need at least two tables here: Students and Grades.

First, you import the student data (StuID, LastName, FirstName, DOB, Fin, Avg Fin) into the Students table (updating matching records +add remaining). Then you need to do 4 imports into the Grades table, the first one importing StuID, Period, Class, Teacher and H1, the second one StuID, Period, Class, Teacher and H2, etc.

If you need to this often, it would be best to import all data as is into a temp table, then script a series of imports from the temp table into the final tables.

I will try that, thank you.

[TaiChi56]

I will try that, thank you.

Ok, this is not working for me. I apparently do not understand how to import into the grade table. When I try to import the grade table is grayed out. I am attaching my sample database which used the sample EXCEL sheet I already uploaded to this thread. What am I doing wrong? I need each period that the student is failing to show up with the proper grades. Thank you.

DatabaseforFMForums.zip

[comment]

When I try to import the grade table is grayed out.

Go to the Grades layout before importing.

Note that there should be on only one H field in the Grades table, and it needs to be populated by the corresponding number following each import of a column.

[TaiChi56]

Go to the Grades layout before importing.

Note that there should be on only one H field in the Grades table, and it needs to be populated by the corresponding number following each import of a column.

Thank you. But how do I keep all the grades on one record? I have now 6 records for the same student. It shows each period on a different record. I would like it to show on one record so when a teacher looks up a student that teacher can see all the periods and grades that the student is failing all on one record. Thank you.

[comment]

I have now 6 records for the same student.

True, and each record shows 4 grades - that's 24 grades in total. Therefore, at the end of the import, this one student should have 24 related records in the Grades table. Or, at the very least, 6 related records with 4 grades each (though I am not sure how much you'll be able to do with this in terms of reporting).

[TaiChi56]

True, and each record shows 4 grades - that's 24 grades in total. Therefore, at the end of the import, this one student should have 24 related records in the Grades table. Or, at the very least, 6 related records with 4 grades each (though I am not sure how much you'll be able to do with this in terms of reporting).

Ok, now the records were imported and for Black, White it shows all records as the last record of COMMAPP, Deniro, Robert.

There has to be a way that I can import all 6 records for Black, White and show it on one record in FileMaker Pro so when a teacher puts in the STUID it will show all the students information on one record screen. I hope. I uploaded the lastest database I was working with. Thank you for your help so far.

FM-Forums_1250.zip

[comment]

Somehow you have managed to import the same record 6 times into the Grades table. I guess you have chosen to update matching records - this should only be done when importing into the Students table.

Also, the fields from Grades on the Student layout should be in a portal.

[TaiChi56]

Somehow you have managed to import the same record 6 times into the Grades table. I guess you have chosen to update matching records - this should only be done when importing into the Students table.

Also, the fields from Grades on the Student layout should be in a portal.

Ok, getting close. I have a portal and brought in the grades. But now my portal is showing 5 of 1st period, 5 of 2nd Period and so forth. The last one it is showing about 8 rows of 6th period with the grades. Not sure what happened.

I just noted that when I bring in the records for the students it brings all the files in such as the grades but with multiple lines. Driving me crazy.

I attached it so you can see what it looks like. Thank you.

FM_Forums_1425.zip