7 Replies Latest reply on Feb 20, 2013 2:26 AM by roryduffy

    Import fields into Filemaker

    roryduffy

      Hi there,

       

       

      Does anyone know how to import fields/columns from an excel into FileMaker?

       

       

      I wish to do this because I am creating several hundred budget fields, and I want to save time rather than manually entering fields into manage database: budget 001, budget 002, budget 003 etc etc.

       

       

      On FileMaker Quick Start, there is an option to Convert an Existing File. This enables you to map columns from excel into a new FileMaker document.

       

       

      However, I've spent 2 weeks setting up my tables, relationships, values and scripts, and I don't want to start from a new FileMaker document. I want to import fields into an existing FileMaker document.

       

       

      Excel allows you to number rows sequentially, and then copy/paste Transpose (turn rows into columns). I need to then transfer the columns into FileMaker as fields.

       

       

      It seems a simple enough task (i.e., importing fields instead of records) but after searching the web extensively, I can't find a solution. FileMaker does perform an import columns in a new file when you select Convert an Existing File - but there doesn't seem to be a way to do this in an existing file. Frustrating!

       

       

      Please let me know if anyone has found anything. It will save me hours of manual field entering!

      Thanks,

      Rory

        • 1. Re: Import fields into Filemaker
          Kantala

          Hi Rory

           

          You can import an Excel file into FileMaker and have it create a new table at the same time.  Once you've selected your file and you have the window to map the fields, click on the 'Target' drop down and choose 'New Table (...'.  You'll get a list of fields called f1, f2, f3 etc.  Then click the box to 'Don't import first record' and you'll see the field names change to the column names from the Excel file.

           

          Hope this makes sense.

           

          Matt

          • 2. Re: Import fields into Filemaker
            roryduffy

            Hi Matt,

             

            Thanks for your response. I've never tried this, but it works great.

             

            However, it copies the fields into a new table. Is there no way of copying the fields into an existing table? I have my existing table recurring 140 or so times throughout my relationships graph, so I'd have to change all occurrences to the new table, which would take hours, not to mention disrupting my table schema.

             

            Even if I have to hack the file, it would save me hours of time to copy the fields into an existing table.

             

            Thanks,

            Rory

            • 3. Re: Import fields into Filemaker
              Kantala

              Hi Rory

               

              Your best bet would be to import the fields to a new table, then copy them to the existing table, assuming you have FileMaker Pro Advanced.  If not, that's a pretty good reason to get it!

               

              Regards

               

              Matt

              • 4. Re: Import fields into Filemaker
                roryduffy

                Ahh... I only have Filemaker Pro 12. I don't have Advanced. That answers my question then.

                 

                Thanks!

                • 5. Re: Import fields into Filemaker
                  roryduffy

                  Since my last post, I discovered an easy way of doing it without needing Filemaker Advanced, so just thought I'd share it with everyone in case anyone else is experiencing the same challenges.

                   

                  There are several apps online that record your keyboard strokes / mouse clicks, so you can set up a sequence and set it to repeat x number of times.

                   

                  I trialled a few of them and I found JitBit's "MouseRecorder" to be the simplest and best:

                  http://www.jitbit.com/mac-mouse-recorder/

                   

                  Set up your table / field schemas or value lists in Excel, and keep the Excel window open in one half of the screen, and Filemaker open in the other. Then it is a simple matter of:

                  - move mouse to Excel

                  - click in Excel

                  - command-C (copy)

                  - down (to move to the next row)

                  - move mouse to Filemaker

                  - click in Filemaker

                  - command-V (paste)

                   

                  You will need to experiment a bit with the speeds. Having it set too fast may cause errors, because keyboard / mouse recorders do not read what is happening in the command scripts.

                   

                  i.e., if you are working on a large file and low memory causes a glitch such that your click misses its target, then it will not take this into account and it will playback at the same speed regardless of what is happening "behind the scenes".

                   

                  But once you get used to it, it saves so much time!

                   

                  This method, of course, can be used for any repetitive action in Filemaker.

                  • 6. Re: Import fields into Filemaker

                    Hi Rory,

                     

                    "I wish to do this because I am creating several hundred budget fields, and I want to save time rather than manually entering fields into manage database: budget 001, budget 002, budget 003 etc etc."

                     

                    Whenever you find yourself creating fields with numerical sequence such as 01, 02 it is guaranteed to indicate incorrect structure.  Those fields should be records in a related table.

                     

                    "I have my existing table recurring 140 or so times throughout my relationships graph,"

                     

                    Another indication of major structural problems.  Seriously, we would help you get on right track if you consider changing it now.  If you try to restructure later it will be 20-times more difficult. 

                     

                    Also, meaningless serials are CRITICAL to use in binding relationships.  Using names is problematic because the first time someone's name is misspelled and a related record is created then their name corrected, you will break the relationship.  Or when a female gets married and changes their name etc.

                     

                    We'll help direct you towards a good structure.  :-)

                    • 7. Re: Import fields into Filemaker
                      roryduffy

                      Hi LaRetta,

                       

                      "I wish to do this because I am creating several hundred budget fields, and I want to save time rather than manually entering fields into manage database: budget 001, budget 002, budget 003 etc etc."

                       

                      Whenever you find yourself creating fields with numerical sequence such as 01, 02 it is guaranteed to indicate incorrect structure.  Those fields should be records in a related table.

                      Totally agree with you - this is exactly the way I think.

                       

                      In my own freelance databases, I use related tables for finances, but my company colleagues don't have fixed pricing structures for everything and they prefer to simply put numbers into fields (as they would in Excel), as a set of budget fields within the event record - partly because they haven't quite fully understood how related tables work. So in my company databases, I do this purely for simplicity's sake.

                      "I have my existing table recurring 140 or so times throughout my relationships graph,"

                       

                      Another indication of major structural problems.  Seriously, we would help you get on right track if you consider changing it now.  If you try to restructure later it will be 20-times more difficult.

                      How would this be a major structural problem? I'm intrigued!

                      Also, meaningless serials are CRITICAL to use in binding relationships.  Using names is problematic because the first time someone's name is misspelled and a related record is created then their name corrected, you will break the relationship.  Or when a female gets married and changes their name etc.

                      The only tables where I use names (instead of serial ID's) are individuals and organizations. Yes we have had problems with this - especially with duplicate individuals, as we have thousands in our DB - but it makes sense to attach names for individuals to a record, rather than ID's for individuals. There doesn't seem to be any other way around it.

                       

                      However, I do use serial ID's for every other table.

                       

                      I understand your 1st and 3rd points, but if you could explain your 2nd point, that would be really helpful.

                      Thanks!