Date Calcs

[James Gill]

I am trying to create a function that will tell me whether a date criteria has been met given some data inputs.

I just want a basical True/False to be spit out if the criteria is not met, but I'm getting wonky results, especially with date ranges that extend from one month or year to another.

Let ( [



StartDate = "1/3/2010" ; Calc = "<" ; CheckDate = "1/10/2010" ;

Duration = 3 ;

Units = "Days"

] ; 

Case ( 

         Units = "Days" ; If ( Evaluate ( Date ( Month ( StartDate ) ; Day ( StartDate ) + Duration ; Year ( Startdate ) ) & Calc & Date ( Month ( CheckDate ) ; Day ( CheckDate ) ; Year ( CheckDate ) ) ) ; "Days True" ; "Days False" ) ;

         Units = "Months" ; If ( Evaluate ( Date ( Month ( StartDate ) + Duration ; Day ( StartDate ) ; Year ( Startdate ) ) & Calc & Date ( Month ( CheckDate ) ; Day ( CheckDate ) ; Year ( CheckDate ) ) ) ; "Months True" ; "Months False" ) ;

         Units = "Years" ; If ( Evaluate ( Date ( Month ( StartDate ) ; Day ( StartDate ) ; Year ( Startdate ) + Duration ) & Calc & Date ( Month ( CheckDate ) ; Day ( CheckDate ) ; Year ( CheckDate ) ) ) ; "Years True" ; "Years False" )

         ; 

"Error" ) 

& "¶Human Friendly Calculation: CheckDate ( " & CheckDate & " ) must be " & Calc & " " & Duration & " " & Units & " From StartDate ( " & StartDate  & " ) in order to pass validation"  )

I'm still a relative noob at FMP so if anybody can point to where I may be going wrong that would be appreciated.

[comment]

You have some issues with data types here. First, "1/3/2010" is text, not a date. This flaw happens to be "cured" by using the Month(), Day() and Year() functions - since these expect a date as the parameter. So:

Month ( "1/3/2010" )

is actually evaluated as:

Month ( GetAsDate ( "1/3/2010" ) )

This will return 1 - IF the date format in use is m/d/y.





The Evaluate() function, OTOH, expects a text parameter. In your example, the supplied text is:



"1/6/2010<1/10/2010"



Again, these are not dates, and the evaluation proceeds as follows:

1/6/2010 < 1/10/2010 



=



.0000829187396352 < .0000497512437811



=



False

[James Gill]

I guess I'm a little confused then. Evaluate requires text yet I need to perform these calcs on dates, so is evaluate the right choice for me or is there another option out there that will fit the need better?

is it as easy as adding GetAsDate ( "text" ) to the first portion of the Let statement for the dates I'm passing in?

[comment]

is evaluate the right choice for me or is there another option out there that will fit the need better?

Hard to say - you haven't told us what the need is. Can one assume that StartDate, Calc, Duration, Units and CheckDate are fields? If so, what are the choices you are willing to allow in the Calc field? If there are only a few, I would try to eliminate Evaluate() altogether and do a Case() on those choices. Otherwise you could try something like =

Let ( [

endDate = Case ( 

Units = "Days" ; Date ( Month ( StartDate ) ; Day ( StartDate ) + Duration ; Year ( Startdate ) ) ; 

Units = "Months" ; Date ( Month ( StartDate ) + Duration  ; Day ( StartDate ); Year ( Startdate ) ) ;

Units = "Years" ; Date ( Month ( StartDate ) ; Day ( StartDate ); Year ( Startdate ) + Duration ) 

) 

] ;

Evaluate (

"GetAsDate ( " & Quote ( endDate ) & " ) " & Calc & " GetAsDate ( " & Quote ( CheckDate ) & " ) " 

)

)

This should return 1 (True) or 0 (False).

[bruceR]

You might look into these functions; including the Date function:

Date

Purpose: Returns the calendar date for month, day, and year.

Format: Date(month;day;year)

http://fmhelp.filemaker.com/fmphelp_11/en/html/func_ref1.31.12.html#1028277

[James Gill]

Hard to say - you haven't told us what the need is. Can one assume that StartDate, Calc, Duration, Units and CheckDate are fields?

Only Calc, Duration, and Units are user definable fields. Checkdate and Startdate are both based off of a related record but for the sake of creating and checking to make sure that the formula works correctly, I have made them enterable fields.

If so, what are the choices you are willing to allow in the Calc field? If there are only a few, I would try to eliminate Evaluate() altogether and do a Case() on those choices. Otherwise you could try something like =

Let ( [

endDate = Case ( 

Units = "Days" ; Date ( Month ( StartDate ) ; Day ( StartDate ) + Duration ; Year ( Startdate ) ) ; 

Units = "Months" ; Date ( Month ( StartDate ) + Duration  ; Day ( StartDate ); Year ( Startdate ) ) ;

Units = "Years" ; Date ( Month ( StartDate ) ; Day ( StartDate ); Year ( Startdate ) + Duration ) 

) 

] ;

Evaluate (

"GetAsDate ( " & Quote ( endDate ) & " ) " & Calc & " GetAsDate ( " & Quote ( CheckDate ) & " ) " 

)

)

This should return 1 (True) or 0 (False).

I will be limiting the "calc" field to values that can be used in the Filemaker Calc Engine (<,>,=, etc...)

After looking at the above formula I think I know the problem with mine. It looks as if your entire Evaluate string is contained within quotes and mine is not. I've never used the Evaluate function before so I think that's where I was going wrong.

[comment]

It looks as if your entire Evaluate string is contained within quotes and mine is not.

Not really. My string to evaluate will come up as:

GetAsDate ( "1/3/2010" ) > GetAsDate ( "1/10/2010" )

I will be limiting the "calc" field to values that can be used in the Filemaker Calc Engine (<,>,=, etc...)

The problem is with the "etc...".

[James Gill]

Not really. My string to evaluate will come up as:

GetAsDate ( "1/3/2010" ) > GetAsDate ( "1/10/2010" )

The problem is with the "etc...".

Hah, I only meant the "etc..." to placehold the other Calc functions (Greater than or equal to, less than or equal to, equals...)

Anywho, I have the calc working as intended now so thank you for the help!