I think that you'll need to explain your design in much more detail. Leaving the "alphabet soup" behind and using the actual table names would also help. Not only are they easier to keep track of, but doing so often provides us with better clues as to the ultimate purpose behind your design.
A calculation field in a child record will automatically reference the related parent record's data if:
a) it's a one parent to many child relationships as is usually what "parent" and "child" terms imply.
b) You have a child record linked to that parent record.
c) it's a calculation field and not a data field with an auto-entered calculation.
But creating a parent record does not automatically create records in the child tables. Such is usually not needed, but there are exceptions. If you use a script to create the parent, the same script can create related child records.
Thank you for response. The database had following structure
Parent: Product [it has all the information regarding a companies' product]
Child: 'Product Industry sales data' [estimates in USD for product sales]
'Product Manufacturing Demand estimate[estimates manufacturing based on industry sales data, and our own assumptions...all fields in this table are calculation based]
Product Raw material [raw material required for product based on Product manufacturing demand estimate...all fields in this table are calculation based]
As per your comment, I have all (a) (b) and (c) set up.
What I want is that once user enters a New Product' and 'Product Industry sales data' data value.....a new 'Product Manufacturing Demand estimate and Product Raw material value is automatically created and calculation performed.
Thank you for help !
Does that mean that your relationships look like this? (I'm shortening the table names to save typing)
Product::__pkProductID = SalesDate::_fkProductID
Product::__pkProductID = MfgDemand::_fkProductID
Product::__pkProductID = RawMaterial::_fkProductID
A script could automatically create the related records in MfgDemand and RawMaterial, but what calculation are you referring to? I'd need to see what that calculation is before I can comment further.
(And often, automatically creating records in a related table when a new parent record is created is not really necessary, though there are exceptions. In FileMaker it's very easy to create such records only on an "as needed" basis.)
Thank you again for answering! The DB diagram and relationship are right on target.
Regarding calculation Product table(P) has following field: ID, Name, Type, Quality, Overfill
Product Sales data(PD) has: ID, 2013 Annual sales data, 2014 Annual sales data, 2015 Annual sales data,2016 Annual sales data
Product Mfg (PM)Demand:Product: ID, 2013 mfg demand, 2014 mfg demand est, 2015 mfg demand est, 2016 mfg demand est
2014 mfg demand est = (PD::2014 Annual sales data/PD::2013 Annual sales data) * PM::2013 mfg demand*PD::Quality*PD::Overfill
so I want a script where if the user enter the Product ID and Name in P table and Sales data in PD table....the system automatically creates a new ID and perform calculation in PM table
Hope this explains the request.....thank you for all your help !
But the script will not need to perform the calculation as a calculation field in the new record will do that automatically. Thought there can be cases where you might compute and store that data in a number field instead of using a calculation field to compute it in an unstored calculation. Which is better is not a question I can answer from the info provided, there are pros and cons to either approach.
ProductID should not be entered by the user given the above data model except as a way to perform a find for a record that already exists.
__pkProductID would normally be defined as an auto-entered serial number or a text field with Get ( UUID ) as it's auto-enter calculation.
A button on the layout might run a script like this to create the new related records after the product and sales data have been entered:
Set Variable [$ProductID ; value: Product::__ProductID ]
Go to Layout ["Product Mfg" ( Product Mfg) ]
Set FIeld [ Product Mfg::_fkProductID ; $ProductID ]
#If you choose to have the script compute the value and put it in a number field, add another set field step here
Go to Layout [original layout]