Jump to content
Salesforce and other SMB Solutions are coming soon. ×

closest date in a list


Recommended Posts

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.



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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.





based on relationship, find next higher value


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

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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.



Issue Number [serialized number to set issue numbers]

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use