Records not showing in Portal

[TaiChi56]

I have read and watched videos and still have problems getting my portal to work. I have an EXCEL spreadsheet that is set up to give the studentID, StudentName, then their courese. I am trying to get the courses to show in a portal. Right now one student has 7 records each for a different course. Cannot seem to get it to work. I have attached so someone can point out where I am messing up. Thank you.

studentrecord.zip

[mr_vodka]

You would have to do at least 2 imports. The student table would store relevant information for the student. Then the courses would have top be imported to the courses table where the studentID would be used as a foreign key to the parent Student table.

[TaiChi56]

I have tried that and I get the same results it may be the way the EXCEL spreadsheet is setup also. I attached the spreadsheet to show my example of how we get our data sent to us. I cannot change the way the send the data. Thank you.

Book3.zip

[lazarus]

Try the following file. Maybe that will help you figure things out. Things that changed:

Added table 'Student_Schedule'

Modified relationships

Notice the relationship 'All Schedules' and how it is being used on the 'Form View' layout.

Also the StudentID needs to be a unique value if you are entering it manually.

StudentRecords_20120810.zip

[eos]

Have a look into this file; this may help you set up an automated import.

On the other hand, there are more problems than just the portal

Your data model isn't normalized: there should only be one record for each student in the Students table, with many related Schedules records (which is actually something like Enrollment), and you also should have tables for Courses and Teachers. All the data within Enrollment can then be displayed in Enrollment (and other related tables) via the IDs, without any need to copy it, and each with central place (record) where you can maintain it.

You will notice that in this corrected version there is only record per student, but the (also corrected) portal shows all the courses he/she is enrolled in, via the keys in Enrollment (which act like a sort of distribution table, since all the other entities are represented here).

Check out the script for the import, but also the Relationship Graph.

PS: As you're obviously using FMP12, you should correct your profile.

StudentRecords_FMForum_normalized.fmp12.zip

[TaiChi56]

Thank you lazarus and eos. I cannot seem to import to either databases. When I do it, with EOS's database I get the portal poplulated but then I still have 7 records per students when I go to the next record. I do not know how you kept it at the portal. Are you importing in form view? Because if I use the import or clean I get nothing. Thank you for the great help. FmForumCopy.zip

[eos]

You need to do the import into Enrollment, not Students, because (as far as I can tell) that's what the rows in the Excel spreadsheet are. Note that my script does some checks to flag the imported records; in a fully normalized structure, you could use these flags to generate new records in the Student and Courses tables for Enrollment records which have no records in the “parent” tables. (To be able to do the same for Teachers, their names would have to be provided in a more structured and consistent manner. )

btw, if you delete all but two records from the students table, then delete all existing records from Enrollment and start the script, you will have 15 Enrollment records, but still only two in Students.

Maybe you can tell a bit more about what this database represents exactly, and what you're doing with these data; this would help to give better informed advices.

[TaiChi56]

Ok, I know that I am a pain. I deleted all the records and then imported the records in the Enrollment table. But when I go to form view there is nothing there. I run the script and it brings in the records like it is suppose to in Enrollment but nothing shows up in the Form view.

What I am doing with this database:

Our school wants to be able to run a database that we can use on the Apple IPAD. I have that part figured out. Unfortunately the way the EXCEL spreadsheet is setup cannot be changed. We want to be able to run the EXCEL spreadsheet about twice a month and update the database. The database is going to be used on the IPAD to be able to look up a student and see what class they are in at a particular time.

The problem we have been having though is that when you click on a name you have to click 7 times to see the whole shedule. Your database took care of that problem. But when I try it I get either nothing in the Form view or I still get 7 records per student.

Thnk you for your help.

[eos]

Well, that's strange, because they're showing up for me.

Make sure that the StudentID_fk_import is populated in Enrollment (which it should), and that the Relationships stills works via Student ID to StudentID_fk_import.

You could again download the zip from my post, since it has Enrollment records showing in the portal, and check again if this is identical to what you have.

[TaiChi56]

Ok, did as you suggested and downloaded the file again from your post. I then went ahead and changed the script name to my original file that has 19782 records. It imported all of them into the enrollment table without a problem. But when I go to the form view it only shows the orignial two records.

[eos]

Ok, did as you suggested and downloaded the file again from your post. I then went ahead and changed the script name to my original file that has 19782 records. It imported all of them into the enrollment table without a problem. But when I go to the form view it only shows the orignial two records.

Of course you only see those two records, because there's no code which generates those missing “parent” records (Students, Courses) from the Enrollment records.

But that's not a big deal: write a script that first sorts by student name, then loops through the records and marks duplicates, then finds the singletons and imports them and their IDs into students; then go back and do the same for Courses (where you have to generate the IDs in the Courses table, or alternatively use the course code.)

Is this going to be a regular import, or a one-off affair?

[TaiChi56]

Thank you. I will work on that. It will be a regular import. About once a week.