You have a classic many to many relationship problem here. For each Investor record, you have many assets and for each asset record you have many investors. THe solution is to create a third table, we'll call it Investor_Asset and relate the other two tables to it.
You should be able to set up something like this:
Each time you want to assign an asset to an investor, you create a new record in Investor_Asset and store the Investor's Id and the Asset's Id in it.
I'd add a third field to Investor_Asset, to record the percent ownership.
I've already created a join table (as per MM), I guess I just don't know how to use it... How do I make it so that I can enter a new record (asset) and relate it from that layout to the investor? Will have I to enter information in both tables for every new asset?
Good questions. Here's some examples of how to implement this:
You'll need to think of the following tasks as separate actions:
Adding a new investor = New record in investor table
Adding a new asset = New record in asset table.
Adding a new investment = new record in Investor_Asset linking an asset record to an investor record.
Let's say you want to assign a new asset to an investor's portfolio:
Make sure that the Allow Creation of records via this relationships options for Assets is enabled in the link between Assets and Investor_Asset.
Create a layout that refers to Assets. Place a portal on the layout that refers to Investor_Asset. Place Investor_Asset::InvestorID and Investor_Asset:: PctOwn fields in the portal. Add other fields from the Investor Field so that you can see what data you need on each investor that is assigned to this asset record.
Now enter browse mode, create a new record and log the data about your new asset. Now, when you enter an InvestorID value in a blank row of this portal, a new Investor_Asset record is created linking an Investor to an Asset. (You can use a 2 column value list on your InvestorID field to make selecting an investor easier.)
You can use the same technique on an investor layout to add a new investor and assign them to asset records.
That make sense?
I'm fairly certain that was it! I haven't tried to create any reports or run any calculations on that, but it seems to have worked! Awesome! Thank you!
Is there any way to make the same information show up in a merged-type report?
I'm guessing here as to what you want, but I think you want a summary report.
You can create a layout based on your join table and put fields from the Asset and Investor tables as needed in the body, header and sub-summary parts.
That can give you a report such as:
Investor Name: John Smith
Asset 1, dollar amount, pct ownership
Asset 2, dollar amount, pct ownerwhip
Asset 3, etc.
Total investment for John Smith:
You can also set one up for assets
Asset 1: Lot 5-A at.....
Investor John Smith, pct ownership, dollar amount
Investor Bob Jones, pct ownership, dollar amount
Total Investment for Asset 1:
and so on.
I want to do a summary report for each asset that shows:
Investor 1 Name Address Etc. XX%
Investor 2 Name Address Etc. YY%
and so on.
When I put a join table in the report as per above, it only shows the first investor...
Or maybe I'm trying to merge the portal?
I'm not one hundred percent sure what XX% is a percent of. Is it percent ownership of a given asset or something else?
If percent ownership of a given asset, then the second example I mention will work for you.
Base the layout on the Join table.
Make either the header or a sub-summary part set to "print above" the location for your assets fields.
Put the Investor fields in the body along with the Pct field from the Join table
Header vs. Sub-Summary Part for the Asset fields:
If you only want to show investor data for a given asset, put the fields in the header and use a find to limit your found set to only those records for a specified asset.
If you want to show investor data for multiple assets, put the fields in a sub-summary part with the Asset Id field as it's "Sort by" field and sort your found set by the same field.
I must have been doing something wrong... Got it. I think... Thanks again.