3 Replies Latest reply on Jun 8, 2013 7:45 PM by alas

    Roles Table in relationship pls help!


      Hi Guys,

      Let me reinstate my previous question in something much shorter.

      I have a Customers table and a Project table, related to each other. Now I am trying to add a 3rd table "ROLES" the reason for this table is beacuse I want to create a role for customers so they hava a different role in each project example: Wedding project= 2 related customers one bride the other groom, but the groom in this project could potentially be a father in other project or best man. I am assuming the ROLES table will be the child of both CUSTOMERS AND PROJECTS. My QUESTION Is this the best solution to integrate roles for a related customer to a project layout? Is any other better way to add a ROLE field without adding a table(like table occurance). Any help will be greatly appreciated. Thank you for your help!!!

        • 1. Re: Roles Table in relationship pls help!



          If I understand your situation correctly I would do the third table for roles.  Because ultimately you may have many different roles for a project (dual best men) and a person may be more than one role (across projects or even within the same project).


          So I would have a roles table with a unique ID for the role entry, the role name, the foreign key for the person's unique ID and the foreign key for the unique project ID. 


          I would imagine having two different views.   One from the project where you assign users to roles for that project.  And if that is done via a portal on the projects view showing the related roles records you can have the fk_ProjectID auto entered into the roles record.  You would only need to create a fk_UserID selection mechanism to add this to the roles portal record.  Could be a drop down, another portal showing the users (with scripting) or a popup window using a find (I use this approach myself adapted from someone else's example.


          The second view would be on the user's record where a portal would show the assigned roles and corresponding project.   Again, in this case, you'd need to address the selection method to find the fk_ProjectID for each assigned project. 


          If this makes sense conceptually we can discuss the mechanics. 

          1 of 1 people found this helpful
          • 2. Re: Roles Table in relationship pls help!

            Thank you so much alas, for taking the time to answer my question I really appreciated your help. I am kind of newbie in Filemaker (wedding photographer) and I am trying to get this DB going I been in lynda and other places to learn more about Filemaker (amazing program), I think your solution sounds good to me, now my question is how do I relate that table ROLES to the solution. Customers(parent pkid)------ Roles(child of customers fkcustomers)-----Projects(parent of Roles) finally Roles (child of projects fk) one of my main question is about the relationship of tables specially of roles to the current 3 tables I have Customres-Projects-Invoices. Thank you so much for your once again.

            • 3. Re: Roles Table in relationship pls help!

              Give me till tomorrow to reply.  Have to go offline right now.  If you can send a screen shot of your relationship graph that will help.  You are going to have to add new table occurrences of your tables to make the additional relationships.  Not hard (and it will help you in the future knowing how to do it). 


              And if you want to email me your file (you can do a copy without the data) I can mock it up for you to see what I mean (including the popup find window). 

              1 of 1 people found this helpful