Many 2 Many

[Søren Dyhr]

I was wondering if someone could come up with fresh ideas to resource to client allocation over a span of dates. Multilinekeys created via repeating calc'fields or recursive definitions in custom funktions are great ways to avoid the join table in the first place.

But if you need to pluck out a specific date in such a span and say cancel the appointment/booking, is the join table manditory - but they suck creation wise since they demand plugins to react accordingly to changes in the bookings ...and if the span is demanding 2000 records is it going to last a while until the cursor is given back to the user from the script the plugin pulls.

Or is it really?? I've been fooling around with splitting the record holding the span into 3 record - one holding the span before the canceled date, one on the particular date and finaly a record holding the span above ...have others fresh ideas or other ways/methods to handle this??

--sd

[Ugo DI LUCA]

Hi,

Well, from what I understand, there are several scenarios.

One would need a Dates Table (where each calendar dates gets an ID) and a MultiLine IDs Key in the Bookings Table (auto-filled on creation and set to recalculate) getting its IDs from the Dates Table.

Then you'd be "working" on IDs rather than Dates and no repeating fields involved.

Any change in the booking should therefore react accordingly, but as you can see, you need a Dates Table filled with all possible calendar dates up to the necessary dates possibily needed.

[Ender]

There may be a way to do this with a Booking join table where each record represents the date range for the event or recurrence range. Then when a date in that range must be cancelled, add the date to an exception list (return-separated field) in the Booking record. I think it's possible to exclude those dates through the relationship.

I can't remember the details, but I did some work on this idea back in that SmartRangesCF thread. I'll see if I can find the file I was trying it on.

[comment]

Are you talking about events spanning more than one day (contiguous), or recurring events (which may span more than a day or not, but in any case they occur at intervals)? IMHO, these are two different things.

If a contiguous event is broken up by a single-day cancellation, then yes, you now have 2 separate events (not three, as the one in the middle is now a non-event).

If you are talking about recurring events, I still believe that there should be a text field (set by a script) with all the occurences. Then a cancellation/shift of a single occurence can be handled as any value list manipulation.

[Søren Dyhr]

Thanks for the input...

Michael you write:

If a contiguous event is broken up by a single-day cancellation, then yes, you now have 2 separate events (not three, as the one in the middle is now a non-event).

Well not quite, say you handle crews of phoners and one have expressed an urge to attend a funeral or a dentist appointment ...Whatever, I'm pretty sure that the person in question woudn't like to be assigned to a new task for that particular date and again regrettingly announce "I can't"

So a canceled appointment means don't bother assigning to a new task eventhough there is a slot...

Mike you wrote:

add the date to an exception list (return-separated field) in the Booking record.

Not a bad idea if it could make me override the earlier appointment, with say a prosponed appointment the same day ...I could imgaine that you have somethins like:

http://www.briandunning.com/filemaker-custom-functions/detail.php?fn_id=39

...in mind.

Ugo you wrote:

One would need a Dates Table (where each calendar dates gets an ID) and a MultiLine IDs Key in the Bookings Table (auto-filled on creation and set to recalculate) getting its IDs from the Dates Table.

Then you'd be "working" on IDs rather than Dates and no repeating fields involved.

If I weren't in a task of displaying conflicting appointments would I probably go for a filestructure like this:

http://www.filemakermagazine.com/modules...r=0&thold=0

It doesn't seem like the solution you suggests, can you expalin the difference???

--sd

[comment]

a canceled appointment means don't bother assigning to a new task

Yes - if you look at it from the point of view of the scheduled resource. From the point of view of the schedule, there's nothing happening.

The iCalendar standard has an interesting feature called Free/Busy Component, where each user can publish a set of busy time. But to implement such thing in Filemaker...

BTW, have you built Matt Petrowsky's scheduler? I don't know if this is a fair rendering, but it is VERY slow.

[Ugo DI LUCA]

Hi,

Not quite the same indeed.

A Dates Table will hold all possible calendar dates, and when building a task for Mr X spanning from May 2nd to May 12th, you'd get a multiline key of 11 ids from the dates Table, autoentered at the moment of creation by comparison to the Dates Table.

If you need to handle cancelations, then this autoentered Multiline field should be scripted and each cancelled dateID would therefore be substituted from the original key.

As Michael said, if this date shouldn't be allocated to Mr X any longer, then you should keep this dateID stored somewhere in a Person's record so that this empty slot wouldn't be reused.

For the visualizaion of such schedules, I like to use what I call a Strainer Table as the front end so that I only need one occurrence of each table rather than as many as days to be shown.

HTH

[Ender]

In a slightly different direction from what Ugo's proposing, I found my little Scheduler sample. To my surprise, it didn't take much more work to get it working.

Soren, as you guessed, my method uses a CF to exclude the dates in an Exception List from the dates in the entire range.

You can check the attached example if you're interested in this approach. The interface is a bit rough, but it seems to work. comment, this was a further development from our discussions in that other thread about scheduling repeating events.

Scheduler.fp7.zip

[Søren Dyhr]

Soren, as you guessed, my method uses a CF to exclude the dates in an Exception List from the dates in the entire range.

Thanks for the view, which is an interesting way of approaching some of the issues we encounter when challenged with such a task. BTW do I handle recuring events a little different than you borrowing an interface idea from Claris:

DateRangeKey(StartDate; EndDate; WeekDays)

Case (StartDate = EndDate; Case(PatternCount ( WeekDays;Choose ( DayOfWeek ( StartDate )-1 ; 7; 1; 2; 3 ; 4; 5; 6 ) ); StartDate);StartDate > EndDate;"";

Let ( CurrStart = Case(StartDate + 1 = EndDate; EndDate; StartDate + 1);

Case(PatternCount ( WeekDays;Choose ( DayOfWeek ( StartDate )-1 ; 7; 1; 2; 3 ; 4; 5; 6 ) ); StartDate & "

[Søren Dyhr]

A Dates Table will hold all possible calendar dates, and when building a task for Mr X spanning from May 2nd to May 12th, you'd get a multiline key of 11 ids from the dates Table, autoentered at the moment of creation by comparison to the Dates Table.

If you need to handle cancelations, then this autoentered Multiline field should be scripted and each cancelled dateID would therefore be substituted from the original key.

It's beginning to make some sence to me, so in order to make the join record display a conflict, must a selfjoin be established via the 3-4 multiline keys stored in it???

--sd