Status Warning

[Lola]

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

[lawaid]

I think I spot a syntax error ...

Case (

Get ( CurrentDate ) > Expiry Date [color:orange]and Get ( CurrentDate ) > Retest Date; "Expired";

Get ( CurrentDate ) + 3650> Expiry Date; "Active"

)

... if that helps.

[Lola]

Thanks Lawaid,

I tried your suggestion but, the dates in my retest date was not yet due for retest (eg 10/2/2010) in the status it says expired. kindly help me to figure out with this. thanks

thong

[LaRetta]

Well, if the Expiry date is 10/25/2009 and the Retest date is 10/1/2010 (and today is 11/11/2009) then it should show Active (as it sounds like you want it) using the corrected calculation you were given.

I suggest that you check and make sure the calculation is unstored in storage options. If it IS set to unstored and the calculation matches then we might need to go over your exact rules (and thinking) on what you want the results to be.

I have a feeling we are still missing one of your rules but I can't determine it without knowing what the data should display.

Edited November 11, 2009 by Guest
Added last sentence

[Lola]

Hi Laretta,

Thanks to your comment, I' dont know what is the real problem because in my database all years having 2014 as the year of expiry & retest the status says "Active" but for the year 2010 as the Expiry & retest date the status says "expired".

Is there a way that I can send or post my database to the forum?

Tanks.

thong

[LaRetta]

There sure is, Thong ...

Zip your file and create a post response. At the bottom you will see Manage Files. You can browse (to find your zipped file) and attach it, then save your post. We'll be happy to take a look at what is happening.

UPDATE: One thing ... both those date fields are true dates, right?

Edited November 11, 2009 by Guest
Added one more thing ...

[Fitch]

Since you have FileMaker Advanced, I'd pull up the Data Viewer and put in your formula like so:

Let( [

    expiry = GetAsDate ( "11/11/2010" ) ; 

    retest = GetAsDate ( "11/11/2010" ) ; 

    now = Get( CurrentDate ) ; 



    result = 

      Case (

        now > expiry and now > retest ; "Expired";

        now + 3650 > expiry ; "Active"

          )

      ] ; 



  result )

Constructed this way, you can experiment:

- replace the dates with any arbitrary date, or use your actual fields instead.

- replace the word "result" at the end with any piece of the formula to see if you're getting what you expected.

[Lola]

Yup..they are both true date fields...One thing that I discovered is that when I make a new record and I tried to put manually the date on the retest and expiry date field (what I mean is clicking form the calendar) the "status" is working. but on the date on retest & expiry that is there when I made the calculation for status...the status warning is not working. I tried also to save my file into excel and importing them, again I got same problem.

thong

[LaRetta]

I would try Tom's suggestion. But I also wonder about your date system formats (whether prior data is one date format and current records are another) or whether you have verified that the calculation is unstored yet.

It might be quickest to see your file.

[Lola]

Hi to everybody...

thanks for the insight...LaRetta, Fitch,lawaid... my problem at last was already solved.

Thanks Bro...

thong

[Lola]

Hi Laretta,

I have attached my simple database for your review, yesterday the "Find Active" & "Find expired" Button is working but unfortunately when I Changed my calculation in the "status field" which is my problem yesterday based from the suggested calculation of "lawaid" they are not working now, it says the operation cannot be performed because one or more of the relationships is invalid. but when I tried to omit the calculation in the "status" with this one is "Case ( Get ( CurrentDate ) > Expiry Date & Retest Date; "Expired";

Get ( CurrentDate ) + 3650> Expiry Date; "Active"

)

they are working but it doest give me my desired result.

the calculation of lawaid that is what I want to be.

thanks.

[LaRetta]

No file has been attached, Thong.

[Lola]

Hello...I will try to attach and send it again. Please let me know if my file is attached or not.

thanks.

Edited November 13, 2009 by Guest

[Lee Smith]

Take a look at this post for How to instructions of attaching a file. Link

[Lola]

thanks lee, I will do it again.

[Lola]

Hope this will work to help me out my problem in the Find expired & Find Retest button of my database.

Thank you.

thong

[LaRetta]

Well I see a few things:

You have NA in a date field. That is invalid and either was imported in or it originally was a text field because dates won't allow that entry. If you remove the NA, you cannot enter it again in that field. The Status appears to be correctly calculating according to my understanding of your rules.

Also, why are you attempting to use GTRR to find the Active and Expired? You have the relationships based upon the LayoutID but the field is indexed with simple 1. It is joined to both Status Active and Status Expired which will never match. Use straightforward find instead.

If there is something else I'm missing, please let me know and we'll work through it. :wink2:

[lawaid]

based from the suggested calculation of "lawaid" they are not working now, it says the operation cannot be performed because one or more of the relationships is invalid

Don't be so modest! I believe it was "your" calculation. I merely pointed out a syntax error. Let's give credit where credit is due. :

[Lola]

I'm very sorry.

Thanks

[LaRetta]

help me out my problem in the Find expired & Find Retest button of my database

Hey, lawaid, I believe we were way past the calculation and it was the SCRIPTS which wouldn't work because they were based upon GTRR from 1 (on the parent side) to Status on the other (which doesn't make a match).

I also believe that the issue stemmed from NA in the date fields (which should never be there). Overall, I didn't feel you were being blamed at all and, in fact, your calculation was correct (and was stated as correct).

Hey, Thong, if you have any further issues with this, let us know!

[lawaid]

Not a problem, and please forgive me for not checking the "logic" of your calc.

Probably all the better anyway, as you now have the-extremely-capable LaRetta on your post. Did she get you on the right track?

[Lola]

Hi to All...

Its working now, I changed my fields "status expired" & "status retest" into "Indexed",. My find Active & Find expired button is working now...thanks to all of you...have a nice weekend.

If I hurt someone/somebody I'm so sorry...

Thank you so much...