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:
- Make sure I have a copy of the primary id’s of each record in the existing FMP Member table.
- Generate primary id’s for each Project by creating a new Excel column and filling with sequential numbers.
- Create the Project table in FMP, including a numeric primary key field.
- Import the Projects, with key field, from Excel into FMP
- 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.
- Create the (empty) Join Table in FMP with the two fields.
- Import the Excel file into the FMP join table
- 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.