2 Replies Latest reply on Jan 10, 2013 10:02 AM by JoePerry

    Populate ID field in multiple related tables

    JoePerry

      Title

      Populate ID field in multiple related tables

      Post

           I am new to FM and my primary background is SQL server. I am creating a database and have a quesiton. In SQL there are processes called triggers that can run on a record create/update/delete to populate data in related tables. I cannot seem to find this in FM Pro. I have 2 tables:

      Table 1

           recID

           Name

           Device

      Table 2

           recID

           Manufacturer

           Type

           Serial Number

           I have built a relationship between the 2 tables on recID. What I need is for a record to be created in table 2 with just the recID field populated everytime a new record is created in table 1. So table 1 will be completely filled out and table 2 will have a row for each recID that can be filled out later. Has anyone done this before?

        • 1. Re: Populate ID field in multiple related tables
          philmodjunk

               FileMaker does not have triggers that can be set at the data level. It DOES have triggers that can be set at the interface level.

               This type of automatic creation of related records usually isn't really needed. The related record in Table 2 can be very easily created on an "as needed basis", but it can be done:

               First the relationship:

               Table 1::__pkRecID = Table 2::_fkRecID

               Double click the relationship line and select the "allow creation of related records..." check box for Table 2.

               On the layout where you will create new reocrds in Table 1, go to Layout Setup and select the OnCommitRecords trigger. Set it to run this script:

               Set Field [Table 2::_fkRecID ; Table 1::__pkRecID ]

               The first time around, this script step will create a new related record in table 2. Subsequent trips of this trigger--such as after editing a field in Table 1, will make no change to the data in Table 2 as it then is setting an existing field to a value that it already contains.

          • 2. Re: Populate ID field in multiple related tables
            JoePerry

                 Thanks PhilModJunk...your solution worked perfectly.