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.
Employee 1, 1/1/2013, 1, 1B
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?