4 Replies Latest reply on May 28, 2012 7:14 PM by alas

    Auto Create Records in Related Table

    alas

      I am having an issue that is driving me crazy. Because I imagine there is a simple solution but I can't seem to figure it out. I have two tables that are related on the unique ID field (Call them Table A and Table B). Table B contains three fields that are calculated fields based on a field in Table A. For all intents and purposes, I could have just put those three fields on Table A. But I wanted to keep them separated because I may ditch them later and wanted to keep them "off ot the side".

       

      So in Table A, there are 910 records. But in table B, I have no records. Ultimately there shoudl be 910 of them.

       

      I know if I have a related field if I put the field from Table B onto a layout with Table A info, when I fill in that field from Table B (call it Calc1), it would automatically create a new record in Table B, tied to the ID from the record in Table A. (I have the allow record creation checkbox in the relationship definition).

       

      What I can't figure out is where Calc1 is a calculation, that record creation is not automatically done. I can only get it to work by going into Table B, filling in the ID field with an ID and then the calculations are performed with the relationship in place.

       

      How can I get FM to automatically create records in Table B, based on a record/ID existing in Table A?

        • 1. Re: Auto Create Records in Related Table
          erolst

          Not sure what you mean with “ when I fill in that field from Table B (call it Calc1) …”

           

          You can put the calc fields from Table B on a layout of Table A, and you can even enter them, but since you can't write into a calculation field,

          there is no trigger for FM to create a new related record. You can control the creation of a new related record via a triggered script, or, in order to

          emulate the described behaviour, create a dummy non-calc field and write something in it (You could even use the foreign key field for that purpose, but then I'd recommend using a script trigger to disallow field entry once it is non-empty.)

          • 2. Re: Auto Create Records in Related Table
            alas

            Yeah.  That is what I meant by "when I fill in that field" (if it were a text field I don't have this problem, but because it is a calc field, there is nothing triggering FM to create the new record. 

             

            How would you go about creating the new record via a triggered script? 

            • 3. Re: Auto Create Records in Related Table
              erolst

              Obviously, I don't know your solution, but you write that the three calc fields in TableB are based on a certain field (Field1) in TableA. So I guess you need the related record as soon as there is a value in Field1.

               

              You could write a trigger script which checks OnObjectExit of Field1 if there is a value in it - and if there isn't already a related record! - and if so, grabs the pk, goes to TableB, creates a new record, sets the fk, commits and returns.

              Analogous method for any other logic that may apply. Don't know if this is the best or “official” method, but it works.

              • 4. Re: Auto Create Records in Related Table
                alas

                Gotcha.  Thanks.