This sounds like it would be best handled in the product record, as it will presumably be consistent. You don't say what the material is, but I assume you could, within the product record, calculate, say, a weight per square metre (or linear metre, depending on how it is sold). It would then simply require a calc in the order table where the specific amount ordered is known to generate the actual weight in the order.
The material is mostly paper and the field in Product table is in format: numbers. it's an automatic field with the number of weight in square meter.
The calc I have to do in the field weight/roll in Order table. Do you have an example how to set this up?
I have tried to get the value from different field in order table and also from produkt table. I don't get it right...
Does an order only consist of a single product?
You need a calculation field that will hold a formula to calculate the weight per roll. You know the length (in metres) and the width (in metres) which you can multiply together to get the area of the material, which is then multiplied by the weight as auto-entered into the appropriate field.
So, in the orders table you want to add the WeightPerRoll field with a formula of:
Orders::RollLength * Orders::RollWidth * Orders::MaterialWeight
Note that I have assumed that the length and width are measured in metres; you will need to adjust the calculation if the units of measurement are different.
So, for example. I have tree different material weight. In product I have made each material as a field with auto data with the number for the weight of that material.
How can I with our calculation get the right field that have the right weight in to the calculation?
In Order i have the roll length and the roll width and I also have a material field but that have only the different name of material in it.
The following calculation will give you weight in kilograms. For the values shown (80gsm paper, roll 30m x 1200mm) the result is 2.88kg. To use this calc you would need to substitute fields for the fixed numbers I have used.
Let ( [
gsm = 80
; width = 1.2
; length = 30
; sqM = width * length
; rollWeight = sqM * gsm / 1000
So long it is correct.
See my reply just posted above.
If you only have the name of the material in the orders table, you need to auto-enter the results of a calculation into the Orders::MaterialWeight field.
For the calculation to work you will need a relationship from the Orders table to the Products table, where the relationship is defined as Orders::MaterialName = Products::MaterialName. Your auto-enter calculation on the Orders::MaterialWeight field would then be "Products::MaterialWeight" and you would set the calculation to override existing values in the field (so that if you change the Orders::MaterialName field value the Orders::MaterialWeight value will update). Set the Orders::WeightPerRoll field to be an Unstored calculation so that it too will update when the Orders::MaterialName field is changed.
From there, keywords' answer should prove to be correct.
So, if I have material field in order that say: coated paper.
And material field in product that say: weight coated paper. And this is auto data: 80. (80g)
Can I in this calculation get if the order field is coated paper it gos and get the no. 80 in weight coated paper in product layout?
Can I somehow get the field coated paper in order to have the grams in it as a value? And showing the name: coated paper in the field?
In your product table you should also have fields for whatever characteristics you wish to store—paper style, type, grain direction, description, etc.
For the purpose of the weight calculation you have asked about, you should have fields for GSM, RollWidth and RollLength. You then reference these in a RollWeight calculation such as I proposed earlier—just substitute the relevant field name where I have a hard coded number (the number is purely for demonstration purposes).
The RollWeight—or any of the other fields for that matter—could then be referenced in your order table to derive the weight for a specific order.
But if i in the future I will expand the product table, is this a good solution then? Now there is 3 post in product and if I then get more post with different information, is that working?
Is there an solution were I can only use order table? I'm thinking, roll length, roll width and somehow fixed numbers for material?
Now I select the material from a list and fill in the numbers for roll length and roll width. In the weight/roll field I want to see the weight automatically.
Try adding records to the Product table in the example file I provided. The value list is dynamic and will reflect the changes to this table. The relevant weight will be copied in automatically and the calculation will adjust accordingly.
I would strongly advise you to avoid using hard-coded values and storing everything in the Orders table. Having a separate Products table allows you to store additional details about each product (as keywords mentions in comment #11 above) and has benefits for future development of the system.