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:
- Contacts :: Name
- Contacts :: Status
The activities table has 2 fields
- activities :: name
- 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:
- I have defined a new table occurrence based on activities. I will call this table occurence activity_affect_Status.
- I relate Contacts to activity_affect_Status in the following manner:
- Contacts :: primary_key_Contacts -----< activity_affect_Status :: foreign_key_Contact AND Contacts :: TYPE_MATCH ------- activity_affect_Status :: type
- I sort by activity_affect_Status :: timestamp in descending order. This field is simply a timestamp auto-entered on creation.
- 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.
- 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
- Finally, I simply define Contacts :: Status as a calculation field:
- Contacts :: Status = activity_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.