I haven't looked at the FMP 11 invoice template. It should be setup with at least three tables Invoices----<lineItems>---Products. (The actual names may be different.)
Usually, you have a portal to LineItems on your invoices layout and fields in the Lineitems table use the product number to look up description and unit price data from the Products table. Thus the Products table would serve as your "table of approx 1000+ products - with simple data such as : Product Code / Product Name / Prodcut Selling Price."
Is that the structure of the template?
If so, then, "We intend to have this down 10 rows for larger orders" should not be a problem as you simply need to either modify your portal to have at least 10 rows or you enable a scroll bar for it.
The main issue would appear to needing to search a list of 1000+ products quickly and perhaps using auto-complete on the product name field in order to find a given product.
You might want to design a global "search field" with a script that adds the located product to your invoice. If so, you might be able to adapt the following technique to suit your needs:
Hi Phil -it only appeared to be 2 tables : "purchase orders" and "Line items"
I managed to figure it out in the end, by adding another table "Products" and relating that to the same keyfied within "Line Items"
It works how we want it to - thats the main thing
For searching, I set the product code field to a DDL with a value list from "Products"using 2 fields "Product ID" and "Product Name" - As we know our products inside out, this is perfect for us as we can find them quickly by code or name. Once selected, the remaining fields populate accordingly.
Im hearing our product field may rapidly increase to over 30k products, so I would need to revist in the future to something a little more drillable in terms of search.
For now, this is working, and is suitable enough for my demonstration.