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

    Parent field = child field where many child records exist.



      Parent field = child field where many child records exist.



           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
        3.                     Contacts :: Status

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

      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.
      3.           I relate Contacts  to activity_affect_Status in the following manner:
      5.           Contacts :: primary_key_Contacts -----< activity_affect_Status :: foreign_key_Contact AND Contacts :: TYPE_MATCH ------- activity_affect_Status :: type
      7.           I sort by activity_affect_Status :: timestamp in descending order.  This field is simply a timestamp auto-entered on creation.
      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.
      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
      13.           Finally, I simply define Contacts :: Status as a calculation field:
      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.