Stuck trying to relate Notes, Opportunities and Accounts
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!