Employee Scheduling

[crebma]

I have a dream that one day, my place of business will have a fabulous scheduling database that will do most of the work for them. Unfortunately, I'm not sure how to accomplish this task. Here's my vision:

There's a table that keeps employee availability. In this table, there are fourteen variables: one for am and one for pm for each day of the week. And these fields hold "Yes" if the employee is available, and, of course, "no" if the employee is not. Then there's another table that handles rotations of running shifts, patios shifts, etc. But I'm not even close to worrying about that right now. The actual schedule, I was thinking, would have a list down the left side with all available shifts. And then across the top would be Mon-Sun, with dates and all that. And when one clicks on the cell where "Monday" and "Main Floor AM" meet, I would like it to bring up a dropdown of server's names. But I would like that dropdown to only contain the names of those who aren't already scheduled for another shift that day, and whose availability says "Yes" for monam (Monday AM).

I have no idea how to relate the tables involved in order to make this work out. Does anybody have any ideas as to where I might find a useful demo, or if this is even possible? It would be incredible helpful to the company to have, but if it's not possible, it's just not.

Thanks just for reading this far!

Amber

[crebma]

I find it hard to believe that no one has done this yet... But, maybe it's just not possible?

How about this, then: Is it possible to have a value list displayed related records based on two different relationships? Ex: Shift is a table, that lists jobs and the shift time (am or pm), and then day is just a list of the days and their names. So the value I want in the value list would have to match both the shift time and the day name. Is THIS possible??

[crebma]

OK. update:

I changed it around a little bit. Now I am having a relational nightmare. it sees like I have to perpetually multiply all tables by seven to get them to work. this may be the case, But I'm goign to upload the file just the same. i stopped when I realized that I would have to make seven availability tables to relate to the seven shift tables, and then I realized I would have to make seven server tables to relate to the seven availability tables. And then on top of that, seven value lists to start from the seven different shift tables.

There has just got to be a better way to do this.

Any thoughts?

Amber

Here's the newest version of the file. Note that since I used the relationship between availbility and shift as am and day, people who can work either shift on wednesdays don't show up in the list, because the am for the shift is 'no', and the am for one who can work both is 'yes'. what to do?!?!?!?!?!?!

A

SCHEDULE_Clone.fp7.zip

Edited September 23, 2005 by Guest
Updated File

[crebma]

bump?

[Søren Dyhr]

Well I'm off on holiday, but I can hep you so if you send me a reminder on thursday!

--sd

[crebma]

that would be absolutely excellent. i really hope i make progress by then, but i'm sure i'll still need help. thank you!!

amber

[crebma]

Problem Solved! Soren, I aprreciate your offer for help, but somehow, I figured it out. Thank you so much!!

Amber

[VFXdbGuy]

please share!

I am working on a similar project.

Thanks!

[crebma]

Yes, soren, I would also like to take you up on that offer of help. This database has become something with so many tables that it's just crazy. So if there's an easier and more effiecient way, that would be awesome. I have attached the newest version of this file; it's still not completely finished for what I want to do with it, but it will be a million times easier to complete if we can cut down on tables. ] The password is blank.

schedule.fp7.zip

[VFXdbGuy]

My schedule is perhaps a tougher nut to crack.

I am trying to be able to see a linear schedule; weeks across the top with events appearing beneath the weeks when they fall into that week's date range.

The weeks are dynamic: week 1 is this weeek but on Sunday next week will become week 1. I know the relationship from the Weeks table to the Events table is working, but displaying all the weeks and their related events on a single layout has evaded me. I am sure it is a set of portals I need but I cannot figure out what parent table will work

The file is in very rudimentary shape with navigation only via the layout selector. Thanks in advance for any hints anyone can provide.

LongRange.zip

[VFXdbGuy]

I take it back. Amber, yours is far more ambitious!

[crebma]

Well, this could be a good advice, but it's probably not, considering my situation. ] I poked through the free version of the CC calendar, and discovered that they use 7 keys, one for each day of the week. Now it might be something different, but days of the week works for me. The problem on my end was that using seven keys and subsequently seven date tables forced me to use seven of everything stemming from that. And as you can see, that's a pain in the butt! It works, but it's extremely time consuming, and probably makes for a larger file. ::shrug::

I don't know, but I figured I've gotten enough free help on here, and I should try to give back. Let's see what Soren has to say on the topic.

:]

Amber