8 Replies Latest reply on Dec 23, 2010 10:03 AM by KenKing

    table of "Notes" relating to "Contact" and "Product"

    KenKing

      Title

      table of "Notes" relating to "Contact" and "Product"

      Post

      Hello folks, 

      Some background:  I'd like my db to track Property, Sellers, Buyers, Deals (which brings Buyers/Sellers/Properties together, akin to an invoice).  Each Property has related Notes, and each Contact has related Notes.  

      But I'd like to have Notes for a Contact (Buyer/Seller/whatever) that relates to a Property; the goal here being that if Mr. Sammy Seller tells me he'd sell the Empire State Buliding for, I can enter notes.  It wouldn't be appropriate to simply enter notes in Contacts::Notes (bc later I may look at those Contacts notes and not have any idea about which Property the notes are referencing).  Nor is it appropriate to enter notes into Property::Notes (bc sammy may not always own the Empire State Building).  

      This is my first attempt at a DB.  I've been doing trial and error, trying to get this to work.  See screenie for my current ER schema.

      Any suggestions?

      screenshot.png

        • 1. Re: table of "Notes" relating to "Contact" and "Product"
          philmodjunk

          Define your Notes table with at least these fields:

          ContactID
          PropertyID
          Note

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

          • 2. Re: table of "Notes" relating to "Contact" and "Product"
            KenKing

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

            - ken

            • 3. Re: table of "Notes" relating to "Contact" and "Product"
              philmodjunk

              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.

              • 4. Re: table of "Notes" relating to "Contact" and "Product"
                KenKing

                PhilModJunk - 

                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?

                Thanks!

                ken

                • 5. Re: table of "Notes" relating to "Contact" and "Product"
                  KenKing

                  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? 

                  Thanks again!

                  • 6. Re: table of "Notes" relating to "Contact" and "Product"
                    KenKing

                    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.  

                    ken

                    • 7. Re: table of "Notes" relating to "Contact" and "Product"
                      philmodjunk

                      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.

                      • 8. Re: table of "Notes" relating to "Contact" and "Product"
                        KenKing

                        PhilModJunk - 

                        Ok great.  And thank you, very much, for you help!