Creating a simple schedule for patients-- files attached

[Steven Cappiello]

I've signed up for a class on, you guessed it, database management.. one of our big assignments it to create a database for an existing small business. I know a doctor who has a paper-based practice (and may never fork over the time & $ to invest in a proper practice management system) so I figured I would give a crack at putting together something simple.

My goal is to create a system that will track patient appointments, allow the system to send reminder letters, and track information about each appointment after it happens (and is then a visit). On my first attempt, I crashed and burned. I'm attaching an attempt at an ERD and a filemaker file which will show how I attempted to put together a schedule. I'm a bit lost though because the list of times that shows on my schedule layout is just a portal from a table of times... so 2pm on one day is the same 2pm field on another day.. this makes no sense.. 2pm on tuesday needs to be different from 2pm on thursday or next tuesday.

Do I need to create a whole bunch of timeslot records for every single day :

any advice would be appreciated!! -)

doctor.zip

[Ender]

Well, scheduling solutions are not at all simple. You can read some discussions about implementing scheduling in these threads:

http://fmforums.com/forum/showtopic.php?tid/139315/post/139482

http://fmforums.com/forum/showtopic.php?tid/169741/post/175376

http://fmforums.com/forum/showtopic.php?tid/124596/post/150700/#150700

[Fenton]

No, you only need the time slots from one day in the TimeSlots table. You get a view of different days by changing a global field, which is part of the compound relationship back to the Appointments data table.

I don't really get what you're doing with all the IDs in the Appointments table. Nor do I really see the need for a Week table. I would just handle that with calculations on the global date field. But I imagine either would work, as it's just relationships to produce "columns" showing a week (or a number of days).

Basically in TimeSlots you've got:

_gDate

Time (one per slot, created with a Loop)

(optionally _gPerson, if you have multiple people, multiple doctors in your case)

In Appointments you've got:

Date

TimeBegin

TimeEnd

PatientID

Compound relationship, from TimeSlots to Appointments:

_gDate =::Date

Time >=:TimeBegin

Time <::TimeEnd

I redid your file a bit. I also deleted your Insurance form, which was bloating the file. So you'll need to put it back.

[P.S. Look at the Timeslot layout.

More days is just adding 1 more to the global date.

You'd want a way to add new Appts. by clicking on a time.

Best when combined with a pre-chosen global PatientID.]

doctor2.zip

Edited October 17, 2005 by Guest
PS

[Steven Cappiello]

Thank you Fenton & Ender!

I actually kind of understand the way calendars work now!!!

what a huge leap of understanding for me..

I've given a first crack at implementing this...(file attached) and while I've build something that seems to work.. I'm afraid it has some big conceptual problems..

I feel as though the origin of the relationship "looking glass"

should not be the workdate table.. i was struggling with how to avoid having to set the date globals in various tables via a script..

And I feel like there should have been a way to avoid a lot of the repetitive work i did when defining fields, relationships and scripts.

On top of all this... the file works well when hosted locally or on a LAN, but when I VPN'd into my company's intranet from home, this beast is terribly slow.. you watch as all the day portals fill up one by one. Removing the container fields did little to improve this speed problem. So.. I'm also wondering if there are ways I can speed things up.

even if this is as far as I get.. I'm very happy with the results .. so thanks again

and of course, if anyone is up to it, I'd love to hear where I went wrong and how the attached file could be improved.

cheers!! -)

doctor.fp7.zip

[Fenton]

I have no problem with setting the 7 globals in Timeslots, instead of having Workdates. But I imagine if you were keen to only show certain dates, not a contiguous block, then workdates would be needed. In your case you've got your "day notes," so they need the actual dates.

(BTW, I tried moving those relationships off to the other side of Workdates, on their own, to see if it made the portals faster. Not really.)

But none of that matters a whole lot. I believe the speeds you're seeing are about normal. Timeslots by their nature have several portals, with a fair number of rows, showing related fields, and are not fast. If you're getting decent LAN speeds that's about it.

You can try various tricks to cause the window to draw all at once. On mine I flip to a blank layout then back. You're using a New Window, then closing it. Similar. I creating mine back in 5, before that.

A crazy idea, for VPN access, run a script to Preview a week, then copy/paste them into container fields. It would be slow while it happened, but pretty quick after that, for flipping back and forth. Or save as PDFs?

[Steven Cappiello]

Thanks Fenton. The reason I decided to use workdates is because the office hours may vary quite a bit, and I needed a way for days in the future to have irregular office hours.. so the doctor can scroll to a tuesday 3 weeks from now and indicate that she is working a half day that day.. then when the secretary pulls up that date, the portal will only show the hours the doctor is in.

would there be a way to do that without the workdate table ?

[Fenton]

I can think of a way, but you're not going to like it. Rather than creating a single set of Times, which are used by all dates, use a script to create an entire set of times for any date which is asked for. It would be lots of records. The script itself would be pretty fast. And the screen drawing would also be faster. I cannot say whether it would be worth it however. All TimeDate slots after today could routinely be deleted. How often do you need to look at the schedule for yesterday? Remember the data is really in Appointments.

I think you're taking a speed hit right at the first by using comparison operators for time from the Workdays table. But it works well for what you want. And the only way to know the difference would be to build both methods and test (yuk).

Also, comparison operators may be a little slower than using multi-line calculation fields, which is what we all did back in 5. FileMaker 7 has made these things so much easier. But they may not be quite as fast. Pretty close though, I think; but I have not done a real test comparing the speeds.

(P.S. You need the WorkDates table in any case, for the doctor to enter their available hours range for each date.)

[Steven Cappiello]

Thanks.. I'm very happy with how far I've gotten with this... to think at the beginning of this message I had virtually no understanding of how calendars could be created in Filemaker..

-)