6 Replies Latest reply on Jan 29, 2010 5:08 PM by michaelbeach79

    Many to many relationships

    michaelbeach79

      Title

      Many to many relationships

      Post

      Hello,

       I am relatively new to Filemaker and I am trying to use many to many relationships in Filemaker Pro 10 on Mac (10.6). The tables are:

      County (one to many)

       to

      County Worker (many)

      County Nurse  (many)  there can be many workers or nurse for each county.

       

      I then have:

      Client (one to many)

      to

      Bridge Table (many)

      to

      County (one to many) 

       

      All tables have unique ids and are linked to field keys.

       

      My problem is that I cannot access the County Worker and County Nurse info from the Client table.

      I need to assigne a county for each client (this works fine), then I need to assign a County Worker and Nurse from the county to the Client table. I have tried drop down lists but this does not seem to work because of the one to many from the County to the Worker and Nurse tables.

      The Worker and Nurse could be connected to the Cient table as a one to many, but I would like to access them through and keep them connected to the County,  I also don't want to create a loop that might cause problems later on.

      Any advice would be appreciated.

        • 1. Re: Many to many relationships
          bcooney
            

          All staff in one table with type (either worker or nurse). You could have type table and assign a typeID to Staff record. Join table btw County and Staff.

           

          Assuming each Client is only assigned one worker and one nurse:

           

          Client table should have three foreign keys: CountyID, NurseID, WorkerID. Assign County using value list of County IDs (Name). Assign Nurse using related value list (relationship to join table btw County and Staff from Client to join cartesian clientIDxjoinkey and client::countyID = join::countyID and Client::TypeIDNurse = TypeID). Assign Worker using related value list (relationship to join table btw County and Staff from Client to join cartesian clientID x joinkey and client::countyID = join::countyID and Client::TypeIDWorker = TypeID).

          • 2. Re: Many to many relationships
            michaelbeach79
              

            Im sorry but Im still confused. Are you sudgesting I link all three County, Nurse, and Worker to the Client table through one join table?

            Instead of associating with the County first.

            • 3. Re: Many to many relationships
              bcooney
                

              Please answer these questions:

              1. Can a worker/nurse be related to more than one County? (My post above assumes so-the only join table)

              2. Can a client be assigned more than one worker/nurse? (my post assumes "no", just one of each)

              3. Can a client be assigned to more than one County? (my post assumes "no").

              • 4. Re: Many to many relationships
                michaelbeach79
                  

                A client could have only one nurse at any time, although they may change.

                A client could have one worker and one case manager, although its usually only one or the other (thats why I grouped them together).

                A client can only have one county.

                 

                A county can have many workers/managers and nurses.

                 

                Ideally, in the client record, I would like to choose the county the client resides in and have the option to choose from a list of workers or nurses, or be able to enter a new worker or nurse in if they are not in the list. 

                • 5. Re: Many to many relationships
                  bcooney
                    

                  You need four tables: Staff, StaffTypes, Clients, Counties.

                   

                  County

                  __kP_CountyID

                  County

                   

                  Staff

                  __kP_StaffID

                  _kF_StaffTypeID

                  _kF_CountyID 

                  Name_First

                  Name_Last

                   

                  StaffTypes

                  __kP_StaffTypeID

                  Type

                   

                  Clients

                  __kP_ClientID

                  _kF_CountyID

                  _kF_NurseID

                  _kF_WorkerID

                  _kF_CaseMgrID

                  TypeIDNurse_c = 100 // given this is the ID for a nurse in your StaffType table

                  TypeIDWorker_c = 101 // given this is the ID for a worker in your StaffType table

                  TypeIDCaseMgr_c = 102 //given this is the ID for a case mgr in your StaffType table

                  Name_First

                  Name_Last 

                   

                  Relationships:

                  Build a relationship: Client>Stf~Nurses. Client relates to Stf by CountyID. That filters the available Staff by County. However, since you want this relationship to only show Nurses, you need to filter by that StaffType, too. So the relationship has two predicates: Client::_kF_CountyID = Staff::_kF_CountyID AND TypeIDNurse_c=Staff::_kF_StaffTypeID.

                  This is the relationship you'll use for a related value list from Client to Nurses. 

                   

                  Create similar relationships for each staff type from client. 

                   

                  Value Lists

                  Counties (__kP_CountyID and County)

                  Nurses from Client by County ( related value list using the above relationship Nurses)

                  Workers from Client by County (related value list)

                  CaseMgrs from Client by County ( related value list) 

                   

                  If the required Staff Name does not appear, you'll need to script the creation of a staff member. You could put a button on the Client Form that pops up a new window to a Staff Form and does a New Record, or just have your user go to the Staff form and create a new Staff record. They'll need to assign them to a County, as well.

                   

                  hth,

                  B

                   

                  PS: I don't know how to post demo files, or I'd just have done this for you.

                   

                  Question: Do you need to keep track of a history of Staff Assignments to a Client? If so, you'll need another table. 

                  • 6. Re: Many to many relationships
                    michaelbeach79
                      

                    Thanks,

                     Things are working now.