14 Replies Latest reply on Mar 9, 2011 11:34 AM by taichi56Templar

    Repeating records in a Portal

    taichi56Templar

      Title

      Repeating records in a Portal

      Post

      I am building a database that is keeping up with student 9 week failing grade. When I imported the information into the database I get multiple lines of the same information. As you see from the image it is repeating the same information. I just need one line of each class.

      FM.GIF

        • 1. Re: Repeating records in a Portal
          kilbot

          How are the relationships between the tables setup?

          • 2. Re: Repeating records in a Portal
            taichi56Templar

            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
               

             

            • 3. Re: Repeating records in a Portal
              philmodjunk

              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.

              • 4. Re: Repeating records in a Portal
                taichi56Templar

                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.

                • 5. Re: Repeating records in a Portal
                  philmodjunk

                  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.

                  • 6. Re: Repeating records in a Portal
                    taichi56Templar

                    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.

                    • 7. Re: Repeating records in a Portal
                      philmodjunk

                      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".

                      • 8. Re: Repeating records in a Portal
                        taichi56Templar

                        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.

                        • 9. Re: Repeating records in a Portal
                          philmodjunk

                          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.

                          • 10. Re: Repeating records in a Portal
                            taichi56Templar

                            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

                            • 11. Re: Repeating records in a Portal
                              philmodjunk

                              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.

                              • 12. Re: Repeating records in a Portal
                                taichi56Templar

                                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?

                                • 13. Re: Repeating records in a Portal
                                  philmodjunk

                                  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?

                                  • 14. Re: Repeating records in a Portal
                                    taichi56Templar

                                    OK, just deleted everything and started over with your suggestions you gave and it works now. Thank you.