help setting field based on date

[innodes]

I need to calc a field based on the current date. Here is the criteria:

If the date_active > today then "preliminary"

If the date_active < or = today AND the date_owo = 00/00 then "active"

If the date_active < or = today AND the date_owo > today then "active"

If the date_active < or = today AND the date_owo < or = today then "inactive"

Here is what I have, but it's not working right.

Case (

date_active > Get ( CurrentDate ) ; "preliminary" ;

and ( date_active ≤ Get ( CurrentDate ) ; date_owo = "00/00" ) ; "active" ;

and ( date_active ≤ Get ( CurrentDate ) ; date_owo > Get ( CurrentDate ) ) ; "active" ;

date_owo < Get ( CurrentDate ) ; "inactive" ;

)

thanks in advance

[comment]

In Filemaker, 'and' is an operator, not a function. Try:

Case (

date_active > Get ( CurrentDate ) ; "preliminary" ;

date_active ≤ Get ( CurrentDate ) and date_owo = "00/00" ; "active" ;

date_active ≤ Get ( CurrentDate ) and date_owo > Get ( CurrentDate ) ; "active" ;

date_owo ≤ Get ( CurrentDate ) ; "inactive"

)

This could be shortened to:

Case (

date_active > Get ( CurrentDate ) ; "preliminary" ;

date_owo = "00/00" or date_owo > Get ( CurrentDate ) ; "active" ;

"inactive"

)

NOTE: If date_owo can be "00/00", then date_owo is NOT a date field. And if date_owo is not a date field, then the comparison with current date will not work.

[innodes]

Will it work if the owo is left blank? That is a termination date that will not be filled in until that decision for that particular product is made.

thanks

[comment]

I believe so (if I understand the underlying logic correctly).

[innodes]

Thanks,

I'm updating the records now, At this point it is showing all files as inactive. I am going to play with it a bit more, but if you have any thoughts they are welcome.

I have the following:

Case (

date_active > Get ( CurrentDate ) ; "preliminary" ;

date_owo > Get ( CurrentDate ) ; "active" ;

"inactive"

)

They have just asked me to add a "cancelled" designation that would be triggered manually. What would be the best way to set that up? I was thinking about adding a step field that would house the calc. above, and then in the status field do a new case that would look for the "cancelled" input, and if not then revert to the step field with the "active" "inactive" results. Does that seem right, or is that too cumbersome?

thanks,

mike

Edited October 9, 2008 by Guest

[comment]

The Case() function returns the result attached to the FIRST TRUE test. So if I understand correctly, you can simply add this as your first (overriding) test:

Case (

Cancelled ; "cancelled" ;

date_active > Get ( CurrentDate ) ; "preliminary" ;

not date_owo or date_owo > Get ( CurrentDate ) ; "active" ;

"inactive"

)

'Cancelled' can be a number field switched between 1 and 0/empty, or (preferably) a date field.

[innodes]

thanks,

so . . . I could set up the cancel as a date field, which would be good, but how would I trigger that in the case statement?

[comment]

It's already in there.

[innodes]

It's not working though. While I was testing, as a text field, with "yes" typed in, it would show "cancelled", but as a date field, it just reverts to "inactive", "active" or "preliminary".

What did I do wrong?

[comment]

Can you post a copy of your file (with only the relevant fields, please)?

[innodes]

good morning. My error, I missed something. It is working great!

thanks!

have a great weekend!