4 Replies Latest reply on Sep 27, 2010 11:44 AM by dicksmith

    Relationships in FMP7



      Relationships in FMP7


      I think I'm over my head in relationships. I have an organization with Members, and Items Bought by those members. Many members can buy many items, so I relate those tables with a Members and Items Join Table. But how can I handle the Number of each Item bought by a Member, as well as the Price paid per each of those items?  I want to display, for each member, the number of Items bought and the price for each item, and also display, for each Item,  which members are purchasers  and the price and quantity ordered by each member.  Can anyone help?  I'm a pretty raw newbie.  Thanks.

        • 1. Re: Relationships in FMP7

          Members can change address, members can leave (deleted)

          Items are sold at today's price, price can change?

          What else are the underlying design.

          • 2. Re: Relationships in FMP7

            There is no more buying going on.  This is the conversion of multiple historical spreadsheets of records into a single FMP file (I hope). There's a table with all member addresses and contact info. These can (and do) change.  There's a collection of about 100 money-making projects  in which members bought interests in the form of shares, at certain times in the past. Thus Mr. Jones now owns 5 shares of Project1 and 7 shares of Project15.  Mr. Brown owns 8 share of Project15 and 6 shares of Project23. The share prices were not constant in time, so that Jones may have paid a different price/share of Project15 than did Brown.

            Now the Projects also make money, which was distributed to share owners.  I know the amount received from each Project owned by each Member. Each project owned by each member shows a profit or a loss, calculated by comparing money in vs money back for each project.

            Phew. Just describing this situation has me exhausted.  Really illustrates the importance of asking the right question. Now I'm thinking there's no way I can do what I want in FMP, with my current state of knowledge.  Maybe there's a template somewhere which is close enough so that I could modify it slightly.

            • 3. Re: Relationships in FMP7

              Check out the Invoices starting point solution for ideas. It doesn't manage inventory very well, but it does demonstrate how to set up an invoicing system in FileMaker--which is what you have described here.

              Here's an even simpler Demo file created by Comment that you may find is a useful "primer" in how to set up such a system:  


              • 4. Re: Relationships in FMP7

                An Invoice System!!??  Is that what I've got?  Hmmm, let me digest this for a little bit.  Thanks very much for the suggestion!  And the Demo file you referenced is beautiful in its siimplicity.  Now to get my mind wrapped around it.