You'll need a number of different tables. Take a look at how invoicing examples work. You have one as a starter solution that comes with Filemaker 11.
In your case, each invoice is a "quote", and you have "parts" instead of products but other than that, the relationships are the same.
Substituting "parts" for Products and "Quotes" for "invoices", you'd get:
Customers::__pkCustomerID = Quotes::_fkCustomerID
Quotes::__pkQuoteID = LineItems::_fkQuoteID
Parts::__pkPartID = LineItems::_fkPartsID
See this link if the above notation is unfamiliar: Common Forum Relationship and Field Notations Explained
With these relationships, you can put a portal to LineItems on your Quotes Layout and select Parts by formatting the _fkPartsID field as a drop down list of PartID numbers from Parts.
Thanks for responding so quickly -- I really appreciate it! I will try your suggestions. Thank you again for your help!!
So for whatever reason, I don't have starter sollutions on my computer. But I figured out how to get line items to list in my quote (invoice) without repeating. Thank you again for your great explanation!
Of course, I have a new challenge! We are a small office and sometimes the prices in our product inventory table are not updated frequently enough to be accurate. In our current system, people go to the most recent quotes to find the most current price for a product. Is there a way to allow the price of a product to be updated in the inventory table by editing the product price in the Quote table -- without "updating" the product price in all previous quotes (when the old price was valid)?
Thanks to anyone out there with any ideas!!
Prices should be stored in the Products table, not quotes and then a looked up value field option copies the price from products into lineitems when you select a product.
A script could take the current price and update the price in Products and this change will only affect new line items recors that you create. The existing records would retain their copied prices.
A button in the line items portal could perform this single script step:
Set Field [Products::UnitPrice ; LineItems::UnitPrice]
You may find this invoice demo file from a different FileMaker forum helpful: http://fmforums.com/forum/showpost.php?post/309136/
Thank you again for your quick response. I tried the script but I'm not sure I'm doing it correctly. I can't seem to get both Products::UnitPrice and LineItems::UnitPrice inside the Set Field brackets. It will only allow me to enter one or the other. But I will keep playing around with it.
Thank you again for your tremendous help!!
When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.
Oh my gosh -- it worked!!! (Not a surprise to you of course -- but I am totally amazed!!) Thank you, thank you!! This made my day!!! This will be so helpful!