closest date in a list

[DrJ]

I have a master db of publication issues (by serial ID) and their dates. all fall on the first or fifteenth of the month, with two skipped issues every year. I need to determine the next issue using the Today function for a global and then I could use that to find which subscription reminder letters have to be sent out, first, second or third. I have already figured out how to find those dates in the subscriber db. I just can't figure out how to get the next publication date since at least two issues are more than 15 days apart.

 

i.e.

issue date | issue number

12/15/2007 | 452

1/15/2008 | 453

2/1/2008 | 454

2/15/2008 | 455

 

So, if today is 12/16/2007, how do I get issue number 453 in the global box so I can also get the corresponding pub date? If today is 1/16/8 i should get 454.

 

Generically, i need to find which number/date in a list is the next up from the global number/date returned by the Today function.

 

I'm using a mac G5 w/FMP6

 

Thanks for any help.

[mfif]

Assuming your publication always comes out every single fortnight of the year including New Year's Day:

 

Case (

Day ( Get (CurrentDate) )

Date ( Month (Get (CurrentDate) ) ; 15 ; Year ( Get (CurrentDate) ) ;

 

Month ( Get (CurrentDate) ) 12 ;

Date ( Month ( Get (CurrentDate) ) + 1 ; 1 ; Year ( Get (CurrentDate) ;

 

Date ( 1 ; 1 ; Year ( Get (CurrentDate) ) + 1 )

 

But just in case you skip an issue or two, how about a startup script that searches in the Issues table for Date > Today, sorts by date, then gets the date for the first record and puts it in whatever global field you need? I'm sorry I can't help you with the details, I don't have FM6 installed any longer, but it shouldn't be too hard.

[DrJ]

Thanks mfif,

 

I actually figured out how to get the current issue date with a self-relationship in the db with all the dates listed. pick any field, relate to self, the do a lookup to find Today and return the next highest find. I must have been asleep.

 

Relationship

field:(=today)::field:PubDate

lookup

based on relationship, find next higher value

 

-- had to force relookup for the Today field, but it worked fine.

[doughemi]

The Today function is obsolete, even in FM6. It is a vestigial remainder from (I think) FM3.

 

If you have a lot of records (or a lot of calculations using Today), you will be amazed at the performance increase you get by substituting Status(CurrentDate) for Today.

 

--Doug

[Jack Rodgers]

I would humbly suggest that you not hardwire this into code or calculated field since your schedule may change in the future.

 

Instead create a table of publishing dates and other info and use that.

 

You can fill in the dates using a script to create the records or do it manually.

 

Date

Issue Number [serialized number to set issue numbers]

[DrJ]

Thanks for the tip on "Today", it was right on.

 

And Doug, I set up a master issue/date db in the first place, but couldn't work out how to come up with a "current issue" date. I have since figured that out using a self-relationship. Once I got the current issue date, I could find the subscribers who needed reminder letters for the next issue.

Thanks for your input.

[DrJ]

Pardon me, Jack -- not Doug. Thanks for your input.