AnsweredAssumed Answered

Finding closest date

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

Title

Finding closest date

Post

     Hello,

     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.

     Relationship:
     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:
     MainTable:  
          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?

Outcomes