AnsweredAssumed Answered

calc field based on child table

Question asked by firth5 on Jul 31, 2013
Latest reply on Aug 2, 2013 by philmodjunk

Title

calc field based on child table

Post

     I'm going to describe what I need, then explain what I think needs to happen architecturally.

     I have a ASSET Table and a child GOAL Table.  There are many Goals per one Asset.  Each Goal has a status field, with many possible results.

     I want to have a "Status" field for each Asset that displays a calculated result based on the various statuses of child Goals.

     For example:  If any of the child Goals are "In Progress", I want the parent status to read "In Progress."  If any of the child Goals are "Pending AA" or "Pending BB" or "Pending CC," I want the parent status to read "Pending."

     ---

     If I put the calc field in the parent table, it will only look at the first related record, correct?  Therefore, I think I should create a 3rd table that will house the calc field. 

     I'm also looking to display the ASSETS table in list view if possible, and will need to generate reports based on Asset Status, so speed is an issue.  What is the most sound way to approach this problem?

     Please advise.  Thanks in advance.  :)

Outcomes