You have Inventory of Products, right?
In your inventory table, there are lots of different materials that are Products, correct?
Each product should have a unique ProductID, and each record in Inventory should have that unique ProductID in it so that you know what product you are holding inventory of.
Your tables would then be related from Product:: ProductID = Inventory:: ProductID
Then you can simply reference the price from the related table.
The solution isn't to reference a value from a non-related table, but is instead to make the tables related.
Edit: removed smileys
Sorry, I think I expalin unclear
The inventory is for materials not for product
inside inventory there are the materials used for build different product
If product code changed, the material quality or type will be changed too
and product is different most times.100 different ways
OK, If I now understand correctly, you have Inventory of Raw Materials and you have finished Products made from those raw materials.
If you use one price for your materials, you would need a table in-between those two that has the FormulaLineItems or BillOfMaterialsLineItems. The Raw Material would have a price, the BillOfMaterialsLine Items would calculate the price of each material used multiplied by the quantity used, and the Finished Product would Sum() the related BillOfMaterialsLineItems.
Table: InventoryRawMats >--- BOMLineItems >----------Product
Field: Material Price CostOfMaterialUsed Product Cost
If you track which shipment of raw material went into which lot of product and calculate a different cost for each lot of product, this scheme would get a bit more complex.
I get you method, But this will cause over 700 new entries
I have a logical way that in my head, but not sure if filemaker have code to do so. (anto)
In inverntory Table, I had an Choose of Type for Product, if It is choose 1, Material 1 will be only 5 used, Material 2 will be 10 used, choose 2, Material 1 will be 10, Material 2 will be 20.
I also, had another type choose in Product, choose 1 Material 1 wil cost $1, Material 2 will cost $1.5, choose 2 Material 1 will cost $1.5,Material 2 will cost $2
Because the double relation I hardly to use the function and relation.
I tried to find calculation fuction, but dont know which I should use
Also, If the above is hard to do,
Is it possible that
In inverntory Table, have mulit-relation with the material and to get the price
For example, Material 1 Type A, Material 1 type B, Material 2 type A,Material2 type B
If and choose happen in Product Table, Choose 1, it will get all Type A, choose 2, it will get all Type B
In inverntory Table, I had an Choose of Type for Product,
if It is choose 1, Material 1 will be only 5 used, Material 2 will be 10 used,
choose 2, Material 1 will be 10, Material 2 will be 20.
Yes Sir. That's a formula (or Bill Of Materials).
, choose 1 Material 1 wil cost $1, Material 2 will cost $1.5,
choose 2 Material 1 will cost $1.5,Material 2 will cost $2
Like same kind material that have different colour, that will cause different price=-=
Forgive me if I'm being thick-headed...but if the customer cares what color the product is, then green material and blue material are two different materials...they can't both be Material1. And if this is the case, the BillOfMaterials must specify which is to be used (and reflect the appropriate cost).
And if the customer doesn't care what color it is...you would always buy the cheapest one and have only one price.
I don't mean to dig into what you're trying to accomplish...but I do want to make sure that what we build together accomplishes what you are trying to do...otherwise it is all for nothing.
Could you post an overview of the end goal that you are trying to accomplish? Then we can make sure that we head in the right direction.
Beacuse, the they only can choose one kind colour or one kind style or something
I want to find the total costs
I trying to find a way that may work, but I dont no if there a code exist
which is for some field equals to specific field that unrelate?
forexample, field A in table 1= the third line field X in table 2