Hi all,

in light of yesterdays fiasco (see feedback loop post), I actually rethought the whole system and came up with a much better design for a musician rehearsal scheduler (I think), but am looking for a Devils Advocate. If you have a knack for calculations, have a look. Could post a sample file if interested. Please bear with, slightly long explanation.

rehearsal scheduling file: OpenTime

Fields:

d_date

i_StartTime

i_EndTime

t_Name

t_MultiKey

c_StartTime= case(count(SelfJoin::Name)>=g_#OfPlayers, i_StartTime)

c_EndTime= case(count(SelfJoin::Name)>=g_#OfPlayers, i_EndTime)

c_OpenStartTime=

case(count(SelfJoin::c_StartTime)>=g_#OfPlayers, max(SelfJoin::c_StartTime))

c_OpenEndTime=

case(count(SelfJoin::c_EndTime)>=g_#OfPlayers, min(SelfJoin::c_EndTime))

c_OpenText= case(c_OpenEndTime>c_OpenStartTime, "Open on "&d_date&" "&c_OpenStartTime&" to "&c_OpenEndTime)-[the case statement may be unneccesary]

g_#OfPlayers

Relationship SelfJoin= t_MultiKey::t_MultiKey

t_MultiKey is a text field set by script (for d_date=2/8/03 i_StartTime= 1:00 and i_EndTime= 2:30, t_MultiKey would be set at 15 minute intervals to:

2/8/03 - 1:00

2/8/03 - 1:15

2/8/03 - 1:30

2/8/03 - 1:45

2/8/03 - 2:00

2/8/03 - 2:15

with carrige returns at the end of each line.

It actually works pretty slick, but there may be potential for error. Lets say we are trying to set a rehearsal for peter paul and mary on 2/8/03.

peter: 1:00-3:00, 5:00-7:00

Paul: 6:00-8:00

Mary: 2:00-6:00, 7:00-10:00

6 records total.

g_#OfPlayers would be set at 3 for this example (peter, paul, mary)

the formula should show only that there is an open block of time from 5-6. It does, but I am worried that there may be a scenario where it will indicate an incorrect opening.

Any takers?

-Raz

Hi Raz,

I must have misread your post. Tell me more if I did.

1.You are looking for common range of hours for n players where n is set by a global

2. Each players in your file have already range of hours created that creates a Multikey.

c_common range hours = Case (Count(selfjoinonMultikey)=g#of players, c_multikey, "not possible")

Looking back to your example, I think this calculation would return

16:00

16:15

16:30

16:45

17:00

you should therefore use another calc to show each ranges in this result.

As a first thought, c_rehearsal hours = Left(c_common range hours,5) &"-"& c_end rehearsal hours = Right(c_common range hours,5) would give you 16:00-17:00 but it wouldn't be the case if there was 2 ranges 13:15-15:00 and 16:00-17:00. This calc would give 13:15-17:00 which is false.

There must be something I didn't see.

Would really want to see your sample file.

Hi Ugo-

c_common range hours = Case (Count(selfjoinonMultikey)=g#of players, c_multikey, "not possible")

Yes, you are right that this would be false, but that was not my calc. That is why I came up with a calc field referencing a calc field for both start and end times. I have been playing around with it a bit more, and it really seems to work great, but still am not sure if there is some condition that will cause it to fail.

I posted a sample if anyone wants to try and "stump the scheduler." You need to make sure to set the multikey after making a new entry (will probably write a calc multi next), but really works instantly.

Please excuse the thrown together layout/interface.

Cheers,

-Raz

Scheduler.zip

Hi Raz,

All was going Ok, I even delete the post I had made while you were posting this attachment.

But here is the case when all 3 have a common range set to 2:45 -3:45, and another entry is made for one of them to 3:00-4:00, your calcs seemed to forget about 2 "quite" similar entries, setting the common range to 3:00-3:45.

So the user must be sure that there is not already a similar date range. However, as I was not familiar to your file, I tried to download it again, set again 3 with the same range, and it worked. Here is back the file where I got the "failure".

Ugo,

Yes, I am planning on implementing data verification steps (scripts or calcs) to prevent user errors (overlapping entries, incorrectly formatted times, start time later than end time...etc). It is more the meat of the calc/relationship itself that I am concerned about. I think it should cover all cases:

for 3 people, a chunk of time needs to be related to at least two other chunks of time that are related to at least 2 other chunks of time. Sound reasonable?

-Raz