5 Replies Latest reply on Oct 1, 2014 10:06 AM by philmodjunk

    Automatically create record

    Sam_1

      Title

      Automatically create record & perform calculation in child record based on new parent record

      Post

      I know this topic has been dealt in some other forums but I have setup by DB in a different way where the information in Parent Table A is used by multiple Child Table B,Child Table C, Child Table D. Most of the fields in Child table are 'calculation field' based on Parent Table A information.

      When I add new record to Parent table A, how do I ensure that all calculation in Child Table B,Child Table C, Child Table D are done automatically ? Do I have script it or FMP has some functionality. 

      I did check the 'allow creation of records in this table' when setting up the relation. I thought this would do it. But apparently this feature is more for portal than for overall automatic calculation and record creation.

      I appreciate the help. Thank you !

       

       

        • 1. Re: Automatically create record & perform calculation in child record based on new parent record
          philmodjunk

          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.

          • 2. Re: Automatically create record & perform calculation in child record based on new parent record
            Sam_1

            Hello Phil,

            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 !

             

            • 3. Re: Automatically create record & perform calculation in child record based on new parent record
              philmodjunk

              Does that mean that your relationships look like this? (I'm shortening the table names to save typing)

              MfgDemand>-----Product----<SalesData
                                                  |
                                                  ^
                                          RawMaterial

              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.)

              • 4. Re: Automatically create record & perform calculation in child record based on new parent record
                Sam_1

                Hello Phil,

                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

                where

                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 !

                 

                 

                • 5. Re: Automatically create record & perform calculation in child record based on new parent record
                  philmodjunk

                  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) ]
                  New record/Request
                  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]