11 Replies Latest reply on May 28, 2009 10:00 AM by fitch

    question about line items



      question about line items


      this is probably going to sound very dumb but keep in mind i'm new to databases and i'm eager to learn but is taking me some time...

      my dumb question is what is the table line items for? what is line items? if i have invoice and  inventory what do i need that table for?

       i'm trying to create my database for my retail businessi founf a post here about it and someone wrote this


      • A customer can have many different invoices, but an individual invoice can have only one customer
      • An invoice can have many line items, but an individual line item appears on one invoice
      • A product can appear on many different line items, but an individual line item has only one product

      i'm fine with the first line but then i get lost :(



      also on the other hand I downloaded the free  business kit from file maker (best ting i could have done)  but i seem to be missing some tables, i have a customers table , a sales order table, an inventory table, but how do i keep track of payments? and orders? do people use diferent tables for this or do they just use sales orders for payments and orders? this may go back to line item... i don't know
      thank you in advance

        • 1. Re: question about line items

          Some businesses only invoice for one item at a time. In my experience this is uncommon, but if this is you, that may be the source of your confusion.


          A "line item" is simply an item on an invoice, typically including the item number/name, price, quantity, and extended price (price*qty). There could be others, e.g. size, color, etc. Since an invoice can have many items, it's best to store these items in their own table, linked to the invoice by a unique ID. This makes it possible to do detailed reporting, e.g. how many red widgets did I sell in July? 


          The FileMaker sample templates are generally very simplistic and not always exemplars of Best Practices in database design. I'm not sure what you think of as an "order," but typically that's the same thing as an invoice. As for payments, yes: use a separate table for them. 

          • 2. Re: question about line items

            "Line Item" refers to a row (or line) that normally is printed in the body of an invoice.  A Line Item describes a specific product, quantity, price, and possibly shipping date that was purchased.


            If a customer orders several different products from your retail business, do you print a separate invoice for each product the customer has ordered?   No, you print several line Items on one invoice (or you should otherwise the customer will probably complain).  Since each line item represents a unique combination of product, quantity, and price, this information should be stored in a separate table (i.e. Line Items table).   Since it is a separate (but related) table, you are not restricted to a maximum number of line items on an invoice.


            Even if you sell only one product, you should still design your invoice to handle multiple line items.  For example, if you short ship due to a back-order and later ship the remaining quantity ordered, you can still cut one invoice with two line items.  And it will also allow you to combine multiple customer orders on one invoice -- even if the price changed.


            There is a simple but effective invoice demo created by Comment that can be found here:


            • 3. Re: question about line items

              OK so line items is the info of a specific item in an invoice right??

              now I single invoice can have several items right? And then the separate into different line items... I hope I got this right :), And a product? ( like for example red widget) if I sell 8 of them each one has it's own line item id?



              now about inventory, who has a relation with inventory line items or invoice?


              the orders I was talking about is a table called sales order which is yeah invoice :P (I just checked)


              I really appreciate all your help thank you very much I have my nose in the missing manual And this forum, very helpful thanks!!!

              • 4. Re: question about line items

                "if I sell 8 of them each one has it's own line item id?"

                Only if you sold one widget on eight different invoices :smileywink:


                A typical invoice often has the following general format:


                Invoice Number:

                Sales Date:

                Customer Data


                3 red widgets                     $0.20 $0.60

                2 blue widgets                   $0.15 $0.60

                5 lefthanded screwdrivers $0.10 $0.50


                Sales Tax:


                Invoice Total:


                Each row of blue text is a single record in your Line Items table and they are frequently controlled with a Portal on the layout.

                • 5. Re: question about line items

                  There is the possibility of duplicate products, take for example:


                  You sell 3 red widgets at standard price of .20 each.

                  You sell 1 red widget at discount price at .10 because it is blemished.

                  You give the customer 1 red widget at 0.00 because they tell you that you messed up their last order.

                  You sell 1 red wiidget for larger price of .45 because they wanted their initials ingraved on it.


                  So the issue is, any time you have something different on a lineItem (maybe one Product ID 44 is Small, one is Large and one is XLarge, whatever the variation, you would want a new line so you could properly represent the difference to their accounting department and stock people.



                  • 6. Re: question about line items
                       ok so  i get what line items are now, but i still don't get why i need them :( is it just for detailed reporting??? and to what should this table be in relationship with? inventoty, invoices or both?
                    • 7. Re: question about line items
                         so line items keep track of inventory?, sorry if i;m asking to many questios...:(
                      • 8. Re: question about line items

                        It's the most flexible way for data entry and reporting. Your alternative is to use dedicated fields or repeating fields to collect data on each invoice. Trying to extract information from that invoice will be many times more complex and difficult if you choose not to use a related table for your line items.


                        One simple example:

                        A problem has been discovered with your red widgets that you've been selling and you need to review all invoices over the last week that sold red widgets. With a related table, you can simply perform a find where you enter the product id or description in the first row of a portal while in find mode and then specify a date or range of dates in the upper part of your invoice layout. If you have repeating fields, you end up having to search each and every repetition of that repeating field that will easily be 10 or more times complex on most invoice layouts.

                        • 9. Re: question about line items
                             ok i get it, Do you a template or do you know of a place where i can find a template to see how a line item should look like??, i'm going to have to created my self since the business solution kit didn't bring one, also do you know how a payment table should look like? thank you for all your help
                          • 10. Re: question about line items
                               I have a really stripped down example of both lineitems and a pricelist table for setting unit prices. If you'd like a copy, click my forum name and then click the send a private message option. Send me your email address and I'll email it to you.
                            • 11. Re: question about line items

                              Here's a start:






                              Accounting systems can be pretty tricky. If your goal is to learn FileMaker then dive in; otherwise I'd recommend either purchasing a ready-made system (quite a few are listed in the last link above) or hiring a developer.