1 Reply Latest reply on Jan 25, 2011 4:07 PM by philmodjunk

    Multiple Table Combination in Real Time

    hvanhoecke

      Summary

      Multiple Table Combination in Real Time

      Product

      FileMaker Pro

      Version

      11 Advanced

      Operating system version

      Mac OS 10.6.6

      Description of the issue

      I want to create a table that will create, update, and delete records based on criteria from two tables instantaneously.  I need the records in the table to update in real time when the other tables are updated.  I've created self-joining relationships with both tables to get the information to another table in real time but I can't figure out how to combine the records from more than one table in real time.

      Steps to reproduce the problem

      n/a

      Expected result

      Records from two separate tables can be viewed and looked up from one combined table that is updated in real time.

      Actual result

      Can't seem to get the self-joining relationships to accept information from two tables at once.

      Exact text of any error message(s) that appear

      n/a

      Configuration information

      n/a

      Workaround

      n/a

        • 1. Re: Multiple Table Combination in Real Time
          philmodjunk

          I may be misinterpreting your basic table structure here, but....

          Why would you use a "self joining relationship" for this? A self join relationship links a data source table to itself via a table occurrence of a different name. I don't see how that gets the job done here.

          TableSource1----MergeTable-----TableSource2

          TableSource1::_PK_Src1 = MergeTable::_FK_Src1
          TableSource2::_PK_Src2 = MergeTable::_FK_Src2

          Enable "Allow Creation of records via this relationship" for the source to merge relationships. Also enable deletion of records in Merge if a related source table is deleted. For other data fields, I wouldn't copy data from the source tables unless absolutely necessary. Instead I'd either add the source tables field directly to the layout or portal or use a calculation field (not an auto-entered calculation) so that source updates will automatically be reflected in the MergeTable and any layouts based on it.

          A simple script can create the related MergeTable records: Set Field [MergeTable::_FK_Src1 ; TableSource1::_PK_Src1]

          will create a new related record if one does not exist and can trigger relookups (if you have some you couldn't avoid) in cases where the record does already exist.

          Such a script can be triggered from the OnRecordCommit script trigger to automate the updates.