3 Replies Latest reply on Oct 20, 2010 12:00 PM by philmodjunk

    Troubles with relationships creating records in other tables

    MichaelLoo

      Title

      Troubles with relationships creating records in other tables

      Post

      I am trying to make an a relationship that will help manage my asset database.  They want it to be searchable by asset AND by employee.  For example, I want to make it so that I can create an employee page showing all of the assets currently tied to him or her, but I want to be able to edit the asset directly, and switch out the asset's user on the fly.

      However, my problem is that this isn't working as well as I thought it would.  I created a table for a specific asset, like computer, and created a table with all the data in it called employee.  I created a relationship between them, linking the fields with the same name, and turn on the "Create record" option.  However, when I make an entry in the asset page, this record does not show up in the "employee" page.  What am I doing wrong?  

        • 1. Re: Troubles with relationships creating records in other tables
          philmodjunk

          The "create record" option works when you enter data in the bottom blank row and in certain other specific situations. Creating a new asset record will not automatically create a new employee record nor will the opposite do that either.

          You may want to set up a portal to assets on your employee layout or vice versa.

          Note that using employee names to link your two tables is not the best approach here. If "Jane Doe" gets married and changes her name to "Jane Smith", changing her name in the employee table will break the connection to any asset records. Using an EmployeeID serial number field in the employee table to link the employee record to a matching number field in the assets table avoids this problem.

          • 2. Re: Troubles with relationships creating records in other tables
            MichaelLoo

            I'm unclear as to how a portal would help me.  My problem is, if I just use one table, and Sam switches phones with Tina, if I change the name in my "phone" page, it changes ALL of the records, so that Sam has theoretically switched all of his assets with Tina.  I would like it so that they are contained in their own tables, and when I alter one, it alters that specific section of the other.  Wouldn't a portal just make it act like I only had one table?

            • 3. Re: Troubles with relationships creating records in other tables
              philmodjunk

              A portal allows you to managed the data in one related "child" table from the perspective of a specifc record in the other "Parent" table.

              If you have this relationship:

              Employees---<Assets

              Employees::EmployeeID = Assets::EmployeeID

              Then you can place a portal to Assets on the Employees layout. If you find the employee record for Sam, the portal will list all the assets that belong to Sam. If you find the record for Tina, the portal lists all the assets for Tina.

              If you enable "Allow creation of records via this relationship" for Assets in this relationship, you can log new assets for Sam by finding his employee record and entering the data in the bottom blank row of the portal.