Looking For A Closest Date Custom Function

[Ken Newell]

I've been trying to create a custom function to find the closest date between a date in a table and related dates in another table.

For Example

Table A

-------

2/9/2005

Table B - Related Dates

-------

1/14/2003

2/1/2004

3/3//2005

3/9/2005

In this case I would want 3/3/2005 returned as it is the closest date via the relationship in table B to the date 2/9/2005 in table A. The closest date can occur on the same day or before or after the date in Table A.

In Pre-FM7 days this was done via scripting and updated on a weekly basis. Tons of scripts not to mention process time when I have about 23 different tables finding the closest to each other.

I'm looking for a more dynamic method which is where the idea of having a custom function comes into play. I have created one that works but I want something even better.

My first attempt at this Custom Function Required 2 additional Relationships. Basically I set up a relationship from Table A to a table occurrence of Table B which was for dates same date and earlier to the Date in Table A. The Max value from this relationship is the closest test prior or the same date to the date in Table A. A second table occurrence of Table B which was for dates same date and later to the Date in Table A. The Min value from this relationship is closest test later or same day as the date in Table A. Using the ABS function I was able to determine which date is closer. Works, no scripting. Better than what I have in FM6 What is needed is 1 field to hold the result, 1 custom function, 2 addition table occurrences (1 for earlier and 1 for later test dates)

My second approach which is to create a value list of related dates and parse that out in a custom function to determine closest test date. I've created the value list without problem. So in the above example the value list returned is:

1/14/2003

2/1/2004

3/3//2005

3/9/2005

At this point I become stuck. So I will pull my head out of the sand and ask for help.

Any thoughts or help would be greatly appreciated. Thanks.

[Ken Newell]

I've been trying to create a custom function to find the closest date between a date in a table and related dates in another table.

For Example

Table A

-------

2/9/2005

Table B - Related Dates

-------

1/14/2003

2/1/2004

3/3//2005

3/9/2005

In this case I would want 3/3/2005 returned as it is the closest date via the relationship in table B to the date 2/9/2005 in table A. The closest date can occur on the same day or before or after the date in Table A.

In Pre-FM7 days this was done via scripting and updated on a weekly basis. Tons of scripts not to mention process time when I have about 23 different tables finding the closest to each other.

I'm looking for a more dynamic method which is where the idea of having a custom function comes into play. I have created one that works but I want something even better.

My first attempt at this Custom Function Required 2 additional Relationships. Basically I set up a relationship from Table A to a table occurrence of Table B which was for dates same date and earlier to the Date in Table A. The Max value from this relationship is the closest test prior or the same date to the date in Table A. A second table occurrence of Table B which was for dates same date and later to the Date in Table A. The Min value from this relationship is closest test later or same day as the date in Table A. Using the ABS function I was able to determine which date is closer. Works, no scripting. Better than what I have in FM6 What is needed is 1 field to hold the result, 1 custom function, 2 addition table occurrences (1 for earlier and 1 for later test dates)

My second approach which is to create a value list of related dates and parse that out in a custom function to determine closest test date. I've created the value list without problem. So in the above example the value list returned is:

1/14/2003

2/1/2004

3/3//2005

3/9/2005

At this point I become stuck. So I will pull my head out of the sand and ask for help.

Any thoughts or help would be greatly appreciated. Thanks.

[Ken Newell]

I've been trying to create a custom function to find the closest date between a date in a table and related dates in another table.

For Example

Table A

-------

2/9/2005

Table B - Related Dates

-------

1/14/2003

2/1/2004

3/3//2005

3/9/2005

In this case I would want 3/3/2005 returned as it is the closest date via the relationship in table B to the date 2/9/2005 in table A. The closest date can occur on the same day or before or after the date in Table A.

In Pre-FM7 days this was done via scripting and updated on a weekly basis. Tons of scripts not to mention process time when I have about 23 different tables finding the closest to each other.

I'm looking for a more dynamic method which is where the idea of having a custom function comes into play. I have created one that works but I want something even better.

My first attempt at this Custom Function Required 2 additional Relationships. Basically I set up a relationship from Table A to a table occurrence of Table B which was for dates same date and earlier to the Date in Table A. The Max value from this relationship is the closest test prior or the same date to the date in Table A. A second table occurrence of Table B which was for dates same date and later to the Date in Table A. The Min value from this relationship is closest test later or same day as the date in Table A. Using the ABS function I was able to determine which date is closer. Works, no scripting. Better than what I have in FM6 What is needed is 1 field to hold the result, 1 custom function, 2 addition table occurrences (1 for earlier and 1 for later test dates)

My second approach which is to create a value list of related dates and parse that out in a custom function to determine closest test date. I've created the value list without problem. So in the above example the value list returned is:

1/14/2003

2/1/2004

3/3//2005

3/9/2005

At this point I become stuck. So I will pull my head out of the sand and ask for help.

Any thoughts or help would be greatly appreciated. Thanks.

[comment]

Not a custom function, but I think it should work (and with 2 more fields it could work in prior versions as well):

