6 Replies Latest reply on Apr 15, 2015 4:32 PM by philmodjunk

    Status

    thong127

      Title

      Status

      Post

      Hi Everyone,

      I have 4 fields

      1. Expiry Date

      2. Retest Date

      3. Remarks ( Check box, Values are ( Quarantined, Released, Expired, Rejected, Used, R & D and Retest, Close to expiry)

      4. Status

       

      I want that In my Status field will show blank when my Expiry Date or Retest Date field are empty or when nothing is selected on my Remarks field;

      I want also in my Status field it will  show “Expired” if a certain material reached its Expiry date or Retest Date otherwise “Active”.

      I want also to appear in my Status Field that If I choose one of the value of my Remarks it will change the "Expired" or "Active" Status on my Status Field.

      Please find below the calculation that I'm working:

       

      Case ( not IsEmpty ( Remarks ) ; Remarks ;

        Get ( CurrentDate ) <  Expiry Date and Get ( CurrentDate ) < Retest Date ; "Expired" ;

                  not IsEmpty ( Expiry Date ) and  Expiry Date < Get ( CurrentDate ) ; "Expired" ;

                  not IsEmpty ( Retest Date ) and Retest Date < Get ( CurrentDate ) ; "Expired" ; "Active" )

       

      Thank you so much.

       

        • 1. Re: Status
          philmodjunk

          First, make sure that this is an unstored calculation field. A stored calculation or auto-entered calculation will not automatically update as time passes.

          Then, the second line of your case function seems incorrect as it returns a status of "expired" when both the expiry and retest dates are later than today. Looks like you need to reverse the inequality operators throughout your calculation.

          • 2. Re: Status
            thong127

            This is my new calc now:

             

            Case ( not IsEmpty ( Remarks ) ; Remarks ;

              Get ( CurrentDate ) >  Expiry Date and Get ( CurrentDate ) > Retest Date ; "" ;

                        not IsEmpty ( Expiry Date ) and  Expiry Date > Get ( CurrentDate ) ; "Expired" ;

                        not IsEmpty ( Retest Date ) and Retest Date > Get ( CurrentDate ) ; "Expired" ; "Active" )

            But its not working, when my retest and expired fields are empty and no remarks is chosen my status is blank, but when I put a greater than or less than the current date on my retest or expired field the status  is not showing.

            Thanks.

            • 3. Re: Status
              philmodjunk

              when my retest and expired fields are empty and no remarks is chosen my status is blank

              This would be the expected result if the "do not evaluate if all referenced fields are empty" check box is selected.

              when I put a greater than or less than date on the current on my retest or expired field the status  is not showing.

              Can't quite understand what you are saying there.

              Did you make this an unstored calculation?

              • 4. Re: Status
                philmodjunk

                I took another look and see that I was not paying close enough attention to the details.

                Shouldn't have told you to reverse the inequalities on the last two rows.

                not IsEmpty ( Expiry Date ) and  Expiry Date < Get ( CurrentDate ) ; "Expired" ;

                            not IsEmpty ( Retest Date ) and Retest Date < Get ( CurrentDate ) ; "Expired" ; "Active" )

                • 5. Re: Status
                  thong127

                  Sorry Phil, it was poorly written. 

                  It is Unstored calculation. 

                  I put it back my original inequalities in my last two rows, my problem now is when the retest or expiry date fields are empty my status is showing active. 

                  Please find below the case function.

                  Case ( not IsEmpty ( Remarks ) ; Remarks ;

                    Get ( CurrentDate ) <  Expiry Date and Get ( CurrentDate ) < Retest Date ; "" ;

                              not IsEmpty ( Expiry Date ) and  Expiry Date < Get ( CurrentDate ) ; "Expired" ;

                              not IsEmpty ( Retest Date ) and Retest Date < Get ( CurrentDate ) ; "Expired" ; "Active" )

                   

                  Thanks.

                   

                   

                  • 6. Re: Status
                    philmodjunk

                    I put it back my original inequalities in my last two rows, my problem now is when the retest or expiry date fields are empty my status is showing active. 

                    And is there a date in the other date field? If so what value?

                    Active could be the correct result when one of these fields are empty, the correct result would then depend on the value of the other fields.