Smart Ranges Custom Functions

[comment]

Perhaps I should outline my purpose a bit. I am after a scheduling/booking solution, where:

1. Events can be both ranging AND recurring.

2. Conflicting events must be flagged.

3. Confirmed events are billed (this means that recurring events must not extend backwards).

4. Single occurances of a recurring event can be deleted/re-scheduled.

This is what I started with, and now I am gradually lowering the specs, as I run into FMP limitations.

You have surprised me by suggesting I "still use the FM ranges (or SmartRanges) for the date range part". Let me put SmartRanges aside for a moment, and state the question thus: can this be done without spelling out the actual dates? After all, SmartRanges is but a method to compress the information, AFTER dates have been spelled out.

As I stated earlier, I don't see how it's possible to relate two ranges by start and end alone. I believe this would require compounding OR criteria, which FMP cannot do. Compounding OR criteria CAN be done by multi-line keys, which in this case means - spell out the dates.

The other specifications lead me also to the same conclusion. I don't see how a "smarter recurring key" would deal with the problems I raised earlier:

- it too extends backwards;

- it cannot catch a conflict between two events defined in a different way but conflicting on a day that satisfies both criteria.

Moreover, since you are on Mac, take a peek at iCal's capabilities in defining a recurring event - esp. the Custom options. Of course, I realize that I will be never able to match that in FMP. I have already given up on the option of having a recurring event that never ends. But even a simple "every two weeks" seems impossible to do in this fashion.

Finally, the 4th specification leads me to conclude that dates need to be spelled out by a script, rather than by a calc. Although I have a rather neat calc using a repeating field, it's only by putting the dates in a text field that can I provide the ability to manipulate individual results.

Thoughts, comments, contradictions - please!

[Rob 7 Collins]

Just my two cents,

If you don't store the actual dates for recurring events, you can't always (or

even usually) flag which specific dates conflict, just know that they do (and

which event first conflicted -- if it is a specifically dated event, you know

the actual date).

The way I am thinking to store recurring events without every date is with

modulus operations ... you need a few calculations for regular years, leap

years, per type of recurrence (like On_day_of_week, monthly, etc), and a single

calc per type to deal with offset from the start/end range of the recurrent

event to the actual first date the event occurs. You store the actual first

date, and the given end date. When scheduling a new event, you iterate through

the recurrences, run the mod for this type of recurrence against the date index

(starting at the start date for the new event) and again against the actual

first date of the proposed schedule: pray for different numbers. You continue

to do this and increment the dates by the period of the recurrence until you get

