5 Replies Latest reply on Dec 9, 2014 6:03 AM by LeeMoreau

    Stuck trying to relate Notes, Opportunities and Accounts



      Stuck trying to relate Notes, Opportunities and Accounts


      Hi everyone,

      I'm getting pretty good at FM13 so far but I'm stuck on making a notes module work.  It seems really straightforward but I'm getting weird info.  I have an Accounts module with customer account info, its Primary Key is "Account ID" which is a serial number.  Then I have Opportunities which has opp info, with its Primary Key "Opportunity ID" which is a serial number and then Foreign Key "Account ID" so that 1 account can have many opportunities.  In my Account layout I have a Portal which shows related Opportunities and that all works fine.

      The problem I'm having is with my Notes module I made to log calls, notes etc.  To do this I made another Table called "Notes" which has Primary Key of "Note ID" plus has "Parent ID" as a foreign key.

      What I'm trying to do is basically use this one Notes table to log notes from Accounts and Opportunities or other future modules.  

      The Relationships I have right now is Opportunity ID from the Opportunity table relating to Parent ID in the Notes table.  With this, if I create a note from a Portal or from a script pop out from an opportunity, it saves the Opportunity ID into the "Parent ID" field on the note, and saves the note.  They all show up as expected.  The only thing is when I add the "Notes" portal to Account, it shows all related opps notes as well, but that was easy to fix with a filter.

      The problem is now trying to get Accounts to work.  If I try to relate Parent ID on Notes to Account ID on Accounts, it says "There cannot be more than one relational path between any two tables in the graph." and then it wants me to make another occurrence of Accounts called Accounts 2.  I did this, and then I related Account ID from the Account table to Parent ID on the Notes table.

      There's 2 things that happen depending on how I make the Note.  If I go to the Note layout and manually make it and enter the Account ID directly into the note, it never appears under the Notes portal under accounts.

      If I create a note FROM the portal, then it is even weirder in that it makes the Parent ID of the Note some related Opportunity ID, and not the Account ID.

      I was going to make a separate module for Account notes but just confused why this wouldn't work especially when I have Accounts and Notes related by Account ID and Parent ID and am manually making a note entering the proper Account ID under Parent ID on the note.  Thanks so much!

        • 1. Re: Stuck trying to relate Notes, Opportunities and Accounts

          Sharing your current Data Model via either a written description or a screen shot from Manage | Database | Relationships would help us to help you.

          When you add the additional occurrence of notes, odds are that you then need to specify that additional occurrence in Show Related Records From in Portal Set up for your portal.

          You also need to be careful of your Primary key values and how they match to records in your Notes table. With the same notes table and foreign key linked to two different primary keys in two different relationships, you can get a record showing up in your notes portal that should not show up because an account record and an opportunity record can both have the same primary key value. One way to avoid this is to use Get ( UUID ) to generate your primary keys in text fields rather than auto-entered serial numbers in number fields.

          • 2. Re: Stuck trying to relate Notes, Opportunities and Accounts

            Great thanks, I didn't realize about the UUID so changed that, that will be good instead of having the primary key be like 1000, 1001 etc.  It's still not working though even when I made a separate foreign key for accounts and opportunities, but it still does the same thing.  If I make a note either from 1) A Note portal under Opportunity, 2) A Script creating the note from Opportunity in a new window, or 3) From the Note module directly, entering the opportunity ID as the Parent_ID_Opportunities field, the note shows up under that opportunity as expected, but also under the Notes portal under Accounts.  Only that opportunities notes are shown to itself, and accounts show the notes from all related opportunities.

            If I do the same from Accounts, 1) Creating a note from the Notes portal under Accounts, creates a note but "Parent_ID_Accounts" is never populated with the account number, but Parent_ID_Opportunities IS populated, with the Opportunity ID of the first related Opportunity, so it still creates it against the Opportunity.  2 and 3) using a script or manually creating a note and then entering the Account ID into the Parent_ID_Accounts field on the Note, also doesn't make it show up under the Account, it just doesn't appear.  

            So the only way I can get any notes to show up under Accounts is if they are under a related opportunity, otherwise I cannot for the life of me figure it out.  Attached is screenshot as requested, note that my database is still in development with a lot of fields coming right from Salesforce which is why there might be redundant or some fields that don't seem to make sense etc.  Thanks so much!!!

            • 3. Re: Stuck trying to relate Notes, Opportunities and Accounts

              Why haven't you linked an occurrence of Notes directly to Accounts? That's how I would set this up.

              • 4. Re: Stuck trying to relate Notes, Opportunities and Accounts

                I'm confused though because that's what I thought I had tried to do?  When I drag Parent_ID_Accounts being the foreign key from the Notes table in the relationship window above, and drag it to primary key "Id" on Account, that's where it pops up that window saying ""There cannot be more than one relational path between any two tables in the graph." and forces me to make an Accounts 2 window there, which I renamed to Accounts for Notes.  Is that not the same thing?

                • 5. Re: Stuck trying to relate Notes, Opportunities and Accounts

                  I think I actually got it working.  Not sure if this is what you meant, but first I deleted the way I had it above, with one Notes occurreence mapped to Opportunities and Accounts, and instead made a second new occurrence of Notes and just mapped it to Accounts.  I noticed that didn't solve the problem even though I had the Notes portal set to show the records from the new Notes occurrence.  Finally I decided to delete the portal and make a new one (since I think I had copied and pasted that portal from Opportunities just to keep the formatting etc) and when I did that it immediately worked.  No idea why it wouldn't before but I have 2 portals open, configured to show info from the same table, yet the original one shows old notes from opportunities but the new one works as expected.  Thank you!