Thought there may be other approaches, it is time to learn about the abs() function. (Absolute value)
You might try this.
1. Perform Find [ ]
2. Go To Layout [ "Event Find" (Dayback) ]
3. Sort Records [ Restore ; With dialog: off ]
4. Go to Record/Request/Page [ First ]
5. Set Variable [ $days ; Value: abs (DayBack::DateStart - Get ( CurrentDate ))
6. Set Variable [ $record ; Value: Get (RecordNumber ) ]
8. Go to Record/Request/Page [ Next ; Exit after last ]
9. Set Variable [ $diff ; Value: abs (DayBack::DateStart - Get ( CurrentDate )) ]
10. If [ $diff < $days ]
11. Set Variable [ $record ; Value: Get (RecordNumber ) ]
12. Set Variable [ $days ; $diff ) ]
13. End If
14. Exit Loop If [ $days = 0 ]
15. End Loop
16. Go to Record/Request/Page [ With dialog: Off ; $record ]
Yep, your script needs the abs() function as Bruce recommends.
But you could use a Pair of ExecuteSQL queries to get the ID's of the two records closest to the specified date instead of looping through them all. That might execute more quickly for you, though you have to convert dates returned by the query from SQL format back into FileMaker format before comparing the difference in days.
The SQL queries can use OrderBy to sort the resultSet and "Fetch First 1 Row Only" to get the date closest to the specified date that is either larger than or less than the specified date. (One query for each). That leaves you with just two dates to check for "closeness" to the specified date used in your query.
Thank you Bruce. That works perfectly.
This sounds interesting. Will this keep the found set intact?
And here's a demo with a similar technique but relying on the ListOf function.
As well as conditional formatting to immediately reveal which dates are less than or greater than.
Closest Date.fmp12.zip 73.1 K
Brilliant ! Thanks Bruce.
Why not put the calculation ABS (DayBack::DateStart - Get ( CurrentDate ) ) on the event records and then just sort ascending by that field, go to first record, and then re-sort by what you want the sort to actually be...
Why not put the calculation ABS (DayBack::DateStart - Get ( CurrentDate ) ) on the event records
That would have to be an unstored calculation and thus might sort very slowly with large found sets.
ExecuteSQL has nothing to do with found sets.
Can you please post real ExecuteSQL queries here? I ma interested to see your approach.
1 of 1 people found this helpful
Here's the "simple but brittle" version:
SELECT primarykey; eventdate FROM eventstable
eventdate > ?
ORDER BY eventdate DESC
FETCH FIRST 1 ROW ONLY
" ; "";""; Get ( CurrentDate ) ]
the second query would use
eventdate < ?
ORDER BY eventdate
Note that the "date" from event date will be text and in SQL format. They will need conversion to actual date values before you can subtract.
The complex but flexible format
Let ( [
@primarykey = SQLRef ( Events::PrimaryKey ; "fld" ) ;
@eventdate = SQLRef ( Events::EventDate ; "fld" ) ;
@eventstable = SQLRef ( Events::PrimaryKey ; "TO" ) ;
SQLa = "SELECT @primarykey; @eventdate FROM @eventstable
@eventdate > ?
ORDER BY @eventdate DESC
FETCH FIRST 1 ROW ONLY" ;
SQL = Substitute ( SQLa ;
[ "@primarykey" ; @primarykey ] ;
[ "@eventdate" ; @eventdate ] ;
[ "@eventstable" ; @eventstable ] )
ExecuteSQL ( SQL ; "" ; "" ; Get ( CurrentDate ) )
) // Let
This expression doesn't break if a table or field is later renamed.
I don't actually write the above expression I only type in the text to the right of SQLa = into a field and a system of custom functions creates the expression for me save for the table::Field references and any expressions needed as optional parameters in the actual ExecuteSQL call.
To see the custom functions used and the SQLRef function used in this expression, see the last example of: