Are you looking for set multi level pricing? I would use a pricing table as a child to the item table and lookup from there. I suppose there are a few ways to do this.
You could have a JOIN table between that has
PRODUCTS - product_prices
This join table could have several "foreign key" fields (one to each 'other' table).
I'm not entirely sure why you need to do this, but the "join" between would certainly help. What you are then selecting is the PK from the "product_prices" to tunnel the price through to "PRODUCTS" (which can also be used in the invoice items). You see how complex it is? So again, let's re-think...
1. are you using it just to practice FMP or do you/your client really need that solution?
in the last case: you need to add ORDERS. because there is accounting wise (think stocks, payments etc) a big difference between orders and invoices.
2. what you need is a "recipe-table" (doesn't matter if you talk about powders or computer parts). imagine an online computer shop. you order a laptop. the shop assembles the laptop according to the "recipe" or "assembly" table (standard model) plus your extra's.
so in the recipe table you list all parts, as far as people can order them separately, plus any assembled laptop that you sell. accounting-wise (you will see the same often in invoices from suppliers), you get order- confirmations will a whole list from the recipe/assembly table which lists every part.
As a further explanation, First of all, thank you all so very much for you replies.
Each of my line items is, for all practical purposes, unique, so a look-up table, I don't think is a viable way to do this one.
Ya you are correct, it does get very complex and that is why I am wondering if there is a better "big picture" way to do this ;-) And your solution of having a join with several foreign keys is the way that I have been attacking it. But as you say, it looks like it will be getting very complex.
Your case explaining the computer shop is interesting, but not sure it can account for all the things that I need to, I will think more on that. The only thing with the recipe table is that each "ingredient" for me may or may not need more production onto it before being put into the final product.
I am building this system for my own business, so I am the self developer. I have built a Filemaker Invoice Solution that I have been using over 15 years. In much simpler form. First version long ago had repeating fields none the less. Now I would like to re-build from scratch to make is much better.
So possibly as a deeper explanation...
Any line item can be very simply or massively complex... For example.
I purchase raw product and then I put work into that product. So the table that I go to in order to develop the customer prices might start with a simple "Cost Plus" Scenario, where I enter the cost of goods, then mark that cost of goods up a certain amount. Then, there are several other costs that I want to put into that product "line item" for the labor put into that base material, and then other materials added to that original material. And then there may be one or more flat rates to put onto each line item based upon minimums and such.
Sometimes I might want all of that workup information to be in the line item description and sometimes not. It can depend on the project. But suffice it to say that each line item is essentially unique. While there may be times when a line item is the same as some other invoice, for the most part they are all unique because of the vast amount of custom manufacturing that can go on. So a table listing all of the possibilities and then looking up is not viable here.
Any other thoughts?
This is just BOM where you have "assemblies" as a single price and that is what is shown on the invoice. However what the buyer sees and what you must pay for the materials, labor, service, etc. is typically what your accountant may need to see.
So create an "assemblies" table (a join) that may be several components combined into one price. With the relationships being two-way, you can always get back to a full breakdown of the components if ever needed.
Thank you again so much for your feedback here. I think that I understand you and will try that. But what do you mean BOM?
Bill of Materials, which can be a 'summary' (single price/line item per assembly), but the Cost Accountants want to see every component as a line item.
When I write quotes, these typically are assemblies. The final invoice may be assemblies or components (depends on customer). But the final BOM is components.
You may search for BOM on this forum for many suggestions.
(not to be confused with encoding text that may have a byte-order-mark)
Got it. Thank you very much....!!!
I would also advise having a stock qty field even if it is a unique item system and the value is always either 1 or 0. I have worked with companies that deal with unique products and they generally have the same thoughts on not needing a stock field, but eventually they see the benefits of a qty on hand.