I know that timetables have been asked about in the FM world but, still i can't get my head all the way around the idea or concepts. I have an excel sheet (provided) that I have been asked to convert to FM. I most gratiously accepted hoping to hone my skills and thinking that this wouldn't be that tough...boy was I wrong. In looking at the spreadsheet, i was thinking that i need tables for the sessions (twilite, matinee, evening), one table for employees, one for the duties (supervisor etc..), and i think this is where i got lost. I was able to create the database using seperate tables for the days of the week. I have it working smoothly, looks just like the excel sheet. Even has the ability to highlight employees that are booked more than two sessions in a day. Then, i needed to figure out the hours each person is booked during th week. the column on the right in the FM database is the aloted hours for that particular dutie. it is the left most column in the screenshot.

Any sugestions would be greatly appreciated.

schedule.zip

Maybe this will help you get your head around it.

Schedule_eos.fmp12.zip

Thank you very much eos. I will have a look. Can I ask questions if I don't understand something?

Nope!… just kidding ;-) Of course you can.

Everything is tickety boo. I change the look and such to match their logo etc. I asked for some sample schedules to input. entered the first one all added up just fine. Then I started adding the second one and noticed that the total shifts and total hours do not change per schedule. I tried adding / modifying the relationships to adjust it but that didn't work. I thought that by isolating all the employees that pertain to that schedule would give me the correct numbers. Or by using the TO employeeperweek as the context that would work. Still to no avail.

Do you think you could shed some light on the situation.

Dave

Yes, I see what the problem is.

From the Schedule context, you can isolate and display the Employees by filtering through SessDuties into Employees; but when you look farther down the relationship chain to retrieve their schedule hours, you'll just get the first matching SessDuties record for that employee, because an Employee record in and of itself has no notion of a scheduleID, let alone the current one.

I've added a script trigger which sets a global in the Employees table to let it “know” the currently displayed scheduleID, and added this key to the relationship, so now every employee retrieves their SessDuties numbers for the correct schedule.

Hope this helps.

Schedule_new_eos.fmp12.zip