1 Reply Latest reply on Jan 26, 2016 2:35 AM by Mike_Mitchell

    Additional linked fields in many to many relationship?

    phillegg

      I have a database containing journalists and newspapers that includes a many to many relationship between contacts and companies.

       

      In my current situation I have the field ‘ROLE::Job Title’ in the join table that allows me to have a job title for each contact relating to each company, but this is not linked to the company and if the company is changed the job title doesn't change with it but stays as is.

       

      This does not fulfil what I’m looking to achieve, as I would like this ‘Job Title’ field to be linked to the currently selected company, so that when the company is changed I am able to redefine the job title - and - should I reselect the previous company, then the job title for that company would still be stored and hence reappear.

       

      I therefore need a way to store a ‘job title’ field for each Company a contact works for which will stay in the system even when the Company is deselected – a sorted of ‘linked field’ that relates to a particular contact AND company.

       

      This is necessary as a contact may work for a company, then leave, and at a later date start working for them again. Also by having the ‘job title’ linked to the company, should an office worker/ database user change the company, there is not the danger of the job title field being left unchanged and hence incorrect for the newly selected company.

       

      I’m not sure this can be achieved with the current table structure in which the ‘job title’ field is in the join table (ROLE) and may need an additional table to hold all the ‘job titles’ for each company the contact is related too.

       

      I would very much appreciate someone’s help in resolving this issue and have included various images showing the current relationship plus attached the programme I am working on (which has originated from the FIlemaker 14 ‘Contacts’ starter solution) - see right-hand side of 'contact details' page for this portal.

       

      The images are:

       

      1. Field layout for Companies Portal showing inspector for Company Name field
      2. Field layout for Companies Portal showing inspector for Job Title field
      3. Relationship graph showing CONTACT - contact_ROLE relationship
      4. Relationship graph showing contact_ROLE - contact_role_COMPANY relationship
      5. Value List for Company

       

      Image 1

      Field layout for Companies Portal showing inspector for Company Name field.PNG

       

      Image 2


      Field layout for Companies Portal showing inspector for Job Title field.PNG

       

      Image 3

       

      Relationship graph showing CONTACT - contact_ROLE relationship.PNG

       

      Image 4

       

      Relationship graph showing contact_ROLE - contact_role_COMPANY relationship.PNG

       

      Image 5

       

      Value List for Company.PNG

        • 1. Re: Additional linked fields in many to many relationship?
          Mike_Mitchell

          This is relatively easy to do. Just add a single flag (call it "Active" or something) in the join table and set it to 1 if the person is currently with that company, 0 if not. Add a constant value of "1" to each parent table and create an additional relationship predicate between those constants and the "Active" field. Then if you want to break the connection between a person and a company, just set the flag to 0 instead of deleting the join record.