4 Replies Latest reply on Oct 28, 2012 11:19 AM by jjfcpa

    Newbie - Getting parent key to populate in child table?

    jjfcpa

      I have just two tables.

       

      Client and Employees

       

      Client has a parent key - __ClientID

       

      Employees has a parent key - __EmployeeID and a foreign key - _ClientID

       

      I have an Employees layout that I use to add new employees and whenever I add a new employee I'm not getting the foreign key populated. What is the proper way to make this happen?

       

      Right now, I'm just clicking the New Record button to add a new record. I am not using a portal. I believe I have the relationship graph setup properly. I have a one to many relationship between the Client and the Employees. I've tried to use the auto-enter properties of the _ClientID field to populate it with the __ClientID field in the Client table, but it still ends up blank.

       

      Not really sure what I'm doing wrong?

        • 1. Re: Newbie - Getting parent key to populate in child table?
          jormond

          You need, in some way, to tell the record who it belongs to. It has no way of knowing unless you tell it.

           

          Options:

          • From the parent record: Script the creation of a new employee record. And attach it to a button.
            • Set Variable [ $ClientID ; Clients::__ClientID ]
            • Go to Layout [ EmployeeLayout ]
            • New Record/Request
            • Set Field [ Employees::_ClientID ; $ClientID ]
            • rest of what you need to do
          • If there is not an already existing record, FM will create one and insert the match field for you through the relationship.
            • from Client table: Set Field [ Employees::FirstName ; ValueYouWantEntered ]
            • FYI...you can also create a special relationship that you know will always be unrelated...then FM will continue creating new records for you.
          • Create the new record in the Employee table and manually ( by typing, or selecting from a drop-down list, or some other creative method ) enter the ClientID.
          • Are others also...but I'm tired and falling asleep. hehe
          • 2. Re: Newbie - Getting parent key to populate in child table?
            comment

            jjfcpa wrote:

             

            I have a one to many relationship between the Client and the Employees.  I've tried to use the auto-enter properties of the _ClientID field to populate it with the __ClientID field in the Client table

             

            That cannot work, because there is no related record in the Clients table - until you populate the foreign key. If you are not using a portal, and you want to create a new record in Employees related to the current client, you must take the current client's ID with you, for example:

             

            Set Variable [ $clientID ; Clients::ClientID ]

            Go to Layout [ Employees ]

            New Record

            Set Field [ Employees::ClientID ; $clientID ]

            ...

            • 3. Re: Newbie - Getting parent key to populate in child table?
              jjfcpa

              Joshua - Thank you.  I see the errors of my ways and have fixed it.

              • 4. Re: Newbie - Getting parent key to populate in child table?
                jjfcpa

                Michael - Thank you - I got it fixed and it's working now.