3 Replies Latest reply on Oct 15, 2008 7:45 AM by mary7800

    Relationship Trouble

    mary7800

      Title

      Relationship Trouble

      Post

      I have a I'm attempting to make a Database to record product sales. so far my relationships look as follows: Products-=Product Price Records=-Distributors.

       

      I want to add a Sales table but I can't figure out which other table to relate it to. the products are sold at different prices depending on the distributor- so do I relate sales to the Product Price Record, or the Distributor- because it's really important that the sale is recorded at the correct price by the correct distributor.

       

      I want to be able to see not only what sales each of my distributors has made, but also what total sales for each Product. I've spent many hours puzzeling over what I thought should be a simple database.

      Please Help!

        • 1. Re: Relationship Trouble
          TSGal

          mary7800:

           

          Thank you for your post.

           

          Your relationships sound fine.

           

          Let me run through the following scenario just to make sure I understand the problem.

           

          In your Sales table, you want to keep track of all sales.  I'm assuming that you may sell more than one item per sales transaction.  Therefore, I would have a link to the Product table to get the information about the product.  However, there may be more than one distributor that provides a product (and a price), so perhaps the link is to the Product Price table, which links to the Product Table.  Or, can certain customers only purchase from specific distributors?  That is the decision I need to know.

           

          Since the Product Price Records links to the Distributors, you would be able to view all sales from a Distributor.

           

          My gut feeling is that you would link the Sales table to the Product Price table.  From the Product Price table, you can search for a specific Distributor, and then total the sales in the Sales table.

           

          What have you tried?  And what results are displaying?  What results are you expecting to display?

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Relationship Trouble
            mary7800
              

            I've tried linking the sales to the product price record, but I ran into a problem with getting the correct ID number selected as the "child key" I made a value list of the Serial ID number associated with each Product Price Record Entry, but I can only show one related field along with the ID number, So I end up Selecting Just the Product Name, or Just the Distrubtor name, but since there are multible records for either and only the combination of both is unique, I don't get the correct price.

             

            I also attmpted to create a calculated field containing both the Product Name, and the Distributor for each product price Entry, but besides being unweildy, it cannot be used in a value list, because it can't be indexed- therefore the only option is to make it an auto-enter field, which is even more clumsy- this also makes the value list very difficult to read. 

             

            I'm left looking for a way to select the distributor and the product name from seperate (hopefully filtered) value lists and somehow then end up with the correct Product Price ID. in which i've been so far very unsucessful. 

             

            It doesn't need to be over complicated as we don't need to track the customers (our distributors don't provide that information) and the sales can be entered individually for each product if need be.

             

            Thank you for your Help!

            • 3. Re: Relationship Trouble
              mary7800
                

              By JOVE I think I've got it! I ended up linking the sales to the product price record, and then making duplicates of the distirbutor, product price record, and Product tables. then I linked the sales table to the duplicate distibutor table and I linked the remaining two duplicate tables mirroring the orginal setup to in the end it looked like this: 

                                   Sales=-Distributors 2-=Price Record 2=-Products 2

                                      Y

              Products-=Price Record=-Distributors

               

              this allowed me to make a value list of the PriceRecordID.pk from the price record 2 table and then filter it by the Distributor 2 table. since Price Record and Price Record 2 have the same ID numbers, the value list from the Duplicate table populates the child key that links the original Price Record table to the Sales Table!

               

              Maybe I over-complicated things, but It does seem to work spledidly! let me know if there is a better way!