to the same index in the recurrence (usually 400 years is the largest period in

the calendar, unless you're really scheduling ahead!). --That's not as bad as

it sounds, since to compare this proposed event to all other events, each

comparison is 2*400*recurrences_per_year -- never bigger than ~292,200 .. which

is not a lot of processor time with simple math equations.

You can find the modulii necessary in a good algorithms book ... or maybe try

googling words like modulus/modulii, algorithm, calendar/schedule, dates. Or,

you might want to go to CPAN.perl.org, or a php library site, and look at the

date modules. Perl is often written rather like a short-hand of a normal

algorithm notation. Find a module that schedules, skip around to the comments

for the functions you are looking for, and look for the <number>%<number>

sections.

You can actually find the specific date that clashed ... my math's a bit rusty

(I came back and edited when I realized the modulus result itself is not what

you don't want to match, that doesn't matter, it is that result applied to the

date index). That resultant date is the date that clashed.

For your fourth requirement, you needn't store the calculated result. You do

need to generate events of specifically queried dates; for the layout or

whatever GUI you've in mind, and that element of your code needs to check an

exceptions table and exclude certain records/generated text before passing

control to the user.

Cheers

[Ender]

Hey comment,

Lots of good stuff. Let's see if we can't figure some of this out.

As I stated earlier, I don't see how it's possible to relate two ranges by start and end alone. I believe this would require compounding OR criteria, which FMP cannot do. Compounding OR criteria CAN be done by multi-line keys, which in this case means - spell out the dates.

Range to range relationships can be done natively in FM7 with the example I showed before. However, I was thinking this range to range relationship would be used for defining the date range of recurrences when trying to add a new event. So when adding an event that recurs every Friday from today on, the range would be from today through 12/31/4000 (or some such,) If there are no conflicts in that range, then the record gets added, with a recurrence match field that will let future additions know that Fridays are already taken from 3/7/2005 through 12/31/4000.

I'm not sure how to fit an event that lasts more than one day into this recurrence theory of mine.

The other specifications lead me also to the same conclusion. I don't see how a "smarter recurring key" would deal with the problems I raised earlier:

- it too extends backwards;

- it cannot catch a conflict between two events defined in a different way but conflicting on a day that satisfies both criteria.

Using the range-to-range relationship starting from the first date of the recurring event should stop it from looking backward. And I think if the match key always contains all of the possible recurrence types, the conflicts would be seen.

Moreover, since you are on Mac, take a peek at iCal's capabilities in defining a recurring event - esp. the Custom options. Of course, I realize that I will be never able to match that in FMP. I have already given up on the option of having a recurring event that never ends. But even a simple "every two weeks" seems impossible to do in this fashion.

I think the trickiest things to reproduce would be an event that last more than one day, and events that recur every n days/weeks/months. I still think these types of recurring events are possible with a clever calc for the match field (I'm not sure how yet.)

Finally, the 4th specification leads me to conclude that dates need to be spelled out by a script, rather than by a calc. Although I have a rather neat calc using a repeating field, it's only by putting the dates in a text field that can I provide the ability to manipulate individual results.

Yes, this one is tough. I think a recurring event that gets changed would need to be pulled out of the recurrences to become a separate event. So maybe do this by 1. adding a new record defined for that date with no recurrences that has all the other info that the original event had, and 2. having another criteria in the relationship that does an exclusion of records for that date. I haven't worked out how all this would work, but I'll see if I can get something going.

[comment]

I need to ponder on this for a while, but one thing sticks out:

If I want to display events in a calendar fashion, I need the actual dates for the current calendar period. I don't recognize the mechanism you describe in the last paragraph. That is, I do - but not in Filemaker. I know that's how it's done in other applications, where the events table holds only the rules, and everything is rendered on-the-fly.

Filemaker is not that good a browser. If every time I shift the calendar's month I need to run a script to generate the dates of ALL events for the current period, I may be better off pre-generating them once and for all.

[comment]

I think we are still not understanding each other regarding the range-to-range relationship. Let's forget recurring events for now, and consider an event that starts on StartDate1 and lasts n days, thus ending on EndDate1.

Now we have a new event that we'd like to schedule, this one starts on StartDate2 and lasts m days, thus ending on EndDate2.

The 6 possible juxtapositions of the two events are shown on the graph above.

I don't see how I can build a relationship that will show a conflict, using only these four fields. That was my original question in this thread.

Using the range-to-range relationship starting from the first date of the recurring event should stop it from looking backward.

OK, that should be possible - like:

Wednesday = Wednesday

AND

CurrentDate >= first occurence

AND

CurrentDate <= last occurence

But what if, instead of last occurence, the user prefers to specify number of occurences (repeat every week for 5 repeats in total)?

Again, it seems to me that generating the actual dates is the lowest common denominator to all the possible scenarios.

But by all means, do continue to attack my position.

[Ender]

Putting the calendar overview aside for now, I would use the range to range relationship for identifying conflicts while adding the new event. As I said before, this covers all the possible ways the ranges can overlap. See the attached example.

Unfortunately, I don't think there's a good way to get this Date Range to Date Range thing to work with recurring events.

I'll work on an example of the recurring events for events that do not span multiple days.

I should think specifying the number of repeats rather than the End Date should be simple enough; just calculate it based on the Start Date, recurrence type, and number of repeats.

[comment]

OK, point taken and humble pie eaten. I thought you were referring to SmartRanges, when you described this relationship earlier.

Still, the other issues weigh heavily in favor of multikeys.

[Søren Dyhr]

Mike the template doesn't behave absolutely correct - say test 8th of july 2002 to 9th of july 2002, this produces a portal full of conflicts, but the same and more conflicts would be likely to show up in the portal if you removed the 8th ...instead does the portal clear???

--sd