Define your Notes table with at least these fields:
Use your Notes table as a Join table between your contacts table and your property table. This will require at least one new table occurrence--likely a new one of Property, to avoid a circular relationship.
I also see a NotesID field in your LineItems table, you can also use the same table to record notes pertinent to a given line item by linking a serial number field defined in LineItems to a separate, matching field you'd add to the notes table. This would require an additional table occurrence of Notes for linking to LineItems.
(Table Occurrences are what we call the "boxes" in the Relationship graph such as what you've uploaded in your first post here.)
PhilModJunk, Thanks for the response, its helpful to think of this in terms of adding another join table.
I must be doing something wrong (or omitting a req'd step?), because although I've added new "Notes" TOs and then added "Property" TOs tying in to each of the new "Notes" TOs, I cant seem to get it to work.
This is what happens:
1. I enter "Mr. Seller X" in field "Deal::SellerContact_ID"
2. I enter "Property X" in field "Line Items::Property ID"
3. Then I enter some text notes into Notes TO at "cSeller_Notes::text" for Mr. Seller X, regarding Property X. The problem is, these notes appear in every "Deal" where Mr. Seller X is a seller, not just the "deals" where Mr. Seller X is a seller of Property X.
What am I not seeing here? Help is overwhelmingly appreciated. (I updated the screenshot on my initial post to show how I've tried to implement PhilModJunk's answer.)
From what layout have you added a portal to Notes_Property? (Which isn't set up as a join table, BTW).
If the layout is based on Deal, then I would expect to see all notes for that Deal record which, going by your graph, could represent multiple properties.
If the layout is base on a Contact TO (I'm assuming all Blue TO's refer to the same data source table. If not, they should.), You'd see all Notes for that Contact, regardless of the property specified.
A Join table looks like this:
Property----<Property_Contacts>----Contacts (Notes would be a text field defined in Property_Contacts
A Portal to Property_Contacts placed on a Property layout would display all notes for that property, from all possible contacts. A Portal to Property_Contacts placed on the Contacts layout will show all notes for the current Contact for all properties.
I'm not sure that's what you want here. Seems like you want all notes for a given contact, for a given property. Since contact and property are records in separate tables, you'd need to set up a portal to Notes that filters by one of the two records. A portal to notes on a Contact layout could filter by Property and a portal to notes on a Property record could filter by Contact.
Yes! That is what I want: "all notes for a given contact, for a given property." I'd like that info via portal to appear on my Deal layout (and yes, all blue TO's refer to the same data source table; Grey=Property table TO; Orange=Notes table TO). I will try your suggestion to filter (on the Deal layout?) the portal results.
Im confused by why my Notes/orange TOs on the left side of the Relationship Graph are not set up as a join table? I have it set up like this:
Property(grey)----<Notes(orange)>----Contacts(blue). I have a text field defined in Notes where I would enter in text notes. What am I missing here? Do i even need this join table if the problem is solved via filtering portal results?
Im thinking I do need that join table anyway - without it there would be nothing to filter?!
So, if that's the case, can you please provide clarification on where my attempted join table has gone wrong?
I just noticed that the screenshot in my inital post hadn't updated with the revised screenshot. I've uploaded it. Not why the last one didn't take, but perhaps PhilModJunk and I were referring to different versions of the screenie.
Looking closer, I see your image is too large and the second related TO is clipped only a small dot down the right hand edge of the TO border tells me that there's a related TO there. So scratch that comment on it not being a join table.
Ok great. And thank you, very much, for you help!