You need three tables for this:
Customers---<Contacts-----<Notes (---< means one to many)
Customers::__pk_CustomerID = Contacts::_fk_CustomerID
Contacts::__pk_ContactID = Notes::_fk_ContactID
This is your basic set of relationships, and allows you to put a portal to notes on your contacts layout, but to get the portal on your tab in customers, you'll need to make a new occurrence of notes and link it directly to customers. Then a trigger controlled script can update a field in Customers to link to different sets of records in Notes to produce a portal of notes "synched" to your contacts portal.
In Manage | Database | relationships, make a new table occurrence of Notes by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be ContactNotes.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
Customers::gSelectedContactID = Notes::_fk_ContactID
Use the OnObjectEnter trigger for the contacts portal to perform this script:
Set Field [Customers::gSelectedContactID ; Contacts::__pk_ContactID]
Thanks Phil. I believe I did as you suggested:
Created a Notes table and related back to Contacts.
Created an occurance of the Notes table and named it ContactNotes
Created a global field in Customers table and related it to the ContactNotes table: Customers::gSelectedContactID = Notes::_fk_ContactID
Dropped a Notes text field (from the table instance ContactNotes) into the tab control (2nd tab called Contact Notes)
Tied the script to the 2nd tab on the tab control: Set Field [Customers::gSelectedContactID ; Contacts::__pk_ContactID]
Result: I can type in the Notes text field on the second tab but when I attempt to leave the tab I get an error "This field cannot be modified until "_gSelectedContactID" is given a value."
I must have missed something here?
I assumed you'd use a portal to Notes on the second tab. The purpose of a separate table is so you can record multiple notes about the same contact and a portal would be part of the set up you'll need for that. A simpler structure is possible if you choose to limit your system to one note for every contact.
For your existing records, does __pk_ContactID have a value? This field will not recieve a value automatically except for each new record you create in Contacts. If you have records in contacts that existed before you created this field, you'll need to give them values. An easy way to update them is to use the Replace Field Contents tool to give all your existing records serial numbers in this field.
I should also let you know that I wish to create new notes for the contact in the 2nd tab (Contact Notes) of the tab control as this is the only place where contact notes can be added or viewed. There is no main layout to edit contacts. Contacts are edited via the portal in the Customer Edit layout.
To recap: The main layout is Customer Edit.
Corresponding contacts (can be one or more) related to the customer are located in a portal in the Customer Edit layout. The portal is located in a tab control with (2) tabs - Contacts on the first tab and Contact Notes on the 2nd.
When a specific contact is selected in the portal contained in the Contacts tab (1st tab), I want to open the Contact Notes tab (2nd tab) and create a new note and or review existing notes.
Existing tables are Customers, Contacts, ContactNotes. A new table occurance of the table ContactNotes now exists and a global text field called gSelectedContactID resides in the Customer table.
Just want to ensure that I am representing the situation clearly. Thanks in advance for assistance on this.
I will change the text field to a portal on the 2nd tab.
...For your existing records, does __pk_ContactID have a value? Yes - all of the contacts have a pkContactID.
No problem there as long as you enable the "allow creation of records via this relationship" option for ContactNotes in this relationship.
What I presented first in my initial post (Customers---<Contacts-----<Notes (---< means one to many) ), showed the "conceptual relationships" typical of your setup. It's your choice whether or not to create and use a different layout based on contacts for working with notes. The relationship linking Contacts to Notes will not be needed then, but you might want to keep it in place just in case you need it in the future. (One other thing you can do with such a relationship is set up a layout based on Notes for printing them out with data from Contacts and Customers included on the layout.)
I have gone with your suggested concept relationships. Added the portal to the second tab for Contact Notes and check "allow creation of records via this relationship". Still same result after typing in the notes : "This field cannot be modified until "_gSelectedContactID" is given a value."
That indicates that the field is not getting a value from the pk field.
Let's try a different trigger. Set up the portal with the OnObjectExit trigger and see if that makes a difference here. There may be cases where you create a new record in contacts and then there will be no value to put in the global field at the time you enter the portal, but it will be there by the time you exit it.
Also, while debugging your design here, put the field on your notes tab so that you can see whether or not it is getting a value.
No luck. Same result. As you said the field is not getting the pk.
I ran a quick test. OnObjectExit should be capturing the ID number--at least it worked for me.
Let's check a few details. Compare what you did to what I did and see if you see any differences.
Enter layout mode
Right click the lower part of the portal to be sure that I am selecting the portal and not a field in the portal
Choose script triggers
Specify the script that sets the field with the contact ID from the contact portal's contact table occurrence.
When I click into any field in the portal and then click outside the portal, I see the ID number appear in the field.
Previously I had the script trigger right on the 2nd tab where the notes portal is located. I removed it and placed it right in the portal. Now when I type a new note and then click outside of the portal nothing remains. I also have a ContactID field in the portal and it remains blank. May my 2nd. ContactNotes table occurance relationship is wrong?
Well...have to sign off for the night. I will come back to fight it in the morning. Thanks!
This trigger needs to be on the Contacts portal rather than the Notes portal.
Thanks Phil. I got it working after specifying on the 2nd tab - the notes field from the Notes table occurance vs the original ContactNotes table.
There is some strange behaviour going on with some records but I will work on that to see were the problem is. I reallyappreciate your help on this!