1 Reply Latest reply on May 10, 2013 10:13 AM by philmodjunk

    How do I generate a calculation that will return a result based on whether a field in any related...

    CarlisleLandel

      Title

      How do I generate a calculation that will return a result based on whether a field in any related record in another table has a particular value?

      Post

           How do I generate a calculation that will return a result based on whether a field in any related record in another table has a particular value?  

           I've got a "Projects" table and a "SubProjects" table, related by a ProjectID.  Each subproject has one of a range of status states from not started through various states of completion to complete.  

           I want to calculate the status of the project as a whole so that it reflects the least complete of the various sub-projects.

           I can think of one way to do it that is fairly clumsy, which is to make a bunch of relationships between the tables based on the various status states, i.e., one where they are linked by status value "not started" and another by "incomplete" and a third by "complete", and then then the project would be not started in the case where the count of related "not started" records was > 0; incomplete in the case that the count of related "incomplete" records was >0, etc. 

           But is there a simpler way?

           Thanks,

           Carlisle