3 Replies Latest reply on Jun 9, 2013 6:48 AM by LyndsayHowarth

    Need help with relationships with 3rd table.

    marksanchez

      Hello Technet community,

      My name Mark and I am a wedding photographer, last year I put together a basic DB on filemaker which has 2 tables (customers and invoices) I learned about relationships and I was able to make it work (basic setup). Now I got really into filemaker and want it to make couple of changes in my data base but I feel there are somethings beyond my knowledge (many to many tables). I will try to describe as detail as possible what I am trying to do, and any help will be greatly appreciated.

      1.My previous Customers table had 3 contacts in each record (bride, groom and any mom or dad), which I recently figured that was a bad choice. Also the project info was in the same table so 3 contacts plus project info in each customer table. Then I realize that each customer could only have one project and if they wanted any other session of photography(which it happen) I could only creatte a different invoice but not a project. Also the 3 contacts were seen as one contact and could not have independent projects.

      Solutions

      a.- I created a projects table now I can create multiple projects on each customer (basic one to many relationship)

      b.- I make each customer only show only one contact and re import the other 2 contacts as new customers with their new IDs.

      Now each customer has only one contact and the project is in a different table so each customers could have multiple projects.

      The help!!

      *Now my question is this

      When I create a new project I want to relate to the customers table and bring them to the project layout( so I have a project assigned A BRIDE, A GROOM, AND ANY PARENTS). Also the MOST important when I bring them to the project I want to assign a ROLE example of roles; bride, groom, sister, mom, dad, etc.This is where I get lost on the roles of the customers, I want a different role for customers on each project so a Maria McBrite could be a bride on one project and a mom on other project or a sister on another project. I figure I will need a 3rd table IF YOU GUYS COULD HELP Me on how I relate this table "ROLES" to CUSTOMERS AND PROJECTS TABLE, or if you may have any other suggestion on the DB Iam trying to put together.

      Once again thank you so much for your help and time.

      Previous tables : Customers, Invoices.

      Future tables: Customers, Projects, Invoices and Roles(which I need help to relate to other tables).

        • 1. Re: Need help with relationships with 3rd table.
          LyndsayHowarth

          What you need is a join table between Contacts and Projects....

           

          The ROLE table should contain 3 basic fields:

          Contact ID

          Project ID

          Role

           

          From Projects you can see all the IDs of the people involved and their various roles if you make an '=' relationship between ROLE and Projects.

          From Contacts you can see one OR MORE Projects related to that person if you creat an '=' relationshipt to the ROLE table.

           

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Need help with relationships with 3rd table.
            marksanchez

            Thank you so much lyndsay, if I undertood correctly the roles table will have on 3 fields: 1. contactid will be use to connect to contacts so contactid in roles is the foreign key (fk) samething with projectid so the role table will be the many from contacts and projects (assuming contacts and projects are already connected). Then the role field will have a value list (bride, groom, mom, dad, etc) so I can assigned the role field from contacts or projects Am I correct? Thank you so much once again I am kind of new in this so excuse my knowledge.

             

            Mark Sanchez

            • 3. Re: Need help with relationships with 3rd table.
              LyndsayHowarth

              Yes, Mark. That is it at the basic level. There are lots of elaborations including:

               

              - From both ROLES and PROJECTS you should be able to show (or calculate for local use) related Contact info...

              - From both CONTACTS and ROLES you should be able to  show (or calculate for local use) related Project info...

               

              You can also have other relationships to the same tables for different purposes... so it is a good idea to use new table instrances rather than the base one for the table...

              eg. You can create a Project_ROLES:: instance and a Contact_ROLES::  instance.

              You may also need from the PRojects table to specify a role... eg who is the BRIDE (only 1) or who are BRIDESMAIDS (there could be many). If in Projects you specified a global calcuation field which had a result of "BRIDESMAID" you could have another Table Instance for Project_BRIDESMAIDS:: which is matching the ProjectID and the BRIDESMAID to the Roles table.

               

              Hope that makes sense.

               

              - Lyndsay