AnsweredAssumed Answered

Parent field = child field where many child records exist.

Question asked by nihmbrisby on Jun 17, 2014
Latest reply on Jun 18, 2014 by 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.

Outcomes