6 Replies Latest reply on Jan 23, 2010 5:15 PM by Steve Wright

    Using a Child Record's generated key to populate Parent Record

    Steve Wright

      Title

      Using a Child Record's generated key to populate Parent Record

      Post

      Something I have been un-aware of, probably due to never actually trying or needing..

       

      Lets say you have a relationship as follows  ParentTable::keyfield  =  ChildTable::keyfield

      and a Layout based on ParentTable, showing related fields from ChildTable

       

      Obviously, if ParentTable::keyfield contains 1

      then any child records created over this relationship will have the ChildTable::keyfield set to 1.

       

      What I didnt realise was,

      If ParentTable::keyfield is empty and ChildTable::keyfield is set to auto generate

      Entering data into the related fields such as ChildTable::fieldA would populate the ParentTable::keyfield with the value from ChildTable::keyfield

      to make the relationship valid.

       

      Obviously, in this scenario I would only want a single related record.

       

      Normally, what I would do here is go to the related record table

      Create a new record

      Grab the ChildTable::keyfield into a variable

      Return to the original layout

      Set the ParentTable::keyfield to the variable.

       

      Q. Has it always worked this way ?  Have I just been overlooking this for years !

      I feel a bit stupid asking this question to be honest, it makes sense why it works, I just wasnt expecting it to.

      I was expecting something along the lines of "This field cannot be modified until "ParentTable::keyfield" is given a value.

       

       

        • 1. Re: Using a Child Record's generated key to populate Parent Record
          RickWhitelaw
            

          "If ParentTable::keyfield is empty and ChildTable::keyfield is set to auto generate"

           

          Having the Child Table generate auto-entered serials in the field used as the key to the parent table  doesn't seem to make sense. If the Child table is also going to be the "Parent" to another table then it should have a separate field that's set to auto-enter serial values which would in turn be passed on to its "Child" Table. Also, the only way the original Parent keyfield could be empty (as long as the serial is set to generate "on create") is if it was deleted after creation, which ought to an option unavailable to the user.

           

          Having said that, and I'm sure none of it is news to you, what your post points out is interesting. I've not tried it. It seems like a recipe for disaster since the Parent could generate serial numbers later on that already exist in the Child Table.

           

          RW 

          • 2. Re: Using a Child Record's generated key to populate Parent Record
            Steve Wright
              

            I may have confused it a little using simple field names..  
            I didnt mean the Parents table Primary Key would be populated, I was going more along the lines of populating a foreign key

             

            Ill give you another example...

             

            Lets say we have an Invoice Table which is linked to a customer table (using above method)  Invoices::CustID  =  Customers::CustID

             

            In this instance, we would want the customers:custID to be the primary key 

            and we would want the invoices:custID to be the foreign key.

             

             

            Now on a layout based on the invoice table,

            * We could select from existing customers and populate the foreign key Invoices::CustID

            * Or.. we could do what I was proposing initially above (via script), to get the ID

            * Or as I have just found out.. we could simply put the related fields on the layout and allow entry into the fields

             

            In which case if Invoices::CustID is empty, it becomes populated by the newly generated Customers::CustID,

            Otherwise if Invoices::CustID is not empty, then we must be editing an existing customer record

             

            The invoices::CustID would never generate, so that shouldnt cause any issues.

            I also dont see any issue with Customers later becoming a parent table. Since the key is set to 'not update' and the field wont be accessible.

             

             

             

            • 3. Re: Using a Child Record's generated key to populate Parent Record
              comment_1
                

              I believe it has worked this way since version 7, when relationships became bi-directional. Every now and then, someone "discovers" this.

               

              I don't think there's any danger. In fact, it can be quite useful in some situations - see, for example:

              http://fmforums.com/forum/showpost.php?post/288333/

              • 4. Re: Using a Child Record's generated key to populate Parent Record
                Steve Wright
                  

                Thanks for the comments and link..

                 

                Ive been playing with it quite a bit and its certainly helpful if used the correct way of course.

                Im happy that Ive finally discovered it, thats for sure...  Why it took me so long, I have no idea..

                • 5. Re: Using a Child Record's generated key to populate Parent Record
                  RickWhitelaw
                    

                  Ahhh . . . it's beginning to make sense now and I can see it as a technique rather than a dodgy situation. This can obviously be quite useful. "Allow creation of new Records" must be turned on for both tables, no?

                   

                  Thanks.

                   

                  Rick.

                   

                  • 6. Re: Using a Child Record's generated key to populate Parent Record
                    Steve Wright
                      

                    Allow creation of related records (as in the example) would only need to be turned on for the child side (customers)

                     

                    After further testing, it is so far proving to be very useful.. definitely something I'm glad I stumbled across.