My workplace has finally agreed to upgrade to FM13 from I think the first or second version.
If they are really using FileMaker 1 or 2, they really can't "upgrade". The software has changed so much that you will be better off just designing a new database from a blank page in the new version.
You seem to be asking for someone to design a full up database system for you. I suggest that you break your project down into smaller steps and seek assistance for each small part while also investing time and $$ in learning how FileMaker 13 and relational databases (filemaker 1 and 2 were not relational) work.
For starters, you'll need a minimum of 3 related tables, probably many more, but for your shift assignments, you'll need a table of shifts with one record for each shift, a table or employees with one record for each employee and a "join" table that assigns a given employee to a given shift.
You are right!
But I have already finished the FM12 training series and my goal is to create a completely new database. Also, I have already created a couple of tables ("Person", "Shift" and "Schedule") and "joined" them.
My biggest problems at the moment are:
- Making the creation of dates automatic and that the date in the Schedule table gets the right shift and persons.
- If I have a 365 posts with the dates from a year and have a field with shifts. How do I replace/insert the shift posts so that they have the right order (123412341234...)
- Is there an easy way to combine the tables "Person", "Shift" and "Schedule" so that the right persons and shift show up on the right date. FYI, the Schedule table contains the dates from 01-01-2014 to 12-31-2015. All the posts in the tables have unique id's, my problem is simply how to get them into the right order.
I might not explain my problems properly, but any help is very appriciated!
Do your shifts work on a 7 day a week schedule? Scripts can loop through your table of dates and assign a shift to each date in the pattern you show.
When you link a "shift" to a specific "date", this automatically links all personnel assigned to that shift to that date.
It looks to me like you may really have two or more different things named "Shift":
1) the current "shift assignment" for each worker. (George is assigned to shift 3....)
2) the actual people from 1) that will work or that worked on a given date assigned to that shift. (George isn't going to or didn't work last Friday due to __ even though that was a date for his shift to work...)
Is that a possibility here?
Yes you are right! Any good ideas? I think I have alreay come up with a solution to most of my problems. But I would really appreciate your thoughts and opinions!
I listed three different variations of "shift":
1) an employees designated shift--this can be a field in the employee table
2) The employees actually expected to work a given shift on a given date--these would be records in a join table
3) The employees that actually showed up and worked that shift--this would be a field in the above join table--sort of like taking class attendance.
You may or may not need all three.
Shifts::__pkShiftID = Employees::_fkShiftID
Employees::__pkEmployeeID = EmployeeWorkDate::_fkEmployeeID
Dates::Date = EmployeeWorkDate::Date
Shifts|Date::__pkShiftID = Dates::_fkShiftID
Shifts and Shifts|Date would be two Tutorial: What are Table Occurrences? with the same data source table.
Dates would be the table where a script might loop through the records assigning a value to _fkShiftID to link each date to a particular shift. Another script could easily pull up all employees assigned to a specific shift and then create a record for each in EmployeeWorkDate to assign them to work on a particular date.
A field can be added in EmployeeWorkDate to record whether or not they worked as scheduled or not. And additional records can be added to EmployeeWorkDate on an as needed basis--to record the fact that an off shift employee came in to cover for an absent employee to name one possible scenario...