Let ( [

higher = LookupNext( TableB::Date ; Higher ) ;

lower = LookupNext( TableB::Date ; Lower )

] ;

Case (

higher - Date < Date - lower ;

higher;

lower

)

The relationship is TableA::Date = TableB::Date.

[comment]

Not a custom function, but I think it should work (and with 2 more fields it could work in prior versions as well):

Let ( [

higher = LookupNext( TableB::Date ; Higher ) ;

lower = LookupNext( TableB::Date ; Lower )

] ;

Case (

higher - Date < Date - lower ;

higher;

lower

)

The relationship is TableA::Date = TableB::Date.

[comment]

Not a custom function, but I think it should work (and with 2 more fields it could work in prior versions as well):

Let ( [

higher = LookupNext( TableB::Date ; Higher ) ;

lower = LookupNext( TableB::Date ; Lower )

] ;

Case (

higher - Date < Date - lower ;

higher;

lower

)

The relationship is TableA::Date = TableB::Date.

[Ken Newell]

I will give it a try and see if I can get that to work. The relationship between the tables is based on two fields UID, Eye not date as the closest date may or may not be on the same date.

I have it working with 3 TOs but what I really want to do is cut down on the 2 additional TOs.

Thanks.

[Ken Newell]

I will give it a try and see if I can get that to work. The relationship between the tables is based on two fields UID, Eye not date as the closest date may or may not be on the same date.

I have it working with 3 TOs but what I really want to do is cut down on the 2 additional TOs.

Thanks.

[Ken Newell]

I will give it a try and see if I can get that to work. The relationship between the tables is based on two fields UID, Eye not date as the closest date may or may not be on the same date.

I have it working with 3 TOs but what I really want to do is cut down on the 2 additional TOs.

Thanks.

[ralph.nusser]

For the ones who like to study the subject with an example file.

Thank you comment

[ralph.nusser]

For the ones who like to study the subject with an example file.

Thank you comment

[ralph.nusser]

For the ones who like to study the subject with an example file.

Thank you comment

ClosestDate.zip

[Ken Newell]

I thought I would share my solution to creating a custom function for closest date since I floated the question out there a couple of days ago. I was able to get it to work so I would like to share it.

The solution uses a related value list as part of the custom function.

The related dates are stored in a related value list which the custom function uses to go through and determine which date is closest. The attachment example should be easy enough to follow and I tried commenting everything to make it easy to incorporate in your solutions.

The function is really two custom functions. This first is one that sets up the vairables. It is:

/*

==================================================

03/12/2005 1.0 KLN Original Version

==================================================

PURPOSE: To find the closest date in a table to a date in a related table. This is

accomplished by capturing the related dates to a value list for parsing out.

==================================================

USER INPUTS

TestDate = The Date field in the Current Table To Find Closest To

ValueList = Name of the Value List That Contains Related Dates

VAIRABLES

gList = Value List Dates To Be Checked

gClosestDate = Closest Test Date

gNum = Number Of Related Dates As Determined From Value List

OTHER CUSTOM FUNCTIONS USED

ClosestTestSub which is recurvise checking all dates.

==================================================

*/

Let ([

gList = ValueListItems ( Get ( FileName ) ; ValueLIstName );

gClosestDate = MiddleValues ( gList ; 1 ; 1 );

gNum = If ( IsEmpty ( glist ) ; 0 ; PatternCount ( gList ; "

[Ken Newell]

I thought I would share my solution to creating a custom function for closest date since I floated the question out there a couple of days ago. I was able to get it to work so I would like to share it.

The solution uses a related value list as part of the custom function.

The related dates are stored in a related value list which the custom function uses to go through and determine which date is closest. The attachment example should be easy enough to follow and I tried commenting everything to make it easy to incorporate in your solutions.

The function is really two custom functions. This first is one that sets up the vairables. It is:

/*

==================================================

03/12/2005 1.0 KLN Original Version

==================================================

PURPOSE: To find the closest date in a table to a date in a related table. This is

accomplished by capturing the related dates to a value list for parsing out.

==================================================

USER INPUTS

TestDate = The Date field in the Current Table To Find Closest To

ValueList = Name of the Value List That Contains Related Dates

VAIRABLES

gList = Value List Dates To Be Checked

gClosestDate = Closest Test Date

gNum = Number Of Related Dates As Determined From Value List

OTHER CUSTOM FUNCTIONS USED

ClosestTestSub which is recurvise checking all dates.

==================================================

*/

Let ([

gList = ValueListItems ( Get ( FileName ) ; ValueLIstName );

gClosestDate = MiddleValues ( gList ; 1 ; 1 );

gNum = If ( IsEmpty ( glist ) ; 0 ; PatternCount ( gList ; "

[Ken Newell]

I thought I would share my solution to creating a custom function for closest date since I floated the question out there a couple of days ago. I was able to get it to work so I would like to share it.

The solution uses a related value list as part of the custom function.

The related dates are stored in a related value list which the custom function uses to go through and determine which date is closest. The attachment example should be easy enough to follow and I tried commenting everything to make it easy to incorporate in your solutions.

The function is really two custom functions. This first is one that sets up the vairables. It is:

/*

==================================================

03/12/2005 1.0 KLN Original Version

==================================================

PURPOSE: To find the closest date in a table to a date in a related table. This is

accomplished by capturing the related dates to a value list for parsing out.

==================================================

USER INPUTS

TestDate = The Date field in the Current Table To Find Closest To

ValueList = Name of the Value List That Contains Related Dates

VAIRABLES

gList = Value List Dates To Be Checked

gClosestDate = Closest Test Date

gNum = Number Of Related Dates As Determined From Value List

OTHER CUSTOM FUNCTIONS USED

ClosestTestSub which is recurvise checking all dates.

==================================================

*/

Let ([

gList = ValueListItems ( Get ( FileName ) ; ValueLIstName );

gClosestDate = MiddleValues ( gList ; 1 ; 1 );

gNum = If ( IsEmpty ( glist ) ; 0 ; PatternCount ( gList ; "