AnsweredAssumed Answered

Finding closest date

Question asked by FileMakerNovice on Sep 4, 2013
Latest reply on Sep 5, 2013 by philmodjunk


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?