Need suggestions for comparing a date to current date
2 database files, master records in one related events in the other.
Need to print a summary of any upcoming events based on whether or not they take place on the current day or in the future. No records with all events in the past are included in the summary. There are between 5 and 15 records with current or future events at any time.
Summary shows each master record and its information as well as all related events, typically between 10 and 20 events total.
We have over 40,000 events in the related events file.
I set a calculation field for the events file : If ( Event date >= Get(current date) ; Event date ; "" ). This only works for the time that the calculation was last run for any unchanging records of course. I then have a field in the master file which pulls the date from the Events calculation by calling for a list of related records from the Events and if there is any content to take the 1st list value as it would be the next Event date based on the sorting I have set.
In order to compensate for the lack of "recalculation" due to the change in current date, I turned off storage of the calculation comparing to the current date. The drawback to that change is that every time this summary is required it has to recalculate for all 40,000+ events in order to find <100 events. This recalculation takes forever when running it from anywhere other than on the same subnet as the FMSA system these are hosted on.
I was hoping for ideas to work around this problem.