Why doesn't this work???

[John Chamberlain]

I have a requirement that states that an employee must have a chest x-ray every three years, or a TB skin test every year. I have tried to create a flag field that will produce a check mark if neither condition is met:

Case((IsValid(X~Ray Date) and X~Ray Date - (Today-1096) >= 0),

(IsValid(T.B. Test Date) and T.B. Test Date-(Today-366) >= 0),

"", "?")

For some reason, if both date fields are empty, I get a null return. If I define the flag field with either component alon, the check mark appears when the field is empty or out of date (e.g.) if I use Case((IsValid(X~Ray Date) and X~Ray Date - (Today-1096) >= 0) "", "?") and the X~Ray Date field is empty or more than three years ago, I get the flag - same thing if I do it for the TB test date).

I have the "Do not evaluated if all fields are empty" option turned off, and have tried it with the field stored or unstored. All to no avail.

Can someone give me a clue here, please?

[slstrother]

Change your calc slightly

Case((IsValid(X~Ray Date) and X~Ray Date - (Today-1096) >= 0) and

(IsValid(T.B. Test Date) and T.B. Test Date-(Today-366) >= 0),

"", "?")

[Ugo DI LUCA]

John,

Indy! , why not ?

Use the Status(CurrentDate) function instead of the Today function and make the calculation unstored.

The Today function is "garbage" for what you're looking for IMHO. You may have problem occur with the Today function...

[IdealData]

Watch out with those "special" characters in your field names.

FM can't perform some calcs if you use characters like -+/* etc..

[John Chamberlain]

Ugo -

I tried your idea, but it still does not work. I even put in a third case to cover both fields being empty, and that does not work either. Calc now reads:

Case(IsValid(X~Ray Date) and X~Ray Date - (Status(CurrentDate)-1096) >= 0,

IsValid(T.B. Test Date) and T.B. Test Date-(Status(CurrentDate)-366) >= 0,

IsEmpty(T.B. Test Date) and IsEmpty(X~Ray Date),

"", "?")

I also tried just the third case by itself

i.e. Case(IsEmpty(T.B. Test Date) and IsEmpty(X~Ray Date),

"", "?")

This does nothing even when both fields are empty

I know I'm missing something, but what?:?:???

[John Chamberlain]

OOPS! that's stupid, of course it does not work when both fields are empty - forget the third case - but I'm still so CONFUSED!

[Ugo DI LUCA]

HI,

You're confused ?

Is it working or not...?

[John Chamberlain]

No, the thing does not work.

The reason I'm confused is that either one of the tests works by it self. That is to say, if the field is empty, or if the date in the field is too far in the past, then the caclulation returns a check mark; if the field is within bounds it returns a null. BUT this is only true if I use two different calculations. There does not appear to be any way to combine them into a single test.

Really, this is not a big thing, because I can create two tests, place the fields side by side, and instruct the user to ignore the result unless there are two check marks. But I hate things I can't understand!

Oh well,

Regards,

John

[CobaltSky]

Hi John,

The problem you are experiencing is because you are not using the correct syntax for multiple texts in a Case( ) statement. The correct fornat is:

Case(test1, result1true, test2, result2true, test3, result3true, resultfalse)

Whereas you have test1 immediately followed by test2 without a result interposed.

The compound logic you require (assuming you want an 'X' returned when the criteria are met) will look more like this:

Case(IsValid(X~Ray Date) and X~Ray Date - (Status(CurrentDate) - 1096) <= 0, "X",



IsValid(T.B. Test Date) and T.B. Test Date-(Status(CurrentDate) - 366) <= 0, "X",



IsEmpty(T.B. Test Date) and IsEmpty(X~Ray Date),



"X", "")

I think you will find that once the Case( ) statement is structured in this way, it will work regardless of whether you are using Today or Status(CurrentDate). Using the latter will speed up database start-ups, but at the cost of speed of searches on the calc result flag field (as the calc will then be unindexed) so it is simply a trade-off, and you should use whichever offers least inconvenience to your workflow and usage requirements.

[John Chamberlain]

Hi Ray -

I'm afraid your way does not work either. What I want is a null return if either of the two dates is within their range (365 for skin test, or 1096 for the X-Ray). I modified your formula like this:

Case(IsValid(X~Ray Date) and X~Ray Date - (Status(CurrentDate) - 1096) >= 0, "",

IsValid(T.B. Test Date) and T.B. Test Date-(Status(CurrentDate) - 365) >= 0, "",

IsEmpty(T.B. Test Date) and IsEmpty(X~Ray Date), "X", "")

What happens now is that, if there is any date (in or out of range) in either field, I get a null return. Apparently it fails the first and second tests, but (since there is something in one field or the other) it passes that. As this stands now it will fall through as long as there is any date in either field.

[rivet]

Here is a solution using ones and zeros, see attachment.

(IsValid(skin) * Status(CurrentDate)-skin > 365) +

(IsValid(xray) * Status(CurrentDate)-xray > 1096)>0

date_range.zip

[CobaltSky]

Hi John,

Given what you are saying, you will get closer with a logic that incorporates the 'or' logic into a single Case statement test:

Case((not IsEmpty(X~Ray Date) and Status(CurrentDate) - X~Ray Date < 1095) or

(not IsEmpty(T.B. Test Date) and Status(CurrentDate) - T.B. Test Date < 365) or

(IsEmpty(X~Ray Date) and IsEmpty(T.B. Test Date)), "", "X")

or, if you'd like to use a slightly condensed boolean construct (along the lines suggested by rivet, you could also use:

Case(((not IsEmpty(X~Ray Date)) * (Status(CurrentDate) - X~Ray Date > 1094)) +

((not IsEmpty(T.B. Test Date)) * (Status(CurrentDate) - T.B. Test Date > 364)), "X")

I've tested both these here and they are both producing the result you are looking for, if I understand you correctly.