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)
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)
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.