2 Replies Latest reply on Jul 12, 2013 9:26 AM by Matty_1

    Status conundrum ...



      Status conundrum ...


           I have a one to many relationship between two tables, DispatchCentral (one) to DispatchJobs (many).  I'm looking to create a calculationfield that verifies all the status fields in DispatchJobs from the stand point of DispatchCentral.  If they are ALL empty I want the status to say "In Progress", if one to one short of all records have data in them I want to see "Partially Complete" and finally if all status fields have data I want to see "Complete"

           Is there a function can look across all it's related records instead of only the first?



        • 1. Re: Status conundrum ...

               Count ( DispatchJobs::Status )  // returns 0 if there are no related records or if status is empty in all related records

               Let ( C = Count ( DispatchJobs::Status ) ; c > 1 and c < count ( DispatchJobs::PrimaryKey ) )

               // Returns 1 (True ) If at least 1 but not all status fields have data. In place of primarykey, you can count any field that always contains data.

               Count ( DispatchJobs::Status ) = count ( DispatchJobs::PrimaryKey )
               // true only if the number of related records = the number of related records with data in the status field.

          • 2. Re: Status conundrum ...

                 Ah yes of course, very cool.  Thank you Phil!!