8 Replies Latest reply on Mar 7, 2014 11:16 AM by camillabradshaw

    Importing from excel to filemaker using 2 tables

    camillabradshaw

      Title

      Importing from excel to filemaker using 2 tables

      Post

           I have set up a filemaker pro 12 database for a conference which i am running. company details are in one table and delegate details are in another table. The two are related by a company code (automatically generated on creation of a new company record) which links delegates to their parent company.

           I would like to import a list of delegates into the database but some of the information is held in the company table and some in the delegate table.. When importing, i can create new company records and input the company information (company address etc) but does anyone know if i can import the delegate details at the same time? (which would mean creating a new delegate record which is linked to the company record)

           I don't seem to be able to import into 2 tables at the same time..

        • 1. Re: Importing from excel to filemaker using 2 tables
          philmodjunk

               Imports have to be one table at a time. Sometimes, to get the results that you want, you have to import into table C, then use a script to parse the imported data from C into tables A and B.

          • 2. Re: Importing from excel to filemaker using 2 tables
            camillabradshaw

                 that's helpful, thank you. do you happen to have a sample script? (writing scripts definitely isnt my strong point!)

            • 3. Re: Importing from excel to filemaker using 2 tables
              philmodjunk

                   Without a few more questions answered, a sample script stands a good chance of being a poor "fit" to your needs.

                   Will you be importing the same file from the same location repeatedly? Or is this a one time thing to get your new database started with the current data?

                   Is there a column in the imported data that uniquely identifies the company? (A company name field will work for this provided that company names are always unique.)

              • 4. Re: Importing from excel to filemaker using 2 tables
                camillabradshaw

                     It will be for regular updates to add new contacts in bulk rather than manually one by one. There isnt a unique code but i could add in a column which corresponds with the company code in the database? Each person does have a company name but there may be instances where 2 individuals are from the same company..Their email address is unique to each one. I assume that if individuals are from the same company, they can't be imported as 2 delegates into the same company record?

                • 5. Re: Importing from excel to filemaker using 2 tables
                  philmodjunk

                       As long as you don't have two companies with the same name (this happens more often than you might think), we can work with the company name field. We can use the company name field in an added relationship to get to the company ID code for that company via some simple scripting.

                       My example assumes that the excel file always has the same name and is always placed in the same folder. A more sophisticated script can avoid that requirement but I'm trying to keep this example as simple as possible.

                       I am assuming these table names, field names and relationships:

                       Companies------<Delegates

                       Company::__pkCompanyID = Delegates::_fkCompanyID

                       You'll need to substitute the actual names for mine to get it to work in your database.

                       First, Open Manage | Database | Fields and specfiy "Unique Values", "validate always" as validation field options for the CompanyName field. This will be key so that you can import data into the Companies table to add New Companies without generating duplicates.

                       Next, Make a new Table Occurrence of Company and link it by companyName to a CompanyName field in Delegates. (You'll probably have to define a CompanyName field in Delegates.) You won't need to put this field on any layout or use it any where but for this purpose.

                       This will produce relationships such as this:

                       Companies|byName-----<Delegates>-------Companies

                       Companies|ByName::CompanyName = Delegates::CompanyName

                       This relationship will let us use the CompanyName to get to the corresponding value of __pkCompanyID.

                       Now the script:

                       Import Records [----> specify the name/location of your excel file here, import data into Companies only mapping those fields that contain company data]
                       Go to Layout ["Delegates" (Delegates) ]
                       Import Records [--->Import into Delegates table, only map to the CompanyName field plus those fields specific to a given Delegate record]
                       Replace Field Contents [ no dialog ; Delegates::_fkCompanyID ; Companies|ByName::__pkCompanyID ]
                        

                  • 6. Re: Importing from excel to filemaker using 2 tables
                    camillabradshaw

                         Sorry for the delayed silence, i have only just got round to testing this. Thank you so much for your help, reall yappreciated. I am currently stuck on the script step: Import Records [--->Import into Delegates table, only map to the CompanyName field plus those fields specific to a given Delegate record]

                         How do i import into the delegates table? i only have the option to import and then select file or folder path (not filemaker table)

                    • 7. Re: Importing from excel to filemaker using 2 tables
                      philmodjunk

                           Keep in mind that this is a SCRIPT. If you manually import records, you have to first select a layout based on the desired target table. With a script step, you select the table in the import field mapping dialog after adding the import records step to your script.

                      • 8. Re: Importing from excel to filemaker using 2 tables
                        camillabradshaw

                             ok go it. thank you again.. seems to be working now (after a little tweeking!). really appreciate your help.