4 Replies Latest reply on Nov 20, 2010 5:31 AM by dicksmith

    Use Excel to pre-create a Many-to-many relationship in Filemaker



      Use Excel to pre-create a Many-to-many relationship in Filemaker


      I’m converting several Excel files to a FMP datebase.  So far I have250 records in the FMP Member table, and I have  200 records Projects in an Excel Project table.  Each member has many projects, and each project is owned by many members.  To avoid the FMP manual entry of all the relationships, I would like to manually create the record id’s in Excel before importing, so the relationships are already in place.  Here’s what I’m thinking:

      1. Make sure I have a copy of the primary id’s of each record in the existing FMP Member table.
      2. Generate primary id’s for each Project by creating a new Excel column and filling with sequential numbers.
      3. Create the Project table in FMP, including a numeric primary key field.
      4. Import the Projects, with key field,  from Excel into FMP
      5. Now for the join table.  In Excel, create a new worksheet with two columns, Column A for Member id’s, Column B for Project id’s.  Fill in this table manually.  Row 1: Member 1 owns Project 13. Row 2: Member1 owns Project 21. Row 3: Member1 owns Project 56.  And so on for all of Member1’s projects.  Then do the same for Member2, and repeat until I’ve handled all the Members.  Since each Member owns an average of 10 Projects, this worksheet has about 2500 rows.
      6. Create the (empty) Join Table in FMP with the two fields.
      7. Import the Excel file into the FMP join table
      8. Finally, in FMP, create the relationships with the Join Table so the Many-to-many relationship is implemented.

      Will this work?  Is there a better way? I’m running FMP 7 for Mac. 

        • 1. Re: Use Excel to pre-create a Many-to-many relationship in Filemaker

          Seems like step 5 is the same manual data entry you were trying to avoid doing in FileMaker, but now you are doing it in Excel and then have to import the data as well.

          In any case, after you import your data, you should define your Primary keys as serial numbers and update their next serial values to be greater than the largest value in your imported data so you avoid any new records getting values that duplicate existing records.

          • 2. Re: Use Excel to pre-create a Many-to-many relationship in Filemaker

            In Excel I can sort the records by Member so that all Members' projects are together, along with the Project ID's I've assigned in a single column. Then, in a blank adjacent column, I can enter Member1's ID and then autofill all the rest of the rows corresponding to Member1's projects.  Then, where the Projects of Member2 begin, I enter that ID once, then autofill.  And so on through Member250. Those two adjacent columns make up the Join table when imported.  It's all the autofills that I thought would save me the time.  I can barely stand the thought of populating the join table Member by Member, each with 10 or 15 projects. 

            A potentially larger problem may occur in Step 8.  I was playing with Comment's  Invoices Demo you sent me six weeks ago, http://fmforums.com/forum/showpost.php?post/309136/.  I was on the Invoices Layout, putting in new lines happily.  Then I opened Define Database and deleted the two one-to-many relationships to the Invoices Join table.  Then I put them back, in a move paralleling Step 8, where the tables are already populated and I just define the relationships after the fact.  When I came out of Define Database and returned to the Invoices Layout, I found that it was broken and would not do anything. So I conclude I can't just populate independent tables and then later come in and try to define relationships. Maybe it would work if I created the relationships before import.

            Any better ideas on how to manage the import?  I'm really a novice here, and am willing to try anything that sounds possible.

            And yes, defining the Primary keys post-import was on my list of things to do, should have included that as Step 9.

            • 3. Re: Use Excel to pre-create a Many-to-many relationship in Filemaker

              You should be able to define and modify relationships at any time in your development cycle. In fact, it's not uncommon to go back and modify relationships several times as you get a better understanding of your data, user needs and layout design. If your change "broke" something in the demo, then what you did changed some detail from the original setup that affected how that layout interacted with the data in your database.

              • 4. Re: Use Excel to pre-create a Many-to-many relationship in Filemaker

                I woke up this morning knowing how I had "broken" the demo database.  In recreating the relationships after breaking them, I had failed to check the box permitting the addition/deletion of field contents from the related table.  Now I understand yet one more thing about Filemaker. Thanks again for your help.