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.
"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:
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!!!
"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:
3 red widgets $0.20 $0.60
2 blue widgets $0.15 $0.60
5 lefthanded screwdrivers $0.10 $0.50
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.
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.
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?
so line items keep track of inventory?, sorry if i;m asking to many questios...:(
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.
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
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.
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.