TaiChi56 Posted August 10, 2012 Share Posted August 10, 2012 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 Link to comment Share on other sites More sharing options...
mr_vodka Posted August 10, 2012 Share Posted August 10, 2012 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. Link to comment Share on other sites More sharing options...
TaiChi56 Posted August 10, 2012 Author Share Posted August 10, 2012 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 Link to comment Share on other sites More sharing options...
lazarus Posted August 10, 2012 Share Posted August 10, 2012 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 Link to comment Share on other sites More sharing options...
eos Posted August 10, 2012 Share Posted August 10, 2012 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 Link to comment Share on other sites More sharing options...
TaiChi56 Posted August 10, 2012 Author Share Posted August 10, 2012 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 Link to comment Share on other sites More sharing options...
eos Posted August 11, 2012 Share Posted August 11, 2012 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. Link to comment Share on other sites More sharing options...
TaiChi56 Posted August 11, 2012 Author Share Posted August 11, 2012 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. Link to comment Share on other sites More sharing options...
eos Posted August 11, 2012 Share Posted August 11, 2012 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. Link to comment Share on other sites More sharing options...
TaiChi56 Posted August 11, 2012 Author Share Posted August 11, 2012 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. Link to comment Share on other sites More sharing options...
eos Posted August 11, 2012 Share Posted August 11, 2012 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? Link to comment Share on other sites More sharing options...
TaiChi56 Posted August 11, 2012 Author Share Posted August 11, 2012 Thank you. I will work on that. It will be a regular import. About once a week. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4291 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now