Appointment Calendar - empty slots

[smilbert]

Somebody must have tackled this before?
I have CRXI and MSSQL 2000

I've got table of appointments that includes the SchedStartTime (DateTime).

I'm trying to create a report that shows the entire daily calendar. My first report showed the appointments one right after another, which makes sense because that's the only data in the table.

However, we have appointment SLOTS available every 45 minutes, so I'd like to display the TIME (and no patient info) for the slots where there are no appts.

My first pass at it (ugly) was to build a several formulas (SchedStartTime+45), (SchedStartTime+90), etc

Then, create additional Data sections that were supressed if the difference between the SchedStartTime and the next(SchedStartTime) was > 45, 90, etc.

It works, but seems ugly and very manual. It ALSO fails if after the last appt. I could fix that, but it's just getting uglier.

Any suggestions?
One caveat--I don't always know the time of the 1st appt (different on weekends than weekdays). Everything is on 15min increments, and the 45 minute SLOTS follow after that.

 

[lbass]

Can you explain your report structure (groups, etc.) and then show a sample of how you want the report to display?

-LB

 

[smilbert]

Hi Linda, thanks for looking.

I'll try to give you all the info you need, without writing a novel.

There are a bunch of tables, but I've created a VIEW to make a lot of it simpler.

Essentially, there is an APPOINTMENT table that has an AppointmentID for each appt ever made. For each appt, there is lots of other info, SchedStartTime (DateTime), SchedEndTime, Name, ApptType, Room, etc

What I'm looking for is to create a "DAY SHEET" calendar for ONE DAY, with a row for each appointment slot and EITHER the patient info (if there is an appt) OR a blank spot (if no slot).

Unfortunately, there are lots of CAVEATS! We have multiple ROOMS, which each have a different START time. Within the room, the Start time may be different on different days. And to really throw this off, one of the rooms has 30 and 45 minute appts!! I'd be happy to handle the 45-minute room, though.

Since everything is based on 15-minute increments, I'd even be happy if I could figure out how to build a calendar with a fixed OPEN/CLOSE time, that would display 15-min increments, showing the scheduled appts in the apropriate slots.

Essentially, I'm willing to be flexible to try to get as close to ideal as I can.

Thanks for any input you can offer!

it would have a row for each 45-minute increment starting at som

 

[lbass]

I am unclear on whether you would have any groups based on room, date, etc., or whether you would be using parameters to limit the report to one room and one date.

-LB

 

[smilbert]

Sorry--I only need to print reports for one room / one day. So we can use parameters to limit our data.

 

[lbass]

I will get back to you on this tomorrow. Sorry to keep you waiting.

-LB

 

[smilbert]

Please don't appologize! This board, and people like you are a great resource! And for the price, I certainly can't complain!! Thank you!!

 

[lbass]

In the main report, add a table that has at least as many records as the maximum number of slots you could have, and place a field (suppressed) in the report header. Then create a number parameter {?Interval}, a time parameter {?StartTime} and a time parameter {?EndTime}. Also create a date parameter {?Date} and {?RoomNo} parameter. Create a report title formula:

"Room " & {?RoomNo} & chr(13) & {?Date}

Place this in the report header.

Then create slot formulas like this:

//{@slot1}:
{?StartTime}

//{@slot2}:
time(dateadd("n",{?Interval}*1,datetime(currentdate,{?StartTime})))

//{@slot3}:
time(dateadd("n",{?Interval}*2,datetime(currentdate,{?StartTime})))

...increasing the multiplier by one each time, and making enough formulas to equal the maximum number of slots per day, based on the smallest interval you would use, e.g., 15 minutes.

Then create a formula:

//{@SlotGroup}:
whilereadingrecords;
timevar array x := [{@Slot1},{@Slot2},...{@SlotN}]; //add actual formulas
numbervar i := i + 1;
numbervar j := int(datediff("n",datetime(currentdate,{?StartTime}),datetime(currentdate,{?EndTime}))/{?Interval});
timevar y;
if i <= j then (
redim preserve x[j+1];
y := x
);
y

Insert a group on this formula. Size the group header height to correspond to the desired height for empty slots. Insert a subreport in the group header that uses the appointment table. Create parameters for date and room (exactly as you did in the main report), and set up a record selection formula that references them. Add the desired fields to the detail section. Place the subreport in the group header section.

In the main report, go to edit->subreport links and link the date and room parameters to each other by using the dropdown box in the lower left to select {?Date} instead of the default {?pm-?Date}. Repeat for the room parameter. Also link {@SlotGroup} to {Appt.StartTime} and link {?Interval} to any field in the subreport (just to get the parameter to pass). Then go into the selection formula within the subreport and remove the link to {?Interval} and adapt the formula so that the formula reads:

{appt.room} = {?Room} and
{appt.date} = {?Date} and
{?pm-@SlotGroup} >= {appt.starttime} and
{?pm-@SlotGroup} <= {appt.endtime}

Next create a formula {@currprev}like this:
whileprintingrecords;
shared numbervar currID;
shared numbervar prevID;
prevID := currID;
currID := {appt.apptID};

Place this in the detail section and suppress the formula.

Then still in the subreport go into the section expert->details->suppress->x+2 and enter:
whileprintingrecords;
shared numbervar currID;
shared numbervar prevID;
currID = prevID;

This suppression formula allows the data to suppress whenever an appointment covers more than one time slot.

If you would like an arrow that points down to the end time for the appointment, create a formula like this:

whileprintingrecords;
shared numbervar currID;
shared numbervar prevID;
if time(datetime(currentdate,{appt.endtime})+1/1440) =
time(dateadd("n",{?pm-?Interval},datetime(currentdate,{?pm-@SlotGroup}))) then
(
if currID = prevID then
"|"+chr(13)+
"|"+chr(13)+
"V" else
"|"+
"V"
) else
"|"+chr(13)+
"|"

Place this formula in the subreport report footer and format it to "can grow" and change the font to "Courier New". This formula will only work properly if {appt.endtimes} are entered as one minute less than the next slot. If appointments read 1PM to 3PM, 3PM to 3:15PM, then you should replace the appt.endtime field in all places with a formula {@endtime}:

{appt.endtime}-1/1440

-LB