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.
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??
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.
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?
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.
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?
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.
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.
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::__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