1 Reply Latest reply on Feb 21, 2012 12:22 PM by philmodjunk

    Add New record through relationship



      Add New record through relationship


      I have three related table in my database TABLE 1, TABLE 2 and TABLE 3. The main table (TABLE 1) is related to TABLE 2 by two fields, let's say A & B. TABLE 2 is related to TABLE 3 at field C. This is a one-to-many relationship where one record in TABLE 2 equals many from TABLE 3. Is there a way to add a new record in TABLE 2 automatically when a new record is added in TABLE 3? I have the relationship settings checked to allow this, but so far can't get it to happen. I'm wondering if it is because of the many in TABLE 3 to the one in TABLE 2. Can anybody help with this?

      Pete P.

        • 1. Re: Add New record through relationship

          Using realy names for tables and fields is actually eaier to follow than generic names and letters.

          Table 1::A = Table 2::A AND
          Table 1::B = Table 2::B

          Table 2::C = Table 3::C

          Don't see where Table 1 is a factor in this at all. If we are adding a new record in table 3 and need a new related record in table 2 created automatically.

          "Allow creation of..." is an option that enables only very specific actions to work. It does not set up any kind of automatic record creation just from this one selected option in the relationship.

          When you select this option for Table 2 in the relationship between table 3 to Table 2, You can then add a portal to Table 2 and entering data into the bottom blank portal row will automatically create a new record in Table 2, but only if you had a valid relationship between the two tables with an existing value in Table 3::C at the time you did this. In similar fashion, if you added a field from Table 2 to your Table 3 layout, entering data in this field would create a new related record in Table 2 if one such related record did not already exist and you still have the limitations I just spelled out for a portal.

          The biggest issue is that you are working from the Many side of your relationship. The typical relationship for this would look like this:

          Table 2::__pk_Table2ID = Table 3::_fk_Table2ID

          Where __pk_Table2ID is an auto entered serial number and _fk_Table2ID is a simple number field.

          Creating a related record in table 2 requires creating a new record and then entering the value generated in __pk_Table2ID into the _fk_Table2ID field of the newly created Table2 record that was created at the same time.

          When this is needed, usually a script is used:

          Go to Layout [table 2]
          New Record/Request
          Set Variable [$ID ; value: Table 2::__pk_Table2ID]
          Go to Layout [original layout]
          New Record/Request
          Set Field [Table 3::_fk_Table2ID ; $ID ]