Showing Related Field Data on a Different Table Layout

[Karl Dickhaus]

I have a three table database in FMP 11 with the following tables: Contacts, Projects and Organizations. I have a layout that shows information on a particular project, based on the Projects table. Each project is associated with a client organization and another unrelated contact from the Contacts table - the salesperson. There is a drop-down menu on the Projects Layout for the contact person from the organization assigned to the project. This drop down list grabs information from the contacts table (a field for the contacts full name) which is sorted by the organization. For example, if Organization A has three project managers Bill Smith, Betty White and George Jones, when the organization is selected, clicking on the drop down field for Project Manager will list only those three choices from the contacts database. This is done using a table occurrence called Contacts_Organizations with a relationship based on the Organization ID field from the Organizations table. All of this works fine.

 

What I want to do is when I click on the project manager (for example George Jones) I want the Project Manager e-mail field to automatically display the project managers e-mail, which is entered on his record in the contacts database. I want that change reflected only on that particular Project Record, not on all the project records linked to that organization. This seems like a simple enough job, but apparently I have been looking at the problem too long, and can't seem to get it to work. 

 

The relationships work like this: Contacts is linked to Projects by a join table. Contact ID from the contacts table is linked to Contact ID in the Join Table, Project ID in the Join Table is linked to Project ID in the Projects Table. The Join Table records are created by a script when the salesman is assigned to the project. The Project Table is linked to the Organization table by Project ID. In turn the Contacts_Organization Table Occurrence is linked to the Organization Table by a relationship based on Organization ID.

 

I know its a bit simple, but all help would be greatly appreciated. If you need to see the table occurrences, I am happy to post them.

 

TIA,

 

Karl.

 

[Peter Fenner]

Hi Karl

 

If I understand correctly I would do the following:

 

First off, I recommend naming your relationships as follows:

You have an Organization Table and a Contact Table (Don't pluralize the table name). The relationship between the two should be named as: Organization to Contact. All other relationships would be named in a similar format. So, for instance, you would also have Project to Organization - which will pull in the Organization info to the Project. Only use these relationships one way (even though they work both ways). Create another relationship called Organization to Project - then a portal on the Organization layout will show all the relevant Projects for that Organization.

 

Any extensions of these relationships would keep the full name of the previous relationship - so now you want to get Contacts of an Organization to the Project so you create a relationship called Project to Organization to Contact (This is where the value list of contacts is created). So the relationships look like this:

 

Project -------- Project to Organization ----------- Project to Organization to Contact

 

Create a field in Project called "ID_contact_email_manager" and another field called "email_manager"

 

Create another relationship as such: Project to Contact_ChosenManager. Match the "ID_contact_email_manager" in Project to "ID_contact" (key) in Contact

 

Create a value list based on the Project to Organization to Contact relationship ( relationship starting from Project ). Value in first field will be "ID_contact" and value in second field will be the contacts name. Show values only from second field.

 

Place the "ID_contact_email_manager" field on your project layout. Use a popup menu with the above value list.

 

Now when you click on your email button for Project Manager ( George Jones ) you run a script as follows: Set Field [ Project::email_manager ; Project to Contact_ChosenManager::email_contact ]. This will fill the field with the relevant email address - but just on that project record. You could also get the "email_manager" field to auto-enter a calculated value of the email from the Project to Contact_ChosenManager relationship - so as soon as you select Project Manager the field is populated. If the auto calculation is set to allow replacement of values then changing the Project Manager will change the address.

 

Hope this helps.

 

Peter

[Karl Dickhaus]

Perhaps I am getting confused regarding the naming of the relationships. Is what you are saying that I should create table occurrences for each of the proposed relationships? For example, if there is a relationship Organization to Project, which links the Org_ID field in the Project table to the Org_ID (Key) Field in the Organization Table (using the = relationship) and there is a Contact to Organization relationship, which links the Org_ID field in the Contact to the Org_ID (Key) field in the Organization Table, then wouldn't you need a new table occurrence to create the Project to Project to Organization to Project to Organization to Contact relationship?