4 Replies Latest reply on Jan 24, 2014 2:44 PM by keywords

    Join table trouble


      Im still new to filemaker and have been trying to do some training on relational databases. I thought I understood this but still struggling. Based on what im trying to do which I will outline below, I am hoping for some more feedback.



      • I have an inventory table which keeps track of items, quantity, and price.
      • I have a sales table which I want to be able to keep track of sales that were made.
      • I have a join table to link these two table.



      • If i sell an item i would like it to update the inventory count.
      • When viewing sales I would like to use a portal to select items that were sold, and update the inventory


      I have attached a screen shot of the relationships. This doesnt seem to work for me. I cant get a portal to work on my sales layout. It appears that the pk_ID_Sales in my sales table is creating a many to many relationship to the pk_ID_sales in my Sold_Items_Join table. when linking the inventory table to the join table it creates a one to many but the sales to join table creates a many to many. I dont understand why.


      Thanks for any help!




        • 1. Re: Join table trouble

          your join table primary key is relating to another primary key (sales), this is incorrect for a many-to-many relationship. See how the forks on each end of the line show many to many between those two tables?


          If I were you, I'd recommend renaming your join table to "transactions" to make it easier.


          Your portal would need to be "transactions", you can display "inventory" fields via a transactions table, but when you create new "transactions" from sales, they need to be in the join table.


          Hope this helps a bit.

          • 2. Re: Join table trouble

            Thanks for the reply, 


            Im still not sure that makes sense.  The many to many relationship is showing a PK to PK in name only.  I changed the name of Transactions::pk_ID_Sales to Transactions::fk_ID_Sales.  I just created another field that woudl act as the PK for the transactions table.  is there a setting somewhere that forces a field to be the PK?  the relationships and settings appear to be identical except one side (sales to transactions) creates a many to many. 




            • 3. Re: Join table trouble

              Okay I figured out part of my problem.  For the pk of Sales table I needed to check the box to "prohibit the modification of value during data entry"  that fixed the one to many relationship issue. 


              Next question is now how do I create a portal that will allow me to use a dropdown that will allow me to choose an inventory item when on the sales layout?



              • 4. Re: Join table trouble

                Do the following:


                1.     Your Sales–Transactions relationship needs to be set to allow creation of related records

                2.     Create the portal on the sales layout to show records from the Sales–Transactions relationship


                These two steps combined will allow creation of transaction records within the portal that are related to the Sales record you have open. The portal will show a blank row to facilitate this. Next:


                3.     Create a value list showing Inventory IDs, and also whatever inventory description best suits your need (you could also set the VL to only show the second field)

                4.     Set the fk_ID_Record field in the portal to display this list. When you enter this field this list will display, and when a selection is made the appropriate ID will be entered into a new portal record


                Make sense?