9 Replies Latest reply on Oct 21, 2013 11:27 AM by philmodjunk

    Import Excel table with multi records to one person

    steve_c

      Title

      Import Excel table with multi records to one person

      Post

           I am pretty new to Filemaker and would appreciate some help or point me in the right direction with a training database I am starting.

           I would like to import an exel table to start and create  a new database.  I have placed the excel file into FM and it appears to work fine.  The excel table is basically a report from another database program.

           I have a number of fields in the table:

           ID

           First Name

           Last Name

           Training Course

           I have many more fields but I hope I can explain my issue with just the above.

           I have 5 rows of a record

           ID   First Name   Last Name   Training Course

           001 Jed                 Smith             First Aid

           001 Jed                 Smith             Abrasive Wheels

           001 Jed                 Smith            Fire Precautions

           001  Jed                Smith            Tractor

           What I need to try and create from the above sample data ( i have lots about 140 persons with up to 10 training courses each) is only one 001 Jed Smith with all the courses etc.

            

           Is there anyone can assist, hopefully I have explained my problem rather than confuse everyone out there?

           Regards

           Steve

            

        • 1. Re: Import Excel table with multi records to one person
          philmodjunk

               Open manage | Database and define a new table with the fields:

               ID, First Name, Last Name.

               Open Field Options for ID and select these options on the validation tab:

               Unique Values, Validated always.

               Import your excel data from the excel file into this table.

               The duplicate ID's will be automatically omitted during the import.

          • 2. Re: Import Excel table with multi records to one person
            steve_c

                 Phil

                 Thank you for your swift reply, I tried the above and had an input summary box at the end of the inport:

                 Total Records Added/Updated 1346

                 Records Skipped due to errors 1176

                 Table Created 0

                 The table created with the records had only one ID; One First Name and One Last Name with only one course????  I may have gone off your script by using the following fileds ID, First Name, Last Name with about 10 more fields, is this where I went wrong??

                  

                 Regards

                  

                 Steve

                  

            • 3. Re: Import Excel table with multi records to one person
              philmodjunk

                   This method cannot be used to create a new Table. It is used to import records into the existing table I described creating.

                   And your dialog shows that more than one record was successfully imported. It shows that 1346 records were added. Try changing the view on your layout to Table View instead of what I suspect is form view and you should see all the records that you just imported.

              • 4. Re: Import Excel table with multi records to one person
                steve_c

                     Phil

                     I was viewing in table view and could see all the records but the for each person that I had they only had one training course? and not the 6 or 7 they have?

                • 5. Re: Import Excel table with multi records to one person
                  philmodjunk

                       This table is not for storing training course data. your original table serves that purpose. This table is intended to store one record for each person and it can then be linked to your original table by ID.

                       From there, a portal to the original table can list all courses taken by that person.

                  • 6. Re: Import Excel table with multi records to one person
                    steve_c

                         Phil

                         I have managed to create what you indicated, is it possible to do something with the existing field (Training_Course) which is the many in the one to many i.e. one person can have many training cources?

                         When I go to form mode it allows me to view the person and when I flick through the records the training course field changes (good), is it possible to make this field into a drop down list using the existing data so that if I needed toadd a record the dropdown list is there?

                          

                          

                    • 7. Re: Import Excel table with multi records to one person
                      davidanders

                           An individual field can be on many layouts. Each instance on each layout can be set to Edit Field, Drop-Down or Checklist.

                           You can have two copies of a field on one layout - one set to Edit field, one set to checklist or dropdown.

                      • 8. Re: Import Excel table with multi records to one person
                        steve_c

                             David

                             Thanks for the reply, I have tinkered with other forms of databases i.e. alpha five, dataease etc but I just purchased FM Pro and am on a steep learning curve to get to know the basics.  I am currently watching a webinar and then will start on the videos that came with FMP, but I am not sure what you mean at this moment in time.  I will do the webinar and a couple of vids and see if I can understand your suggestion better.

                             Regards

                              

                             Steve

                              

                        • 9. Re: Import Excel table with multi records to one person
                          philmodjunk

                               Ultimately, you will likely want to set up a separate, but related table with one record for each course. Such could serve as the source of values for your value list and also serve a number of other purposes in your database.

                               But to work with the specific data that you have now. Select Manage | Value Lists from the File Menu. Click New and name your value list. Select the "use values from a field option" to get a dialog where you can select what values you want to appear from a table in your database.

                               Where I think that you are headed with this database: (and you probably have some climbing up that learning curve to do to get to where this makes sense)

                               Students----<CoursesTaken>-----Courses

                               Students::__pkStudentID = CoursesTaken::_fkStudentID
                               Courses::__pkCourseID = CoursesTaken::_fkCourseID

                               Students has one record for each person that has taken at least one course.

                               Courses has one record for each course offered.

                               CoursesTaken--called a "join" table is used to list each course that has been taken by a specific student. There is one record for each course taken by that student.

                               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained