4 Replies Latest reply on Oct 26, 2009 8:18 AM by TSGal

    import from Excel with matching field and new records

    Detlef

      Summary

      import from Excel with matching field and new records

      Description of the issue

      Hi,when I try to import records from Excel using the ID field as matching field and the option to add additional records as new records the additional records are not added, but the ID value increments like it did.When I choose update existing records in found set the additional records are added and the ID counter increments, but is not populated in the corresponding field on the layout. I am using FMP 10 Advanced with Windows XP and Excel 2007. Thanks.

        • 1. Re: import from Excel with matching field and new records
          TSGal

          Detlef:

           

          Thank you for your post.

           

          Unfortunately, I am unable to replicate the problem.  This is what I have done:

           

          1. I created an Excel spreadsheet with the following data:

           

          ID - Name

          1     TSGal

          3     Detlef

          4     New person

           

          2. I saved the file as Workbook.xlsx

           

          3. I created a table in FileMaker Pro 10.0v3 with the following fields:

           

          ID (Number)

          Name (Text)

          Location (Text)

           

          4. I entered the following information:

           

          1 - T - Santa Clara

          2 - X - Silicon Valley

          3 - D - Forum

           

          5. I then imported the records from "Workbook" choosing the options:

          Update matching records in found set

          Add remaining data as new records

          Don't import first record (contains field names)

           

          6. The results in the found set were:

           

          1 - TSGal - Santa Clara

          3 - Detlef - Forum

          4 - New person -

           

          7. I deleted and renamed the records like before in step #4.  I omitted the second record, so the found set included two records; ID=1 and ID=3.

           

          8. I imported from Workbook with the following options:

          Update existing records in found set

          Add remaining data as new records

          Don't import first record (contains field names)

           

          9. The results were identical as step #6.  That is:

           

          1 - TSGal - Santa Clara

          3 - Detlef - Forum

          4 - New person

           

          -----

           

          Please let me know what I'm doing differently than you, and I'll try to replicate the problem here.

           

          TSGal

          FileMaker, Inc. 

           

          • 2. Re: import from Excel with matching field and new records
            Detlef
              

            Hi TSGal,

             

            there is indeed one thing I forgot to mention. The database file is hosted by Filemaker Server 7 and was created by Filemaker 8.5.

            I just copied the file to my local drive to open it as host with the same behavior.

             

            Also, in my Excel file the additional records do not have an ID number. I leave that field blank.

             

            I can send you the database and excel file if that helps.

             

            Thanks, Detlef

            • 3. Re: import from Excel with matching field and new records
              TSGal

              Detlef:

               

              Yes, I would like to see the database file and Excel file.  I have sent you a private message (top of this page - right side - X Messages) with instructions where to send the files.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: import from Excel with matching field and new records
                TSGal

                Detlef:

                 

                I received your files.  Thank you.

                 

                I should have read your last post more carefully.

                 

                Yes.  If you are matching on a field, and that matching field is blank, the record will not be added.  You would need to assign a value to those new records, or put those new records in a separate sheet and import them separately, allowing the Auto-Enter to then assign a value.

                 

                I have forwarded this entire thread to our Development and Product Management departments so they can consider this for a future release. 

                 

                TSGal

                FileMaker, Inc.