2 Replies Latest reply on Jun 18, 2014 9:17 AM by nihmbrisby

    Parent field = child field where many child records exist.

    nihmbrisby

      Title

      Parent field = child field where many child records exist.

      Post

           Hi

           I have a parent table of Contacts and a child table of activities.  They each have a table occurrence related in the standard primary to foreign key fashion.  I have defined the following fields in these tables:

             
      •           The Contacts table has 2 fields:           
                         
        1.                     Contacts :: Name
        2.                
        3.                     Contacts :: Status
        4.           
             

             
      •           The activities table has 2 fields           
                         
        1.                     activities :: name
        2.                
        3.                     activities :: type
        4.           
             

      Activities :: type may take 10 values (from a dropdown).  Contacts :: Status is directly related to activities :: type.  However, only some activities :: types can affect Contacts :: Status (lets say 5 of the possible 10 values can affect status).  The other 5 activities do not affect Contacts :: Status.  In fact, I would like Contacts :: Status to equal the most recent eligible activities :: type.  This is my solution thus far:

             
      1.           I have defined a new table occurrence based on activities.  I will call this table occurence activity_affect_Status.
      2.      
      3.           I relate Contacts  to activity_affect_Status in the following manner:
      4.      
      5.           Contacts :: primary_key_Contacts -----< activity_affect_Status :: foreign_key_Contact AND Contacts :: TYPE_MATCH ------- activity_affect_Status :: type
      6.      
      7.           I sort by activity_affect_Status :: timestamp in descending order.  This field is simply a timestamp auto-entered on creation.
      8.      
      9.           Contacts :: TYPE_MATCH is a field I have defined to Auto Enter the name of the 5 activity types (seperated by carriage returns) that can affect Contacts :: Status.
      10.      
      11.           I am using this method to simulate OR per filemaker's explanation of a multi-key field:  http://www.filemaker.com/help/11/fmp/html/glossary.html#1027937
      12.      
      13.           Finally, I simply define Contacts :: Status as a calculation field:
      14.      
      15.           Contacts :: Statusactivity_affect_Status :: type

            

           So far this works.  However as this is the most important function in my database,  I want to be sure I am not misguided in simply setting Contacts :: Status = activity_affect_Status :: type.  Why does this work?  Why doesn't it, say, make Contacts :: Status equal to all the values of the matching activity_affect_Status :: type's (ie a list)?  Is this a rock solid technique?  

           Does anyone know where it says in the filemaker documentation that setting a parent field equal to a child field ALWAYS returns a single value from the most recent child record (according to sort order).  

           Much appreciation for your attention!

           Edit: I'm confused by the post editor.  When I wrote my post, and when I view it in edit mode- it's nicely formatted with nested lists.  When it's post all by the highest level lists disappear (and the post is difficult to read.  Am I missing an option to, like, enable rich text.

           Edit: I've tried to reformat the post so it isn't such a mess.