4 Replies Latest reply on Oct 30, 2008 5:28 AM by Kat4

    Orders and Order Line, Help!

    Kat4

      Title

      Orders and Order Line, Help!

      Post

      This is my second thread and question today. I find the more I read, the more questions I have!

      I'm having trouble with the concept of the need for two tables, Order and Orderline, instead of just Orders. I understand its about a 'many to many' relationship, but this particular one really stumps me. 

      I'm making this database for a gallery and need to deal with many customers (of varying types), multiple products (with varying options and pricing structures eg framed or flat, wholesale or retail, US or CA dollars). This part I think I can handle. Each one can be dealt with in one table each (I think). So, one more table is needed for Orders... but, the relationship between orders and products is a 'many to many' relationship... is that right? If so, where is the dividing line between what an order is and what order line is?

      I've been trying to think of this in terms of how the business runs with paper forms, eg purchase orders and invoices and then order tracking.... but I'm not clear.

      Any suggestions or clarification is greatly appreciated. 

        • 1. Re: Orders and Order Line, Help!
          AndreasT
            

          This should get you started with a basic setup. 

           

          The order table functions as a header for the whole order, where customer details, shipping, sum of all orderlines and such are stored. Customer details are usually pulled from the customer table using lookups. There is usually no need for a direct relationship between the order and product tables.

          You have an Order ID field which is set to be auto enter serial number and must be unique and not empty.

           

          The orderline table holds the items that are purchased. One record for each item. 

          The orderline table also has an Order ID field, but this is just a regular field with no validation. This field is used as the relationship match field with the same field in the orders table. You check the Allow creation of records... box on the orderlines side of the relationship. This makes Filemaker automatically fill the Order ID field in the orderline table when you make a new record.

           

          You also have a relationship to the products table using the stock code as a match field. Fields such as product name and price are lookups from the products table. You also need a quantity field and a field orderline_sum with a calculatation like:  quantity * price. 

           

          In the order table you get the order total with this calculation: sum (orderlines::orderline_sum)

           

          Set up an orderline portal on your order layout to display and enter data.

          • 2. Re: Orders and Order Line, Help!
            Kat4
              

            Thank you Andreas,

            That sounds clear. If I understand correctly, the orderline is where the "action" so to speak, of the actual order occurs. So if I'm creating an order for a customer, I create a new order using the "Orders" table, but the actual calculations occur because of the way the Orderline table is set up. Is that correct?

            And just to clarify, the order is where I would set up an Invoice print out from also (like a report?), pulling the pricing detail (regarding quantity, taxes etc) from the orderline table (with the customer detail already in the order record, having been pulled from the customer table) ? 

            • 3. Re: Orders and Order Line, Help!
              AndreasT
                

              Kat4 wrote:
              That sounds clear. If I understand correctly, the orderline is where the "action" so to speak, of the actual order occurs. So if I'm creating an order for a customer, I create a new order using the "Orders" table, but the actual calculations occur because of the way the Orderline table is set up. Is that correct?

               

              Yes, the only calculations occuring in the Orders table is the total of all orderline sums as well as any discount, shipping and so on that you want to apply to the whole order.

               


              And just to clarify, the order is where I would set up an Invoice print out from also (like a report?), pulling the pricing detail (regarding quantity, taxes etc) from the orderline table (with the customer detail already in the order record, having been pulled from the customer table) ?


               

              You can print out order slips or even invoices from the Orders table, but this only works well with a limited number of products, as portals are limited. If you plan to have orders with many items, you should print from a layout based on the orderlines table and use related fields from the orders table to show the customer details and order header. You will need to use a script from the Order to show related records using this layout and then print.

               

              Lastly it is common to use a separate table for invoices, though not strictly required. One reason for this is that invoices should be designed to lock out editing when they have been issued. Another reason is the sequential invoice numbers, where the sequence must not be broken.

              A table for invoicelines is then also required. An import script moves a completed order to the invoice table, doing the same with the orderlines to the invoiceline.

               

               


              • 4. Re: Orders and Order Line, Help!
                Kat4
                  

                Thanks again, Andreas.

                I can see I'll need the separate Invoice and Invoiceline table.

                More challenging set up work, but I'm keeping your post closely on hand to refer to! 

                 

                Kathy