3 Replies Latest reply on May 14, 2014 8:38 AM by philmodjunk

    Importing a single column

    RosieUnderwood

      Title

      Importing a single column

      Post

           I have a file that has 5 tables- all of which contain individual ID numbers. I have imported a large amount of data from an excel sheet, updating current ID numbers names etc and adding new ID numbers with corresponding data into one of the tables. However I just need to import the individual ID column into the rest of the tables without any other data, I have tried a few ways to do this and sometimes it has imported correctly and others not- I just wondered what the best way to import a single column would be?

           Thanks, 

        • 1. Re: Importing a single column
          philmodjunk

               Importing a single column of data from excel is simply a matter of mapping a field in the FileMaker target table to that column and then not mapping any other columns.

               But it would appear that these "other tables" would be related tables and if the "ID number" is the only value to be imported and it's to update existing records, how do you determine which record is to be updated with a given ID number?

               And that may be an incorrect description of your issue due to details missing from your initial post. I suggest using Post a New Answer to post a more detailed description of what you are trying to do here.

          • 2. Re: Importing a single column
            RosieUnderwood

                 Well I have 5 tables:

                 Individual, Family, Storage, Samples, Clinical

                 And currently in all five tables there is 9000 individual records, and in all 5 tables there is the column of individual which are numbered from 1-9000. For the individual table all this detail is in an excel file, which also contains another 2000 individuals and their details for that table. So I am wanting to import that excel file into the individual table- I am still having issues with this too, I have been using the matching records, matching being the individual ID's and then importing the data and clicked add remaining - is that the correct way because it keeps missing appearing to have missed importing some records. 

                 Then for the other 4 tables I just need to add the rest of the individual ID's that are in the excel file and then I will manually enter the rest of the other columns in these tables as they are from a book.

                  

                  

            • 3. Re: Importing a single column
              philmodjunk
                   

                        So I am wanting to import that excel file into the individual table- I am still having issues with this too, I have been using the matching records, matching being the individual ID's and then importing the data and clicked add remaining - is that the correct way because it keeps missing appearing to have missed importing some records.

                   There are several factors that can affect the results that you get. Presumably, you specify only the ID column to ID field pair as your match fields. If you include other column/field pairings as match fields, your results will change.

                   This option matches to records in your target Tutorial: What are Table Occurrences?current found set. Thus, for the import you describe, you need to be sure to go to a layout based on the same table occurrence as you are specifying for your import (this is automatic in a manual use of this option, but not with a script). Then you need to select Show All Records from the Records menu to be sure that all records from your target table are in the found set.

                   Other issues can arise if you have a value in the ID field that fails to perfectly match the value in the excel column of ID numbers. Be especially careful to import using identical data types if your ID's are a mix of numeric and alphabetic characters, you might otherwise import with matching that ignores the alphabetic characters if your ID field in FileMaker is a number field and/or if your data from excel imports as a number instead of text.

                   

                        Then for the other 4 tables I just need to add the rest of the individual ID's that are in the excel file

                   I'm a bit puzzled as to why you need to import the ID's into these other tables.

                   First, an imported ID is not the ideal value to use as a primary key to link records in your relationships. It's better to use an auto-entered serial number in the Indivdiual table, importing the externally produced ID into a data field in this table, but no where else. Any time that you need to access this Imported ID in the context of one of the other fields, your relationship to the Individual table will make it possible to show this ID without needing to copy it to the related table.

                   Second, there is rarely any need to automatically create matching IDs into all your related tables like this. Each time that you need to record data in a related table, you can simply "manually enter the rest of the other columns" via portal to the related table (with the portal placed on a layout based on Individual) and FileMaker will link it to the current individual record for you. There are even ways to set this up that do not use a portal, but in each of these cases, you don't need to add a record into a related "child" table until you have other data to record in addition to the ID value used to link it to the correct parent record.