14 Replies Latest reply on Nov 6, 2009 10:26 AM by DLW-BPEX

    Status

    thong127

      Title

      Status

      Post

      Hi to Everybody,

       

      Just want to ask your help.

       

      I have a 3 fields Expiry Date & Retest Date & Status.

       

      In my status field I want to show that a certain product is "Active" or "Expired" based from Expiry Date or Retest Date. my calculation is

       

      Case ( Get ( CurrentDate )  > Expiry Date & Retest Date; "Expired";
             Get ( CurrentDate ) + 3650> Expiry Date; "Active"
      )

       

       

      but it doesnt give me my desired result. Kindly help me improve my calculation in the Status.

       

      Thank you very much.

       

      Best regards,

       

       

      thong

       

       

       

        • 1. Re: Status
          david_lalonde@d-cogit.ca
            

          The line that reads "Get ( CurrentDate )  > Expiry Date & Retest Date" should instead read "Get (CurrentDate) > Expiry Date or Get (CurrentDate) > Retest Date"

          • 2. Re: Status
            thong127
              

            Thanks David, but my problem now is the date under expiry dateretest date e.g 6/29/2010 and all the years ahead of time, The Status give me a remarks "expired".

             

            Thanks.

             

             

            Best regards,

             

            thong

             

             

            • 3. Re: Status
              david_lalonde@d-cogit.ca
                

              I am misunderstanding the issue. To be sure, this is what I understand:

               

              There are three fields: Expiry date is a date field; Retest Date is a date field; Status is a calculation field with a text result.

               

              Status should indicate "Expired" if either Expiry Date or Retest Date are past the current date. Otherwise, status should indicate "Active" if Expiry date is within the next ten years. Otherwise, status should be blank.

               

              Let me know what I misunderstood. 

              • 4. Re: Status
                thong127
                  

                Hi David,

                 

                You are right. Now, with the calculation of the status field is there something wrong because all the materials that did not reached their expiry or retest period the status remarks is expired it should be active.

                 

                Thanks.

                 

                thong

                • 5. Re: Status
                  david_lalonde@d-cogit.ca
                    

                  Then this very simple calculation does it:

                   

                  Case (
                  Get (CurrentDate) > Expiry Date; "Expired";
                  Get (CurrentDate) > Retest Date; "Expired";
                  "Active" 

                   

                  I got rid of the calculation for the "Active" status because a blank status did not seem to make sense.

                   

                  If this calculation does not work, you might want to verify that both Expiry Date and Retest Date are truly date fields. If, despite this check, it still does not work, then I am at a loss.

                  • 6. Re: Status
                    DLW-BPEX
                      

                    Just a thought, if I may chime in:

                     

                    When doing math on dates, are you treating them as numbers? For example, today is 733717; 6/29/2010 is 733952.

                    The GetAsDate function and expressing calculation results as numbers should give you consistent results.

                    • 7. Re: Status
                      david_lalonde@d-cogit.ca
                         FileMaker Pro's internal representation of a date is always a number. It makes no difference in calculations whether dates are used as date or as numbers for the purpose of comparing two date. The results are the same.
                      • 8. Re: Status
                        philmodjunk
                           As long as the Newbie user doesn't try something like datefield > "5/1/2009" in their expressions --which I've seen a time or three. :smileywink:
                        • 9. Re: Status
                          DLW-BPEX
                            

                          David,

                          Thank you for the assurance. I had always thought that. But now I am confused, also.

                          Just yesterday this same problem cropped up in an evaluation solution. A script tested whether Get ( CurrentDate ) > "11/30/2009". If true, the eval had expired. It worked properly until 11/5/2009, then gave a false result.

                          Changing the script to test Get ( CurrentDate ) > 733741 works as it should. Any idea why this is?

                           

                          Thanks,

                          David 

                          • 10. Re: Status
                            philmodjunk
                               Filemaker percieves "11/30/2009" as text, not a date. You could rewrite this as Date (11; 30; 2009) and then the expression should work.
                            • 11. Re: Status
                              thong127
                                

                              Hi David,

                               

                              I checked the Expiry Date & Retest Date they are both datefield. I try your suggested calculation but still the problem is there. all materials not supposed to be expired and retested the status field still saying "expired"

                               

                              Thanks,

                               

                              thong

                              • 12. Re: Status
                                DLW-BPEX
                                  

                                ...and which is of course exactly what was done here :)

                                Thanks, Phil.

                                I'm still trying to understand how FM interpreted Get ( CurrentDate ) as being > than 11302009 yesterday, but not a few days ago. Not that it matters, I guess, since it's the wrong way anyhow.

                                David 

                                • 13. Re: Status
                                  david_lalonde@d-cogit.ca
                                    

                                  It did not! "11/30/2009" is a string. String comparisons are in effect here. Get (currentdate) will automatically be coerced to a string, as if you did GetAsText (Get (CurrentDate)).

                                   

                                  Today, that would give "11/6/2009" > "11/30/2009", which is true, because in alphabetical order, 6 comes after 3 (the 3 of 30). 

                                  • 14. Re: Status
                                    DLW-BPEX
                                      

                                    That's what I was thinking, but I did not realize the "coerced to a string" part.

                                    So the script test actually failed on 1132009(?).

                                    Thanks, David