3 Replies Latest reply on Nov 18, 2009 6:07 AM by Sewlong

    Formula to show "Complete"?



      Formula to show "Complete"?


      FM Pro 10 Windows Vista


      have a quick formula question I hope you can help me with I have 1 table for the (order) and another with the (project) for the orders

      I want to have a field on the (order) table that shows "complete" when all of the related (project::status) fields are marked "complete".

      So what would the fomula be for the (order::status) table that tells me when ALL of the related projects (project::status) are marked complete. Any information is appreciated! Thanks Justin  

        • 1. Re: Formula to show "Complete"?

          I'm thinking make a script trigger for onlayoutload tied to  a script that does something like


          if IsEmpty(ordercompletefield) 

           if (field1) = "complete"


             exit script

            end if

           if (field2) = "complete"


             exit script

             end if

           if (field3) = "complete"


            exit script

            end if

          SetField(ordercompletefield) = "complete"

           end if





          to access script trigger edit a layout, goto layout menu on main menu bar, then layout setup, and script trigger tab

          hope that gets you going in the right direction.  sure there is an easier way though, I overuse if statements

          The above mentioned will only edit one record i think, so you'll have to work that into a loop statement to go through all your records.

          • 2. Re: Formula to show "Complete"?

            A script may not be needed here. If you have one status field in each project record, add a calculation field called cCompleteFlag: If(status = "complete"; 1 ; 0 )


            Then put the following expression in a calculation field in your order table:


            If( Count(Projects:: ProjectID) > 0 and Count (Projects:: ProjectID) = Count(Projects::cCompleteFlag); "Project Complete" ; "" )


            Place this field on your layout to see which orders have all completed project records.



            If the status field is always blank unless it's "complete" you can count that field and don't need cCompleteFlag.

            If you have multiple status fields in a project record, you can make cCompleteFlag into an expression that returns 1 only if all such fields contain "complete".

            • 3. Re: Formula to show "Complete"?

              Thank you both!

              I went with the calculation method. and i have never understood the "If" function, but this helped a lot!

              I ended up with these  formulas:

              Project calculation - If ( Proj_Status="Complete " ; 1 ; 0 ) 

              Order calculation -  If( Count(PROJECT::__ID_Project) = Sum(PROJECT::Proj_Status_Flag); "Complete" ; "In Progress" )

              and it works.

              Thanks again!