I am trying to alter a statement in a long script that sets a variable to the result of an exact match of a couple variables. Right now it's an exact date match, I want to have it to match if the dates are off by ~2 days, before or after. The current script is:
Let( [
UUID = sqlNameField( EIV_Eiv::A__UUID ; "" ; "" )
; BATCHID = sqlNameField( EIV_Eiv::A_BATCH_ID ; "" ; "" )
; PID = sqlNameField( EIV_Eiv::User_ID ; "" ; "" )
; STARTDATE = sqlNameField( EIV_Eiv::Start date ; "" ; "" )
; EIV = sqlNameTable( EIV_Eiv::A__UUID ; "" )
] ;
ExecuteSQL( "SELECT" & UUID & "," & BATCHID & "FROM" & EIV & "WHERE" & PID & "= ? AND" & STARTDATE & "= ? FETCH FIRST ROW ONLY" ; ¶ ; "" ; EIM_Eim__SELECTED::User_id ; EIM_Eim__SELECTED::Start_date )
)
I tried modifying to the ExecuteSQL line to:
ExecuteSQL( "SELECT" & UUID & "," & BATCHID & "FROM" & EIV & "WHERE" & PID & "= ? AND" & STARTDATE & "- ? >= -2 AND" & STARTDATE & "- ? <= 2 FETCH FIRST ROW ONLY" ; ¶ ; "" ; EIM_Eim__SELECTED::User_id ; EIM_Eim__SELECTED::Start_date )
To no effect (and FMP won't return any kind of errors that I can see). Am I tackling this the right way? I'm fairly new to FMP scripting and I'm amateurish with SQL.
I wouldn't try to do math on the dates columns in the SQL directly, but rather do it in the FileMaker parameters you pass. (Also, there seemed to be some spaces missing that I added)
Something like this:
ExecuteSQL( "SELECT " & UUID & "," & BATCHID &
" FROM " & EIV &
" WHERE " & PID & " = ? AND " & STARTDATE >= ? AND STARTDATE <= ? FETCH FIRST ROW ONLY" ;
"¶" ; "" ; EIM_Eim__SELECTED::User_id ; EIM_Eim__SELECTED::Start_date - 2;
EIM_Eim__SELECTED::Start_date + 2 )