10 Replies Latest reply on Jun 24, 2009 1:48 PM by spectre6000

    Very new to all of this



      Very new to all of this


      I'm using FileMaker Pro 10. I'm new to FileMaker and databasing as a whole. I'm thoroughly stumped.


      I'm trying to create a relationship between two tables: Investors and Assets. Each investor owns a portion of many assets, and each asset is owned by many investors (not all investors own all assets). I need to be able to tie a number of investors to a number of assets by varying percentages that are more or less unique to each asset/investor


      i.e. Asset 1 is owned by 25% Investor A, 10% Investor B, and 65% Investor C, Asset 2 is owned by 10% Investor A, 35% Investor D, and 55% Investor E, and so on.

      i.e. Investor X owns $$$ worth of assets (A% of asset 1 + B% of asset 2 and so on.


      I've searched here, read the Missing Manual, called support, and beat my head against the keyboard. None of this has helped.


      A final caveat, I don't know the jargon yet.


      Any and all help is very much appreciated. Thank you.

        • 1. Re: Very new to all of this

          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.

          • 2. Re: Very new to all of this
               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?
            • 3. Re: Very new to all of this

              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?



              • 4. Re: Very new to all of this
                   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!
                • 5. Re: Very new to all of this
                     Is there any way to make the same information show up in a merged-type report?
                  • 6. Re: Very new to all of this

                    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.

                    • 7. Re: Very new to all of this

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

                      • 8. Re: Very new to all of this
                           Or maybe I'm trying to merge the portal?
                        • 9. Re: Very new to all of this

                          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.

                          • 10. Re: Very new to all of this
                               I must have been doing something wrong... Got it. I think... Thanks again.