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

    Formula to show "Complete"?

    Sewlong

      Title

      Formula to show "Complete"?

      Post

      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"?
          SaterInc
            

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

           

          if IsEmpty(ordercompletefield) 

           if (field1) = "complete"

            else

             exit script

            end if

           if (field2) = "complete"

            else

             exit script

             end if

           if (field3) = "complete"

            else

            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"?
            philmodjunk
              

            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.

             

            Notes:

            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"?
              Sewlong
                

              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!