This is probably a stupid question, but I'm a beginner trying to get the hang of Filemaker - hoping to get some help from you experts. I want to construct an inventory/invoicing system consisting of the following databases:

Products (basically a static file with all articles that I can sell - they have a part number and a unique serial number)

Clients (the clients that I sell these articles to)

Inventory (a live database of the articles I presently have at home)

Invoicing (the sales that I make - articles are pulled from Inventory to this one and an invoice is printed)

The problem I have is with the invoicing part. I have been able to show only the products I have in my inventory, and to choose between serial numbers in the case I have more than one product with the same part number. What I haven't been able to do is - like many of the downloadable examples - to make a list of several products to sell on the same page. I want for instance to be able to sell 10 different products on the same invoice. The examples I've downloaded all have this fancy portal system (I think?) where as soon as one product is added to the invoice, a new line is added to the invoice for adding another product. I don't know if I make myself clear, but it all comes down to the ability of adding several products to the same invoice - and to do it in an efficient and easy to use way. How should I do it? Any help would be appreciated. I can gladly send you my files to show you what I've done. Pls. send email to [email protected], since I don't check into this site very often. Thanks in advance!

The missing link here is a Line Items file, that joins the Invoice with the Products and/or Inventory. Line Items would contain the fields: Invoice Number, Product Serial Number, plus any pricing and descriptive info to look up from the Products file based on the Product Serial Number. Also Quantity and Extended Price (Qty*Price) if needed.

Line Items records can be shown in a portal on an Invoice record. Make a relationship from Invoice to Line Items based on the Invoice Number, and allow new records to be created. Now, when you make an Invoice, and enter a Product Serial Number in the portal, it will automatically create a new record in the Line Items file.

Is that enough to get you started?

OK, so if I got you correct, this Line Item file is empty, and everytime a sale is made, it adds the product (and all info about it, including the invoice number) to this file. Thus, this is the sales information file? All sales reports can be extracted from this file, right?

I'm going to try it. Thanks!

"...this Line Item file is empty, and everytime a sale is made, it adds the product (and all info about it, including the invoice number) to this file. "

Correct.

"Thus, this is the sales information file? "

Sure, you could call it that.

"All sales reports can be extracted from this file, right?"

That's a pretty broad question! Probably.

The design decision you have to make is: do I do all my calculating and printing from the Invoices file (via portal) or from the Line Items (via summary)? I.e., in Invoices, your total would be a calculation field, based on a single record, the invoice -- Sum(Amt::Line Items). Whereas in Line Items your invoice would be based on a found set of records, and the total would be a summary field, Total(Amt). If your invoices have a lot of line items, sometimes the Path of Least Resistance dictates that you print your invoices from the Line Items file.