13 Replies Latest reply on Apr 25, 2017 5:40 PM by philmodjunk

    Go to record with closest date

    mjharding

      Hello.

       

      You help is greatly appreciated.

      I am working on Filemaker 15.

      The records are calendar events with a start and end date.

       

      My goal is to go to the record in a found set who's start date is the closest to the current date - either in the past or future.  The frustrating part is my script works sometimes.  Very unFilemaker like.   Often, but not always, the record it goes to is not the one closest to the current date in the found set.   The line that doesn't always work is #9.    It doesn't always correctly determine if the calculated number of days is less then the previously set variable $days, even though my formula for that calculating the number of days - [  $days ; Value: Substitute (DayBack::DateStart - Get ( CurrentDate ); "-" ; "" )-  is always correct.

       

      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: Substitute (DayBack::DateStart - Get ( CurrentDate ); "-" ; "" )

      6.  Set Variable  [  $record  ; Value:  Get  (RecordNumber ) ]

      7.  Loop

      8.         Go to Record/Request/Page  [ Next  ;  Exit after last ]

      9.         If  [ Substitute (DayBack::DateStart - Get ( CurrentDate ); "-" ; "" )  <  $days

      10.          Set Variable  [  $record  ; Value:  Get  (RecordNumber ) ]

      11.          Set Variable  [  $days ; Value: Substitute (DayBack::DateStart - Get ( CurrentDate ); "-" ; "" )

      12.       End If

      13.       Exit Loop If  [  $days = 0  ]

      14.  End Loop

      15.  Go to Record/Request/Page  [ With dialog: Off  ;  $record  ]

       

      Thank you !!

        • 1. Re: Go to record with closest date
          BruceRobertson

          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 ) ]

          7.  Loop

          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  ]

          • 2. Re: Go to record with closest date
            BruceRobertson

            Help for the abs() function:

             

            FileMaker Pro 15 Help

            • 3. Re: Go to record with closest date
              philmodjunk

              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.

              • 4. Re: Go to record with closest date
                mjharding

                Thank you Bruce.   That works perfectly.

                • 5. Re: Go to record with closest date
                  mjharding

                  Thanks Phil.

                   

                  This sounds interesting.   Will this keep the found set intact?

                  • 6. Re: Go to record with closest date
                    BruceRobertson

                    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.

                    • 7. Re: Go to record with closest date
                      mjharding

                      Brilliant !  Thanks Bruce.

                      • 8. Re: Go to record with closest date
                        kluchnik

                        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...

                        • 9. Re: Go to record with closest date
                          philmodjunk

                          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.

                          • 10. Re: Go to record with closest date
                            Siroos Jafary

                            Can you please post real ExecuteSQL queries here? I ma interested to see your approach.

                            • 11. Re: Go to record with closest date
                              philmodjunk

                              Here's the "simple but brittle" version:

                               

                              ExecuteSQL ("

                              SELECT primarykey; eventdate FROM eventstable

                                 WHERE

                                      eventdate > ?

                                  ORDER BY eventdate DESC   

                                  FETCH FIRST 1 ROW ONLY

                               

                              " ; "";""; Get ( CurrentDate ) ]

                               

                              the second query would use

                               

                              WHERE

                                      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.

                              1 of 1 people found this helpful
                              • 12. Re: Go to record with closest date
                                Siroos Jafary

                                Beautiful, Thanks.

                                • 13. Re: Go to record with closest date
                                  philmodjunk

                                  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

                                                     WHERE

                                                          @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:

                                   

                                  Adventures in FileMaking #2--enhanced value selection