4 Replies Latest reply on Mar 8, 2014 6:08 AM by philmodjunk

    Relationship issue



      Relationship issue



           i am trying to create a new relationship data base for my job. I have 4 tables so far, Employee records, Payroll, Contract and Confirmed event. 

           Both the employees table and the payroll table are linked with each and they are working properly. if i create a new employee record a new record is also created on the payroll table with the same employee ID number. I wanted to recreate that same with Contracts and Confirmed events. how ever; when i create a new contract it does not create a new record on confirmed events. but if create a new record on confirmed events and add the address or any information it does create a new record on the contract's table. i tried to move the relation ship around but it has the same output. what am i doing wrong?


        • 1. Re: Relationship issue
          /files/8e76eaca82/Screen_Shot_2014-03-07_at_3.22.32_PM.png 600x568
          • 2. Re: Relationship issue

                 You have a number of issues with your relationships, how you named the fields and the options selected in your relationship between Contract and Confirmed events.

                 In order of importance:

                 a) Clear the "delete" check box for contracts unless you want to delete the related Contract record and all "confirmed event" records linked to that contract record--which seems a very unlikely way to set this up. The "Delete" option must be used with great care or deleting a single record can trigger a "cascading delete" that deletes many records from more than one table.

                 b) "Allow creation..." does not ever automatically create records in table 2 when you create a new record in the related table 1. It only makes it possible to create a new record in table 2 if certain layout objects are located on the Table 1 layout and you use them to enter data into fields from Table 2. The most common way to do this is via a portal to Table 2. But if you enter data into a field from Table 2 and a related record does not already exist, this will also create a new related record and in both cases, FileMaker will enter data into the relationship's match fields to link the new record in the related table to the current record on your layout.

                 From what you have posted, I would guess that "entering an address" was done on a layout based on Confirmed Events by entering text into a Contract::Contract Address field placed on that layout.

                 Note: if you have a portal to a table where the relationship allows creation for the portal's table, the illusion that a new record is created in the portal's table occurs because FileMaker puts a blank "add row" in the portal for adding new related records in the portal. But no actual record is created in the portal's table--you can switch to a layout  based on the portal's table to confirm--until you actually enter data in a field of this add row.

                 In both relationships, you are matching two pairs of fields in one table to two pairs of fields in the other. You only need one pair of fields if the _KP_Employees_ID and _KP_Contact_ID fields are correctly defined to enter unique ID's such as auto-entered serial numbers. Except for certain specialized circumstances that don't exist in the current file, the Payroll File Number and Event Record # fields should not be used in these relationships and probably should be removed from these tables as this data is stored a single time in the Employe_ID and Contract# fields and thus there is no need to store multiple copies of this data in the related tables.

                 _KP_Confirmed_Event_Number has a name that implies a different function that what you have defined. It should be renamed as _KF_Contract_ID to be consistent with how you have named your other fields. KP identifies a primary key, a field which uniquely identifies each record in the field in which it is defined. Since you matched this field to _KP_Contact_ID in Contracts, this field cannot uniquely identify records in Confirmed Events unless there is only one record in Confirmed events allowed for any given record in Contracts--which also seems very unlikely.

            • 3. Re: Relationship issue

              thank you for opening my eyes!

                   I see how the relations work! i must have a field from the other in to "automatically" create a new field on the other relation. for example if i have a yes no button for "Event Approved?" and that field is from confirmed events and i hit YES then that will create the record on the confirmed events table.

                   thank you or your other comments i have made the changes you suggested as they all make sense!


              • 4. Re: Relationship issue

                     There are also a number of other ways to create and link a record in the related table.