Hi,

I have the a calculation field running the following:

ExecuteSQL ("SELECT MIN(checkins.date_due) FROM checkins  WHERE checkins.id_contact=? AND checkins.isComplete=0"; ""; ""; id)

If I tell the field that the calculation result is a number, then I appear to get the correct date back in the following format: YYYYMMDD, otherwise I get nothing or a ?.

However using this result in a comparison with say get(CurrentDate) does not work.

The strange thing is, that if I set the data formatting in the inspector to be 'general' i get YYYY-MM-DD, so it must know its a date somehow?

Any help about how to get fop to recognize the sql result as a fmp date would be greatly appreciated!

 

Try this:

STRVAL( MIN(checkins.date_due) )

or 

COALESCE ( MIN(checkins.date_due), '' )

These "convert" to FMP -date format of mm/dd/yyyy (or dd/mm/yyyy if non-US).

 

 

Sorry, "nested functions" (if one is an aggregate) do not seem to work well in eSQL. try this:

Let (
    [ $query = " SELECT MIN(checkins.date_due)
        FROM checkins 
        WHERE  checkins.id_contact=? AND checkins.isComplete=0"

    ; $result =ExecuteSQL ( $query 
        ; "" ; "" 
        ; id ) // returns yyyy-mm-dd

 

    ]; If ( $result ≠ "?" ; 
     
        Let ( 
            [ $y = Left($result; 4)
            ; $m = Middle($result; 6; 2) 
            ; $d = Right($result; 2) 
            ]; Date($m;$d;$y ) 
            )
            
        ; "?" )
) 

Thank you so much!

I was also in the process of trying a workaround where I simply have another field that stores the date as an integer and then do my sql query with this field...

Will try both solutions and see what happens

Thanks again!

Your suggested solution works great! Thanks for "let"ting me know