2 Replies Latest reply on Jan 26, 2011 3:31 AM by traart

    Many to many relationship choice !

    traart

      Title

      Many to many relationship choice !

      Post

      Hi everyone

      I am new in filemaker.

      My problem is to create join table.

      I have a 

      firm table (connected to) product table (connected to) join table (connected to) receiving orders table (which i use with portals)

      And i want to create another table which calls "giving orders table" to connect same firm table and product table. But i could not decide to use same join table or not. I am little bit confusing for reporting, maybe reports could not work properly when i used same join table for two separate tables. Because of too many records in it. 

      Whats your advice ?

      Sorry for my English 

        • 1. Re: Many to many relationship choice !
          philmodjunk

          firm table (connected to) product table (connected to) join table (connected to) receiving orders table

          Firm----<Product----<JoinTable>---OrdersReceived

          And i want to create another table which calls "giving orders table"

          Firm----<Product----<JoinTable>---OrdersGiven

           But i could not decide to use same join table or not

          I'd use the same table (called LineItems in many such systems) for both, but use different fields in that table for the quantity given and the quantity received. That would allow me to add these two fields for reporting purposes:

          cBal:  QtyReceived - QtyGiven
          TotalOnHand:  Summary, Running Total of cBal with Restart Summary for each sorted Group with LineItems::ProductID as the "group field".

          This would allow you to create a report layout where you can see a running "inventory balance" for each product in your database if you sort your records by ProductID.

          maybe reports could not work properly when i used same join table for two separate tables. Because of too many records in it. 

          That shouldn't be a problem and you can perform Finds that restrict your reports to specific sub sets of the total records in this table anyway.

          • 2. Re: Many to many relationship choice !
            traart

            Many many thanks :)