4 Replies Latest reply on Jan 24, 2011 12:25 AM by RossanoIennarelli

    automatic change of a reference field

    RossanoIennarelli

      Title

      automatic change of a reference field

      Post

      I have two tables linked by a field is not unique, I would like that when I change the fields in a table automatically the reference field change in the other , as I do?
      thanks in advance

        • 1. Re: automatic change of a reference field
          philmodjunk

          Which field is being changed, the field that serves as a key field in the relationship or a data field that is looked up or displayed from the related field?

          I think this is what you are asking here:

          You have a relationship between two tables: Table1::KeyField1 = Table2::KeyField2

          You want a change to KeyField1 to update KeyField2 in all matching records in Table 2.

          If so, best solution is to design your relationships so as to make such updates unecessary, especially since a cascading update feature is not available in FileMaker like it is in some other products. (Even when you have this feature, it's best not to use it as the cascading update can trigger a significant delay as many, possibly hundreds or thousands of related records update.

          To do this in FileMaker requires scripting and the use of ScriptTriggers can make the process automatic.

          Use an OnObjectEnter trigger on KeyField1 that passes the current value of KeyField1 as a parameter to a script that stores that value in a global variable: Set Variable [$$OldValue ; Value:  Get ( ScriptParameter ) ]

          On OnObjectExit or OnObjectSave triggered script can then do this:

          Set Variable [$NewValue ; Table1::KeyField1]
          Set Field [Table1::KeyField1; Value: $$OldValue ]
          IF [ Not IsEmpty ( Table2::KeyField2 ) // there are related records to update ]
              Go To Related Record [Show only related records; From table: Table2; Using layout: "Table2" (Table2)]
              Replace Field Contents [no dialog; Table2::KeyField2 ; $NewValue]
              Go To Layout [original layout]
          End If
          Set Field [Table1::KeyField1 ; $NewValue ]

          Note: if this takes place on a multi-user system and another user has opened (locked) one of the related Table2 records, that record will not be updated by the Replace Field Contents step and an error will be thrown.

          • 2. Re: automatic change of a reference field
            RossanoIennarelli

            my situation is :

            table1::field1;field2;field3;...;field6

            table2::field1;field2;field3;....;field6

            the two table are linked by the field1 (the code of a plate) and the fields are the code of the Automotive pieces avaiable for that product. in the table 2 the code of the product is unique

            • 3. Re: automatic change of a reference field
              philmodjunk

              But what change are we talking about here?

              Do you want to change field2 in table2 and see the same change in table1? (Or the opposite)

              If so you should remove Fields 2 through 6 from one of the two tables and just use the one set of fields any where you need to see or edit this data. Since the data is only in one place, any changes made automatically appear on all layouts and reports.

              Here's an example.

              To replace fields 2 through 6 in table 1 with the related fields from Table 2:

              In every layout where these fields were found, enter layout mode and double click each such field from table 1 to bring up the specify fields dialog. Select Table 2 from the drop down at the top of the dialog. Then select the corresponding field from Table 2.

              Once you have done this in every layout where fields 2 through 6 were found, open Manage | Database | Fields and delete these field definitions.

              Note: It's very possible that a better design for your database is to place fields 2 through 6 in separate related records instead of all in the same record like you have.

              • 4. Re: automatic change of a reference field
                RossanoIennarelli

                unfortunately I can't do this because could be changes in every record in the table1, so I've decided to abandon this way