3 Replies Latest reply on Sep 5, 2013 6:14 AM by philmodjunk

    Finding closest date



      Finding closest date



           I've been attempting at creating a field in a table that will compare a date in said table with a list of dates in another table and then display the closest date in the second table.

           Main Table ----- Dates Table

           Main Table:  Has multiple fields including Employee ID, date, ID, Exception Code
           Dates Table:  Simply has Date, ID, QuarterCode and PeriodCode

           What I want is a value in Main Table that would return with the closest date available in the Dates Table.

           For Instance:
                Employee 1, 1/1/2013, 1, 1B
           Dates Table:  
           1, 1/25/2013
           2, 2/2/2013
           3, 1/1/2012
           4, 1/3/2013
           5, 2/1/2013

           MainTable.DesiredResult would result in 1/3/2013.

           I was able to create a script that would be able to loop through a list and provide the correct value but I would like the value to be a calculation field in the MainTable.

           Thanks in advance.

           Additional:  I think there could be a executeSQL option and regular fm function version.  Which would be best for performance?

        • 1. Re: Finding closest date

               If you define this relationship:

               Main::Date < Dates::Date

               and sort the related dates record via the sort setting in the Edit Relationship dialog into ascending order...

               then, from the record in Main, you can simply refer to Dates::Date to access the future date that is nearest to the date specified in Main.

          • 2. Re: Finding closest date

                 I like the clean easy fix.  Unfortunately, I would want the resulting date to be the closest one, not necessarily the next date in the future.

            • 3. Re: Finding closest date

                   That can still be done though your screen will be slower to update.

                   Define Main::gDate as a field with global storage.

                   Define a calculation field in Dates, cInterval as: abs ( Date - Main::gDate )

                   Use this relationship:

                   Main::anyField X Dates::anyField

                   And sort it in ascending order by cInterval

                   But if you have a lot of records in Dates, this relationship may be very slow to update and return a value. If you know that the interval will always be a certain number of days or less, you can set up a relationship that matches to a listed range of dates and this should then evaluate and update more quickly.