2 Replies Latest reply on Jan 23, 2013 1:47 PM by pbedouk

    Many more "many to many"  questions

    pbedouk

      Title

      Many more "many to many"  questions

      Post

           I'm going around in circles and would rather not be!  I think I'm missing something basic about how Filemaker works. I'd love some help.  Here's my problem.

           Context:  A small retail grocery with an inhouse bakery.  The owner takes advance orders for pies, bakes them later, and then they get picked up by the customer.

           I have tables for customer info and order info, and a join table linking them together.  The join table only has it's own primary key field and the foreign key fields for the Customer and Order tables.

           I have a layout based on the customer table with the customer info showing.  I want a portal to show the open and closed orders for this customer.  There is a button (Add Order) on this layout to take me to the order details layout to enter an order.

           On the Order detail layout I'd like to see the customer name too.  Also I'd like to be able to add a second, third etc order for this same customer, using a button (Add Another).  At the end of the order taking 'process', yet another button (Done) to return to the original customer layout, where these new orders would be visible in the aforementioned portal.

            

           So my questions:

           What table do I base the Order detail layout on?  The Customer table or the Order table?  Does it matter? Why?  All the fields from both tables seem to be available when I create the layout.

           How do I create one or more orders and connect them back to the customer record?  IE does Filemaker do this automagically or do I have to populate the key fields somehow?

            

           How do I display the customer name on the order detail layout and keep it there as I create new orders?

           In general when do I have to manage keys and when does filemaker manage keys?

           Where is the best place to keep the order status (open or closed)? In the join table or the order table? does it matter? why? (There will hopefully be a report showing open orders by pie type so she knows how many of which kind to bake).

            

           I feel like if I understood the theory better, Id be able to answer these questions myself.  Any pointers to books, articles, postings etc would be appreciated too!

            

           Thanks in advance.

        • 1. Re: Many more "many to many"  questions
          philmodjunk

               I have tables for customer info and order info, and a join table linking them together.  The join table only has it's own primary key field and the foreign key fields for the Customer and Order tables.

               This would seem to indicate this relationship:

               Customers-----<Join>----Orders

               Customers::__pkCustomerID = Join::_fkCustomerID
               Orders::__pkOrderID = Join::_fkOrderID

               But that does not make sense. A many to many relationship is required if you have many orders for one customer (you have that) but also many customers for one order (Don't see how you have that).

               This looks like a one to many relationship:

               Customers-----<Orders

               where one customer may have many orders but only one customer for any given order.

               If you look at some of the invoices examples, you may find that this relationship makes more sense:

               Customers---<Orders-----<LineItems>------Products

               This allows a customer to use a portal to LIneItems to place a single order that lists several different baked goods, quantities and prices in one order, all selected from the list of Products that may be ordered.

          • 2. Re: Many more "many to many"  questions
            pbedouk

                 Thanks for dragging me away from the trees.  Now I can see the forest again!

                 You, of course, are so right.

                 "Many"  thanks!!