8 Replies Latest reply on Jan 23, 2015 11:49 AM by thong127






      Hello Everyone,

      Can you please share your expertise in building my status field calculation.

      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 “Expired” if a certain material reached their Expiry date or Retest Date otherwise “Active”  and when the Remarks is “Expired”, “Rejected” or “Used” it will show “Expired”

      Thank you so much in advance.

        • 1. Re: Status

          You'll have to substitute your actual Table::fieldname, for the variables listed as Table::Expiry Date, etc.  Spelling counts.  By the way, this will fail if multiple checkboxes are selected.  If you change to a dropdown, popup, or radio button, it will fail if someone shift + clicks and selects multiples.  Can should/multiple checkboxes be selected?  If not, put this auto enter calc into the the remarks field (first posted by the great Raybaudi):

          GetValue ( Self ; ValueCount ( Self  ) )

          This goes in the auto enter tab for the remarks field, uncheck the "Do not replace...." box

          Here's a script for your status field, set as calculation.

          Let ([

               @a=Table::Expiry Date;

               @b=Table::Retest Date;




          Case (@a >= @d or @b>=@d; "Expired';

                    @c = "Expired" or @c = "Rejected" or @c = "Used"; "Expired";




          edit:  Of course Phil's is better :)

          • 2. Re: Status

            The following calculation assumes that Expiry Date and Retest Date are fields of type Date, not text fields.

            Case ( Expiry Date > Get ( CurrentDate ) ; "Expired" ;
                        Retest Date > Get ( CurrentDate ) ; "Expired" ;
                        Not IsEmpty ( FilterValues ( Remarks ; List ( "Expired" ; "Rejected" ; "Used" ) ) ) ; "Expired" ;
                       "Active" // this is the "Else" result
                      ) // Case

            In storage options, make this an unstored calculation

            • 3. Re: Status

              Question for Phil, if you don't mind, does this line in your case statement evaluate as such? (pardon my newbie-ness):

              Not IsEmpty ( FilterValues ( Remarks ; List ( "Expired" ; "Rejected" ; "Used" ) ) ) ; "Expired" ;

              FilterValues.  Filter the values from this list- "Expired" ; "Rejected" ; "Used".  If it is not one of these values, filter values comes up empty (0).  If it is one of these values it returns true (1).

              Not isEmpty.  If FilterValues returns a value, Not isEmpty is true, and uses the result "Expired".  If  FilterValues returns (0) (false), Not isEmpty returns (0), and this line of the case statement evaluates to false, so the case statement uses the default value of "Active"

              • 4. Re: Status

                Hi Phil,

                I tried putting calculation into my Status field; even the retest date Or the Expiry date is 2014 the result is expired. and if the retest date year is 8/8/2015 and my remark is Released it shows also expired.

                I would appreciate if you can make it fix.

                Thank you so much.

                • 5. Re: Status

                  Are your expiry and retest date fields of type DATE? They cannot be of type text. Check them in Manage | Database | Fields.

                  Is your Remarks field of type TEXT?


                  Filtervalues ( list1 ; List2 ) returns a list of values found in both list1 and list2. I think of it as a "set intersection" function.

                  • 6. Re: Status

                    Yes, both fields their type is Date option/Comment is Indexed.

                    The calculation result is text; Then I checked the box Do not evaluate if all reference fields are empty and the storage option is Do not store calculation results.



                    • 7. Re: Status

                      My original calculation had the inequality operators reversed and they assumed that the two date fields were never empty.

                      Case ( not IsEmpty ( Expiry Date ) and  Expiry Date < Get ( CurrentDate ) ; "Expired" ;
                                  not IsEmpty ( Retest Date ) and Retest Date < Get ( CurrentDate ) ; "Expired" ;
                                  not IsEmpty ( FilterValues ( Remarks ; List ( "Expired" ; "Rejected" ; "Used" ) ) ) ; "Expired" ;
                                 "Active" // this is the "Else" result
                                ) // Case

                      • 8. Re: Status

                        Hi Phil,

                        Thank you so much. Your help is highly appreciated.