3 Replies Latest reply on Oct 5, 2010 4:10 PM by philmodjunk

    Importing Records from Excel in FileMaker Pro

    matthewul

      Title

      Importing Records from Excel in FileMaker Pro

      Post

      We have a report that runs from our FileMaker Pro v10 database and exports to an Excel spreadsheet.  It has been easier to manipulate this data (change fields, etc.) in Excel than in FileMaker.

      Is it possible to take these updated records in Excel and import them back into the FileMaker database, to overwrite the old records with these updated records?


      Thanks

        • 1. Re: Importing Records from Excel in FileMaker Pro
          philmodjunk

          Yes you can, but given FileMaker's rich array of layout options, I'm suprised this is easier in Excel. What operation can you do in Excel that you can't in FileMaker? A block copy and/or paste action perhaps?

          Provided you have a field that functions as a unique identifier for each record, there's an update matching records option you can specify when using Import Records to import the data.

          • 2. Re: Importing Records from Excel in FileMaker Pro
            matthewul

            I'm sure FileMaker would prove to be better to do record field updates, but my limited experience with FileMaker scripting means that for now, we have some Excel formulas that are already available to make changes to the records.  Some are simpler, like find & replace functions, others are more complex...such as searching for records based on multiple field values, then changes specific fields.  I've tried playing around on our test database with our existing layouts, but haven't been able to get a layout that allows me to edit any field I need.  It seems some are protected from editing - which I'm still trying to figure out.


            So in the meantime, I'm looking to use some Excel reports that have been modified to our end-users' liking, and get those updated records back into the FileMaker database.


            Thanks

            • 3. Re: Importing Records from Excel in FileMaker Pro
              philmodjunk

              Your "protected" fields are likely calculation fields. Like a calculation cell in excel, you can't just type data into it directly, you have to edit one of the fields the calculation references. FileMaker gives you a different option: You can define an auto-enter calculation on a field. This gives you several ways to control what data appears in the field while still allowing you to edit the data so that might give you some ideas you can check out.