Page 1 of 1

Sliding window for recurring appointments

PostPosted: Mon Jul 27, 2009 3:18 pm
by PJB
John,

First off, great product, well worth the money. I run a residential maid service and built our current database almost ten yrs ago now using FM5. It is a testiment to both filemaker and your product that that after no filemaker development on my part for at least five years, and five versions of filemaker later, I can step in and integrate your product and in just a few days have a very solid base to build on.

I'm hoping you can nudge me in the right direction in the following:

1. We have regular clients who get weekly, bi-weekly, etc maid service. So it's basically an open-ended recurring appointment. I would like to schedule about eight weeks out, anything more is over-kill. I'm thinking a weekly script that would somehow check what appointments should be added. It would also need to safeguard against the occasional cancelled clean or clean moved a day or two. i.e. Joe Schmoe gets cleaned every monday. In a month hi is going away for a week and calls in and cancelles that appointment. When the script runs, it needs to know to not reschedule that week. Same if he just moves the appointment to thursday of that week. I'm not sure if this should be as simple as a script going in and extending the end date for each appointment marked repeating or if it needs to be attacked from a lower level.

2. I have set-up resources as employees. We have about 60 employees and I would like to expand your schedule view with resources across the top so it is a zoomed out version (hoping for 10-15 resources per screen) I haven't started this, so I don't know what issues I will run ito, but any pointers would be appreciated.

Thanks,
PJ

PostPosted: Tue Jul 28, 2009 8:20 am
by John Sindelar
PJ,

Thanks for the kind words. Its a testament to your work back in FM5 that you're running your business on the same code base all these years later. FMP is a pretty amazing tool, isn't it.

So, your questions...

1. I think it would be much simpler to schedule them out much further than 8 weeks since it is so easy to manipulate the appointments once they're scheduled. For example, since the calendar treats each repetition as its own discrete event, you can - or delete any one instance without messing up the others. Also, you can use FileMaker's built in Replace Records command to perform batch changes on the ones that are already scheduled. So if someone cancels all together you can search for the schedules appointments from today onward and delete them, or once you've found them you can perform batch Replaces on them to switch the time, the employee, etc. Or simply delete them and create a new repeating event if they switch days.

Using this approach we recommend scheduling a year out. Also, this requires no code changes.

Even if you were to write a robot that periodically extends the schedule by 8 weeks, you'd still have to edit and delete appointments as described above: you'd just be doing it to fewer appointments. And since FMP doesn't care how many appointments you're deleting or Replacing...

=)

2. On the subject of extending the columns to 10-15 I'd strongly recommend against it. Each additional column slows the screen by about 15% and in our tests, 10 column screens were unusably slow. Instead I'd try two built-in tactics.

First, you can segment your employees into "Resource Types" so you can filter the schedule by resource type you're looking at the first 5 *relevant* employes. Maybe these types are by the days they can work, by skill set, pay rate. I'm not sure, and that may not work for you.

The second approach is to use the horizontal scheduling view, which will let you show all 60 resources down the left side of the screen with times across the top. You see less detail about each appointment (and thus, this screen is much faster) but you can see more by hovering over an appointment as its tool-tip reveals the details. And, of course, clicking on an appointment brings up its editing window just as in the vertical schedule.

I think you'll find that more satisfying than building a 10-15 column horizontal version.

Hope that helps,

John

PostPosted: Mon Aug 10, 2009 7:55 pm
by PJB
Thanks for the tips, the horizontal layout is a better starting point for what I need. I will be expanding it shortly and may be back with more Q's.

Right now I am trying to add employee availability and hours worked to that same horizontal layout. I am using the appointments table and creating a record for each employee (resource) for each day with the hours they are avail to work. Records for future dates are availability (appt type= availability) and records for past dates get the appointment type switched to timecard and it is changed to the actual hours worked. I am trying to exclude these records from the conflict relationship, but I can not get it to work.

I have found your Tip: scheduling resource availability and am working off of that. In there you say it is possible to edit the conflict relationship so that items must be of the same type to conflict. I need to expand that idea slightly so that groupings of appt types don't conflict. I have about ten appointment types set up. Most are various types of cleaning appointments and then I have the availabilty and timecard types. So I have set up a calc field that returns 1 if it is any of the cleaning appt types and 0 if it is avail or timecard. 1's should conflict with 1's and 0's should conflict with 0's. 1's and 0's should not conflict. The calc fields is titled ApptTypeNonConflict.

So I have added the additional pair ApptTypeNonConflict = ApptTypeNonConflict to the relationship btwn CalDailyAppts and CalDailyApptsConflict, but this is not avoiding the conflict even though the two appts have different values for the calc field.

I feel like I'm inches away and missing someting minor, but I've tried every variation I can think of and can not get it to work. Any tips would be appreciated. Calc is set to be stored and indexed. Table occurances and multiple value relationships are still new to me and I am trying to wrap my head around them. Thanks for any guidance.