How are the relationships between the tables setup?
Table is at the bottom, what I do is I import to student Records Table first. Then I import to the grades table four times, one for H1, then H2, then H3, and then finally H4. Hope that makes sense. What I need is that each class of the student who is failing shows up in my portal one time only. Some students may only have one class failing or a student is failing all seven classes. H1, is the first nine weeks and H2 is the second nine weeks, we are not done with the 3rd nine weeks yet so actually at this time I only need to import H1 and H2. Thank you.Here are my tables: StudentID is the primary key. Fin is the Final grade and AVG FIN is the Final Average.
StudentID StudentID LastName Period FirstName Class Date of Birth Teacher Grade H1 H2 H3 H4 Fin Avg Fin
So do you have this relationship defined?
layoutTable::StudentID = PortalTable::StudentID
I'm not sure you do as the portal only shows a StudentID in the first portal row.
With a Portal Filter expression, you can filter out all records where the grade is a passing grade.
With an Update import, you could match the records by StudentID and Class so that the H1...H4 grades from your 4 imports are imported into the same record for a given student and class.
There are other possibilities here--including using the same field for H1...H4 instead of separate fields.
No I don't because I am not sure how to do that. How do I have the layout table = the portal table? Do I do it in Database management? Thank you. Is there a way that I can send it to you so you can look at how I have it setup? This forum does not allow for posting of zip files that I can see only pictures.
LayoutTable and PortalTable are just my place holders for your table occurrence names. This is indeed something you set up in Manage | Database | Relationships (Each "box" in this window is a "table occurrence".) LayoutTable is my name for whatever you have selected in "Show Records From" in Layout Setup.... PortalTable is my name for whatever you have selected in "Show Records From" in Portal Setup....
You have some kind of relationship in place or you couldn't have your portal.
Right, my first table is "Student Records" my second table is "Grades" my layout is from Student Records my portal is from the grades table. Just don't understand why it is making multiple line of the same record. I import from an EXCEL Spreadsheet in mass.
Yes, but how do you link a record in Student Records to records in Grades?
I would expect this relationship to match on StudentID:
Student Records::StudentID = Grades::StudentID
But in your screen shot, I can only see a student ID in the first portal record, and so I have asked for clarification.
I don't think it is making multiple lines of the same record. You've said: Then I import to the grades table four times, one for H1, then H2, then H3, and then finally H4.
This will produce the separate records you see in the grades table unless you specify "Update matching records".
Ok, I see what you are saying. Yes, Student Records::StudentID = Grades::StudentID but the minute I bring in the records into the StudentID it also brings in the students Grades and causes many lines to be filled. I do bring them in by "Adding records", Really confusing.
Ok, I see what is happening but don't know how to fix it. My student record is showing 7 files which is two students, one having one grade and the other student having 6 classes failing. My grade table is showing 98 records. My excel file only has 7 rows.
With an "Update Matching Records in found set" import, you could match the records by StudentID and Class so that the H1...H4 grades from your 4 imports are imported into the same record for a given student and class.
Please, I cannot figure this out. Even "Update Matching records in found set" is giving me 98 records, it keeps repeating the rows in the portal. I have redone tables in various ways and still having the same problem. Not sure if it is the way my EXCEL is set up. Here is the way it is setup maybe that will clue something in:
STUID LASTNAME FIRSTNAME PERIOD DOB CLASS TEACHER H1 H2 H3 H4 FIN AVG FIN
211799 Doe John 4 11/22/1995 Eng 1 PerAP Lancaster 69 58 0 0 64
164434 Black White 1 10/26/1995 Span 2 Wayne, John 59 41 0 0 50
164434 Black White 2 10/26/1995 Eng 1 Curtis, Tony 55 66 0 0 60
164434 Black White 3 10/26/1995 BIO Ball, Lucy 65 45 0 0 55
164434 Black White 4 10/26/1995 ALG 1 Cruise, Tom 64 60 0 0 62
164434 Black White 5 10/26/1995 W GEO Reynolds, Burt 73 53 0 0 62
164434 Black White 6 10/26/1995 COMMAPP Deniro, Robert 57 77 0 0 67
The devil is in the details. Exactly how did you use the import with the update matching option?
Referring to your data above, If you specify both the STUID and Class fields as your match fields during import, the above sample data should import as 7 records even if you import 4 times--once for H1...H4. I'm assuming that H1...H4 represent different quarters of the school year and that only H1 is populated with data during the first import and that each succeeding import happens at a later time and adds a column of data from the "H" section of the spreeadsheet.
Ok, see if this makes sense. I have two tables "Students" and "Grades" and I have STUDENID as primary key. I only have the field "class" in grades so not sure what to do with that. I import the student information into the "Students" table. Then I import grade information into the "Grades" table adn I do use update matching records by STUDENTID. I get 98 records in the grades record. I am usually quicker then this on figuring out what is wrong but this one has me confused. This is not the first time I have done databases in FileMaker Pro but it is the only the second time I have used Portals so not sure where I am screwing up. I know you are trying to point me in the right direction. Could I have my tables set up wrong?
When you import data into the grades table, you shouldn't just match by StudentID. You have multiple rows of data in your spreadsheet for the same student ID but for different classes. When you import into grades and match fields by both StudentID and class, you can import and match the data so that repeated imports update the same record each time in Grades.
Please take another look at your original post. I've pointed out an issue to which you have not responded: Why does the student ID number only show in the first row of your portal? Why don't I see it the the subsequent rows of data here?
OK, just deleted everything and started over with your suggestions you gave and it works now. Thank you.