11 Replies Latest reply on Dec 21, 2015 5:46 AM by phillegg

    Displaying fields in portal from many-to-many relationship

    phillegg

      I am relatively new to Filemaker Pro but do have some basic experience with building solutions and am in the process of reading the Filemaker Pro Advance training course.

       

      Meanwhile, I am trying to build a contacts database for use in the music business and have used the contacts.fmp12 starter solution that comes with Filemaker Pro14 Advance as a starting point. By editing the basic Contacts starter solution, I wish to create a separate table for Companies so I can have a many-to-many relationship between the contacts and the companies (the thinking behind this is because a journalist can write for many publications and a publication can have many journalists)

       

      To achieve this I have created a separate table for the companies called COMPANY and a join table called ROLE, linked the CONTACT::ID_CONTACT field to ROLE::IDf_ContactID and linked ROLE::IDf_CompanyID to COMPANY::ID_COMPANY.

       

      I have created a relationship between these tables as follows:

      • Contact to Role relationship (see image 1 below)
      • Role to Company relationship (see image 2 below)

      1_Contact to Role Relationship.png

      2_Role to Company Relationship.png

       

      Then: I have created a portal in the Contact Details layout that “shows related records from: contact_role_COMPANY” (see previous images for relationships diagram) and included various fields from the Company’s address fields (see image 3 below)

       

      3_Contact Details layout sowing portal and inspector for the Company Name field.png

       

      However, at first I had ‘allow creation of records in this table via this relationship’ ticked for IDf_ContactID (on Contact Role relationship) plus IDf_CompanyID and ID_Company (on Role – Company relationship). To be honest I’m not sure what these should be set to and feel that this may be an area where my problem lies. By having these options ticked, I was able to access a drop-down menu on the COMPANY::name field from the companies in the database. But when I selected a company the other address fields in the portal (COMPANY::street, COMPANY::city, COMPANY:: State etc) did not change to the correct values - i.e. show the address details for the Company chosen from the drop-down list. But in fact, what happens is it creates a new company record of the same name (that I selected) which because it's a new record has no address details.

       

      Then, realising that there was a problem with a new record being created, rather than selecting one already in the database, I unticked all the ‘allow creation of records in this table via this relationship’ - but then I couldn’t access the fields in the portal to select a company at all so the portal was then purely "display only" with no ability to select a company.

       

      Basically, what I want to do is have a portal on the Contact Details page layout  (as in image 3 above) that shows the name and address of each company that the contact is related to. In practice the user can access a drop-down list of the company names imported into the database by clicking on the COMPANY::name field in the portal and choose the company to include. I'd like the portal to work so that when the user selects a particular company, the other fields in that portal's record will show the relevant information (like the addess fields show the address for that particular company)

       

      To be honest, I’ve got myself in a bit of a mess and not sure how to get this to happen, although when i read through this but in the Advance14 Training Course I thought I understood - but in practice, I'm obviously not understanding the process in full. I hope I’ve explained myself OK and would appreciate someone’s help. This is the first time I have posted something on this forum but have heard great things about it from the Filemaker team.

        • 1. Re: Displaying fields in portal from many-to-many relationship
          jbrown

          Good morning.

          It seems to me that you want to let the portal show records from the join table. For a contact you need to create a role. The role is then attached to a company, all in that join table.

           

          I think you do want the Allow New Records to be set for Contacts -- Role, but not from Role -- Company. You're simply choosing a company that the contact performs the role you just created.

           

          You can put any related fields into the portal, so after choosing the company (using the IDf_Company field) you will be able to see that company's address when you put those fields into the portal.

           

          EDIT: I attached a sample file. It shows the three TOs, Contacts, Contacts_Role, Contacts_Role_Company. The portal is the Contacts_Role. A user can select a role then select the company assigned to the contact for that role.

          • 2. Re: Displaying fields in portal from many-to-many relationship
            HammerOz

            You need to make sure those fields in the portal are from contact_role_COMPANY.  It so easy to drop the wrong field into a portal that is out of context. Above you state the portal fields are from the unrelated Company table occurance. So this looks like your problem. The creation of records in the join table is not the issue. Looks like you are on the right track overall.

            • 3. Re: Displaying fields in portal from many-to-many relationship
              Abingdon

              If I understand this correctly, not only do you wish to show the companies that a contact is related to, you also want to be able to select a company from a dropdown list that the contact does not yet have and add that company for that contact.  Is that correct?

              If so, I think you are trying to do two different things with the same portal, both see related company records and select a new company that you have not already added?

              Your relationship currently allows you to see the companies the contact is related to because your join table contains the contactid and the companyid.
              That relationship will only allow you to see related records, it cannot, by definition, show you unrelated ones.
              So, if a contact has 10 records in Role and those 10 records contain the CompanyID of ten companies then all you will see is those ten companies in your portal.
              Basically, 10 Role records gives you 10 Company records.

               

              You need to create a new record in Role for every Company you want a contact to be related to.
              To do this you need to be able to see the companies you have not already added to that Contact.

              If you want to work with portals this way, I think you need a second portal, one that shows those 'available' companies.
              Plus a way of adding (and deleting) companies for a specific contact.

               

              This can be achieved by the addition of a few fields and some scripts.

              I have attached a sample file that I hope will help.
              Take a look and see how the relationships, portal filtering and scripts work. You will have to adapt them for your own database of course.

               

              Best regards.
              Abingdon

              • 4. Re: Displaying fields in portal from many-to-many relationship
                phillegg

                Thanks guys all your comments have been great and most educational.

                 

                Jeremy and Abingdon, I've looked at both of your example and love them. On the weekend I'm going to try and build each example into my solutions may have additional questions on this subject early next week.

                 

                Abingdon - Not sure why you need the MyListOfIDs global parameter? Could you explain a bit as that would help when utilising your idea into my solution this weekend.

                 

                Great though - and what a fantastic forum!

                • 5. Re: Displaying fields in portal from many-to-many relationship
                  Abingdon

                  Glad the Forum can be of help, it's always helping me!

                   

                  I'm not clear on your question about MyListOfIDs global parameter.  It's not a global parameter. It's a calculated field that returns a list of Companyids that your contact is related to. And it's purpose when you look at the relationship graph is to return a list of companies that the Contact is not yet related too.

                   

                  Please do tell me if I've misunderstood your question!

                   

                  Have a good weekend!

                  Abingdon

                  • 6. Re: Displaying fields in portal from many-to-many relationship
                    phillegg

                    Hi Jeremy

                     

                    Sorry it's been a while in responding to your answer to my earlier question but had a personal matter which took me away from working on this for a few weeks. However, I've been able to have a look at things on the weekend and think you're solution is a really nice way to do it.

                     

                    However, there is one part of your solution that for some reason I cannot get to work on my solution and also one new edit that I would like to ask you how to do.

                     

                    To show you this I have attached the file/solution I'm working on to this email (Would also like to warn you that the file is generated from the Contact Starter solution and has various field's on the Companies layout tab that have many 'broken' fields. This is because I've copied it over from the Contact Details layout and am yet to assign all the fields correctly. However, the company name and address 1 fields are assigned to fields in the Company table and should therefore hopefully mirror your suggested solution:

                     

                    1) On my solution, to get the various companies to show up as a list of options in the portal when in browse mode on the Contact Details layout, I've copied your example and set that field to contact_ROLE::IDf_CompanyID (this is my field but directly relate to the corresponding field in your solution. But I'm rather confused as to why this is set to the IDf_CompanyID and yet when clicked on in browse mode, shows the "company names" as opposed to the "foreign key" which is what I would have expected. Could you give a short explanation as to why this happens?

                     

                    Anyway, the part which is not working is . . . .  When I select a company on the portal, unlike on your example. all the corresponding address fields do not show up for that company in the portal? I have carefully inspected both solutions but have not been able to work out why my solution is not working yet your one is? Would you be able to have a look at the attached file and see if you can spot why it's not working for me?

                     

                    2) There is one adjustment to the solution that you offered which I would like to include in my solution is as follows . . . .  When you select a 'Role' for each company on your portal in browse mode for the Contacts layout, how would it be possible for that role to be related to the selected relevant company for the current contact, so that when you selected / swapped it for another company the 'role' would change to whatever had been set for that newly selected company - or - if indeed no role was set, that the 'Role' field would be blank? In this way the Role would be connected to both the contact and the company? "i.e. Phil is the MD for Futureproof and if you select another company in the same portal slot, then the 'Role' would change from MD to whatever Phil's Role was for that company - I hope that makes sense"

                     

                    3) and finally one other small layout issue I'd like to ask you about is . . .  I seem to have a situation in which the 'Job title' field in my portal doesn't seem to be included in the actual portal so that when you switch to the personal address tab of the slide control in which the 2 addresses are placed in, the 'job title' field stays visible. This make me suspect that the 'Job Title' field is not actually inside the portal, but instead just placed on top. Could you explain to me why I'm getting this type of behaviour?

                     

                    Sorry to ask you a variety of questions but I'm a quick learner and the sooner I understand these concepts the less questions I'll need to ask.

                     

                    Many thanks and I look forward to hearing back from you about these issues

                     

                    Phil

                    • 7. Re: Displaying fields in portal from many-to-many relationship
                      jbrown

                      Morning. I am glad you think this could work for you. I'll respond to each question in a separate post.

                       

                      1) On my solution, to get the various companies to show up as a list of options in the portal when in browse mode on the Contact Details layout, I've copied your example and set that field to contact_ROLE::IDf_CompanyID (this is my field but directly relate to the corresponding field in your solution. But I'm rather confused as to why this is set to the IDf_CompanyID and yet when clicked on in browse mode, shows the "company names" as opposed to the "foreign key" which is what I would have expected. Could you give a short explanation as to why this happens?

                       

                      Anyway, the part which is not working is . . . .  When I select a company on the portal, unlike on your example. all the corresponding address fields do not show up for that company in the portal? I have carefully inspected both solutions but have not been able to work out why my solution is not working yet your one is? Would you be able to have a look at the attached file and see if you can spot why it's not working for me?

                      The reason the addresses are not showing up is because the IDf_CompanyID field is not being filled in with a company's ID. That's the key needed to 'grab' the address from the Contact_Role_Company table. instead, this field is being filled in with the company's name, which is NOT the key to grabbing the address fields.

                       

                      The value list used in the dropdown for the field in the portal just was pulling the company name. I've revised it in the pictures to store the ID_Company value while showing only the company name.

                      Screen Shot 2015-12-14 at 9.20.54 AM.png

                      This is the value list. I'm using two fields as the values. The details are shown below.

                      Screen Shot 2015-12-14 at 9.21.34 AM.png

                      The field I choose in the left side will be stored in the field. The field I choose in the right side will only display. I'm storing the ID_Company value in the field (left choice) and am displaying the company name (right choice). Notice I checked "Show values only from 2nd field". This will give the user only the name of the company in the value list. This is a UI need only.

                       

                      For the field, choose the pop-up control type. This is the only one that will allow you to see the name of the company AFTER you've selected it. I personally don't like this and might do something else, but this is a good start.

                       

                      A user will now be able to choose a company using the pop-up value list.

                       

                      In doing so, the user is storing the ID of the company in that Role::IDf_Company field. Since that is the key in relating to the Contacts_role_Company table, the address will then be shown.

                       

                      Note: the first company in the pop-up does not work. Can you guess why? (hint: its based on the above information. Check out the record in the company table.

                       

                      I'll get to answering #2 and #3 in a bit.

                      • 8. Re: Displaying fields in portal from many-to-many relationship
                        phillegg

                        Hi Jeremy

                         

                        Thanks very much for that. I've managed to get the basic portal working now. Apart from the first Address (which you mentioned and gave me a hint). What I have noticed is when i view the company record in table view, I see that the first address (Futureproof) has nothing in the ID _Company field? Not sure how that happened, but I can't find a way to put one in there. Obviously i could just delete the record and recreate a new one, but 1) am I right in the assumption that the problem is an empty ID_Company field or 2) is it something else that you were hinting at?

                         

                        I presume you also noticed I am now using: GET(UUID) so that users will be able to input new data without duplicating the Primary key/ID when they're out and about. Incidentally the Advance Manual suggests when using GET(UUID) that the option for Do not replace existing value of field (if any) is left unchecked? Surely if the UUID is doubled and needs to be regenerated, does the system understand that any foreign keys linked to that UUID need to be updated too?

                         

                        Otherwise, would appreciate if you could look into my 2 other questions when you get a moment.

                         

                        And thank you very much. You're a great teacher - very clear in your explanations

                         

                        Phil

                        • 9. Re: Displaying fields in portal from many-to-many relationship
                          jbrown

                          2) There is one adjustment to the solution that you offered which I would like to include in my solution is as follows . . . .  When you select a 'Role' for each company on your portal in browse mode for the Contacts layout, how would it be possible for that role to be related to the selected relevant company for the current contact, so that when you selected / swapped it for another company the 'role' would change to whatever had been set for that newly selected company - or - if indeed no role was set, that the 'Role' field would be blank? In this way the Role would be connected to both the contact and the company? "i.e. Phil is the MD for Futureproof and if you select another company in the same portal slot, then the 'Role' would change from MD to whatever Phil's Role was for that company - I hope that makes sense"

                          Hi. Here's my thoughts for #2:

                          1. Why would you ever want to swtich out companies for a particular contact? I guess if I needed to switch out the companies, I'd just create a new portal row and delete a portal row. This is the easiest method and doesn't require extra relationships and such.

                           

                          I guess I'd, if I have to do it this way, I'd create another table called Role_Company. In this table, I'd have a field for the role and a field for the ID of the company. Then I'd set a script trigger on the Role dropdown to create a record in this table when the user has selected a role and a company. I'd put this on the Role field and check to see if the company and role fields are filled in before creating the record. Finally, I'd put a script trigger on the company field that grabs the role from the Role_Company table when the user has changed the company.

                           

                          The problem that you'll come against, unless your use cases account for this, is that one company could have many roles. This scenario would not know which role you wanted if you choose a company.

                          • 10. Re: Displaying fields in portal from many-to-many relationship
                            jbrown

                            3) and finally one other small layout issue I'd like to ask you about is . . .  I seem to have a situation in which the 'Job title' field in my portal doesn't seem to be included in the actual portal so that when you switch to the personal address tab of the slide control in which the 2 addresses are placed in, the 'job title' field stays visible. This make me suspect that the 'Job Title' field is not actually inside the portal, but instead just placed on top. Could you explain to me why I'm getting this type of behaviour?

                            Objects like portals, slide panels, and tab panels (the latter two are basically the same thing) need the objects completely contained. Sometimes the objects get off when you select an individual object and move it just off the object. Then it becomes an object on the layout, not in the portal/slide plane/tab panel.

                             

                            Simply move the "Job Title" text completely off the portal. Then put it back on the portal, making sure the text field is completely in the portal. (Use the position coordinates in the inspector) Move the portal around to make sure that the text object is in there completely.

                            • 11. Re: Displaying fields in portal from many-to-many relationship
                              phillegg

                              Hi Jeremy

                               

                              Thanks for your reply to points 2 and 3

                               

                              Regarding point 2 - Wanting the job title field in ROLE table linked to the Contact and the Company

                              In response to your question "Why would you ever want to switch out companies for a particular contact?" This is a database of journalists who are often moving companies, writing for a publication as a freelancer and then joining the team as a reviews editor, then leaving and coming back as a features editor and so one. So that's why I'm looking to link the job title to the company.

                               

                              Also, knowing how these things work in practice. I know that if the job title and company are not linked, it's only a matter of time before someone will change the company when the journalist leaves but forget to change the job title, hence compromising the data integrity of the database. I was hoping that to set up this type of relationship would be quite simple, but reading your response I'm now thinking that may not be the case.

                               

                              At a recent Filemaker experience day in Reading (UK) I actually asked this question and was told that it wouldn't be a problem, making me wonder whether I have explained the issue to you clearly. Basically, to reiterate what I'm looking for: a unique "contact" will only have one "job title" with a unique "company" so I need a way of having a "job title" field that relates to one "contact" AND one "company". Is there a way of implementing this simply?

                               

                              Regarding point 3 - The "job title" field not being linked to the portal that it's placed inside.

                              Your idea of moving the field outside the portal, and then placing it back in the portal again worked fine. Many thanks

                               

                              Would appreciate any further thought you have on point 2 though

                               

                              Many thanks

                               

                              Phil