Excel-like staff shift table

[Buckie]

Okay, I can't wrap my head around this at all. Please clue me in!

The task is to create a simple excel-like table where there will be columns with current month dates (like 1 to 31) and rows with employee names. Inside the cells the shift status is presented, it's either empty (no shift that day), day or night.

I can't understand how to program and present this at all! Either with a portal, or with a list view. Would be grateful is somebody could advise on the solution/implementation theory.

[LaRetta]

Hi Buckle,

If this is for display only, I should think repetitions would do the trick. So you should have an Employees table with EmployeeID, a Shifts table (with ShiftID) which would hold the various shifts possible and an EmployeeShifts table (which would hold the EmployeeID, ShiftID, and shift Date). Each selection of a shift for an Employee should create a new join record in EmployeeShifts.

Before we go further, can you explain how your existing setup matches up with the structure I am suggesting above?

[comment]

I am not sure about the necessity of a Shifts table: what information would it hold?

[LaRetta]

I know that in one business at least, a shift holds information such as:

• How many credentialed staff must be on this shift at one time
  
• Shift rate (shift rates vary)
  
• Visitation rights for a specific shift
  
• Manager 'responsibility' assignment (shift coordinator)
  
• Emergency procedures (would vary whether day or night and there were other differences as well)
  
• and four or five other items that I cannot recall off hand
  

It was for Title XIX facility. But you are right ... I am not sure in this case so thank you for bringing up that it isn't required.

[comment]

If they have stuff like that to record, then it is required. I would like to point out, though, that this is a "shift types" table - i.e. it would hold around three records in total. This may not be immediately obvious to the casual reader.

[LaRetta]

If you or Buckie only have day, swing and night then sure. But many facilities run multiple shifts throughout their facilitiy every day. For instance, day shift can have overlapping times (in residential treatment facilities, times must overlap to properly staff the residents) and shifts are named by 'building location and age of children'. In all, there were 5 day shifts, 3 swing shifts and 2 night shifts. But still the number is usually only 3 or quite low.

Buckie, my apology for calling you Buckle. That silly i looked like an l. :geek:

[comment]

Yes, the exact number could be higher (or lower). The important point to notice is that a shift does NOT have a date attribute.

[LaRetta]

Correct, only EmployeeShifts would have a date.

[Buckie]

I'll try to explain more. I've never used repetitions so it's a bit of a gray area to me.

Whether it's for display only or not is hard to decide. Right now it's all being done manually in Excel and the operator types the shift type into a cell. So probably it would be best if those fields were clickable and rotate values between "day, night, empty". Second, there should be summary fields at the bottom (total persons on day and night shifts that date, number varies...) and at the right side (total of shifts per person for the current month, currently it doesn't differentiate between day and night shifts). Other than that it's pretty basic. Shift rate is fixed, hours are fixed, no specific complications, not even swing shifts. Need to have it printed out to hang on the board so that people could see and plan their time for the whole month and be able to correct it easily on the fly in case somebody trades their shift or can't go on a shift for any reason. The way I see it is there should be a pop-up list of current year's months that an operator can select that would instantly change the historical data in the table below. Makes it even trickier since months have different amounts of days. Oh, and in the original table they have two unrelated shift tables for the two departments on one sheet, that could probably be circumvented somehow when printing to avoid further layout complications. Finally, ideally the table would populate itself, that is the reason I even bother with implementing it in FileMaker, but I know how to do that - it's the presentation that I cannot wrap my head around.

Tell me this: is FM the right tool for this job? It probably calls for completely dynamic layouts and that is not FM's strength...

[LaRetta]

Hi Buckie,

FileMaker is certainly the right tool to track employee shift assignments and also provide the UI as (or similar to what) you describe.

You said, "... in the original table they have two unrelated shift tables for the two departments on one sheet"

I suggest that both shifts be in the same table, with a Department field. Filtering then (relationally, through portal, or through found set) can take care of separating the information. It would help to have some idea of how you are structured since you indicate you already have it in place (or at least started). Also, can an Employee ever work for a different department, ie, work day shift for Dept A and then night shift for Dept B? On same day? Is the department assigned in the Employee record?

You said, "It probably calls for completely dynamic layouts and that is not FM's strength..."

Can you explain more what you mean here? What do you envision when you say 'dynamic layouts'?

Anyway, we first need to be sure you are structurally sound (thus my questions) before we move to the presentation layer, so any information in this regard (hopefully your file itself zipped and answers to the above) will help. :-)

[Buckie]

Filtering idea is brilliant! So, an employee can indeed work for a different department and that is usually marked with a special symbol in his schedule ("NK" in the attached file for employee 16). But this is rare and probably should stay that way. That particular month it happened on only one shift and it may not happen for several months afterwards. The employees are assigned to their respective departments. So again, nothing complicated happening in this regard. What I meant by dynamic layout is for example how I'm going to handle 28 days in February and 31 days in July all in one table neatly formatted. With portals you're stuck with predefined columns anyhow. If it was truly dynamic then the columns would be generated as needed, just as the rows. But, I'm willing to give it a try even given the limitations.

Now the attached embarrassing pdf file shifts.pdf demonstrates how we're doing it right now. No calculation fields work in this example (showing zeroes). But you should still get the idea. The operator cleans out the table each month and then starts filling the fields by hand, correcting them also by hand if something doesn't add up. Generally we need to have 4 day shifts and 3 night shifts per day. New employees are added by hand to the bottom of the department names list or get removed as needed. I'm looking to replicate the look of that table with the added benefit of having data in a manageable format and the ability to automate scheduling tasks and avoid errors. One somewhat important thing is that some employees need to have day offs on special days that they announce before the table is made and that it should be accounted for. Perhaps it calls for another hidden type of shift that is invisible when printing but accessible by scripts and operators.

[Buckie]

And to expand on that last bit - some of the employees also have shift preferences, for example one person will want strictly night shifts, no day shifts at all, yet another person dislikes night shifts and wants to avoid them as much as possible (yet that doesn't preclude them from having a night shift from time to time).

[LaRetta]

This would be perfect for a Repetition Master. The dates, don't worry, would be calculations ... I think of calendar solution Comment did (CalcSlots I think; discussion with JMO) or even Calendric. I will see what I can find. I could do it but it would not be optimum; I am weak in repetitions. As for Employee 16, if there is EVER the possibility of something then you must either design as if it can happen every day or restrict so it can NEVER happen; no in-between. With this in mind, you must allow for this possibility.

Employee 16 works for Dept2 but when they work for Dept1 you put an NK in the field? Should not Employee 16 appear in the Dept1 section with only the 31st marked) so they can be counted in that shift requirement? With proper structure, each EmployeeShift entry is a record and so Employee 16 could (and probably should) appear in both places, listed only for the days they are working. What do you think of that possibility? I am also surprised that this isn't tied to a timecard solution but we might want to ignore this aspect right now.

Shift preferences should be tracked in the Employees table. Then if an Employee is selected for a Night Shift, and they prefer days but would work nights, custom dialog can tell the User so they can select someone else if possible. For those employees who refuse to work nights (for example), you can use conditional value list so only those Employees who have agreed to ever work nights (even if rarely) would appear in popup for Night Shift selection. It can get more complex ... what if an Employee says they will not work weekends? These requirements would need to be fleshed out further. Vacation days should be another table related to Employees. Those days (for an Employee) would stop User selection of that Employee if they are scheduled to be on vacation for that day.

I will be quite busy for the new few days but if nobody provides a sample, I will take it on. I will not leave you hanging even if I hang myself in the process, LOL. FM (in the right hands) can handle this nicely.

[Buckie]

LaRetta, thanks, but don't you think I should do this myself? That's really generous but I'd hate to take your time!

That's right, I put the NK (night 'K') in there if an employee from Dept2 works night shift for Dept1. What you say makes logical sense, however the primary goal of this table is to provide a visual guidance for the employees so they can easily see what goes on with their schedules. But of course the NK solution doesn't help with the overall organization. But if we're putting one employee in two department tables they may be confused, again, because this situation happens so rarely. Well, for now I think it would be best if we consider that such a situation NEVER happens.

I don't think that dialogs are necessary for shift selections given that I plan to use scripts to automate scheduling. Reserved (day off) days concern me more however.

[LaRetta]

LaRetta, thanks, but don't you think I should do this myself?

Okay. :laugh2:

[Buckie]

I'd still appreciate though if you or someone else out there could sketch the table structure for me or give some clues. I still haven't got any idea on how to organize this internally.

[LaRetta]

Okay, yes my point of providing a demo is because it is too difficult to explain in a post thread. Some things are best explained by example.

I did not use repetitions (except for the day labels). I used filtered portals. You will have to carefully check each object. The day label repetition has conditional format. The actual slots are filtered portals and the Shift field inside each portal has conditional formatting. Note also the conditional formatting on slots 29, 30 and 31 both to cause the background to disappear and also text inside to disappear (see custom font size 500).

Script sets the Shift but also notice that 'allow creation of related' is checked in the graph. Others may have used different approach. Note that I did not include everything you discussed but I hope it gets you moving.

I only 'completed' the first five and last three days. You will have to replicate the functionality for all the rest of it.

File replaced with Shifts2 (which includes modifications suggested below).

File replaced again (smile) with Shifts3 which corrects filtered portal, adds day name headers and fine-tunes conditional format on cCalDays.

Shifts3.zip

[Buckie]

Wow. I'm speechless. You did it so quickly and it's basically what I was looking for. And the structure is so simple! Just two tables and one relationship...

If you allow me to study it for a moment...

[LaRetta]

Also ignore the value list I created for Shifts. I decided not to use it since the slots were too small to select from pop-up ... whereas toggle-script was easier.

LOL, it wasn't THAT fast, I started putting it together earlier today but stopped after you said you wanted to handle it.

ADDED: Personally, I would rather see a relational filter on those dates, at least filtering down to the current year or so. Otherwise if the volume of records in the EmployeeShifts table becomes large, it can get slow (particularly when served).

This would be my first 'attempt' at considering the approach. It is not optimum. It feels a bit convoluted. And it is ugly yet.

ADDED MORE: Note also that the cCalcDays is actually a date result. This is because I was originally going to use it in the relationship, changed my mind, but left it because it doesn't hurt anything and we might yet use it, LOL. I could have made that calc a number with simply: Get ( CalculationRepetitionNumber )

[Buckie]

I noticed that CCalDays is a date.

Could you explain in a nutshell how the EmployeeShifts::Shift works to display different values despite being the same field? That is the part I was looking for and I don't understand the theory behind it (or even how to manage those fields).

[LaRetta]

Hi Buckie,

Each slot (where you click to add N or D) has two parts ... a Shift field from EmployeeShifts and it resides in an invisible portal. The portal filters by three criteria, one of them being: EmployeeShifts::ShiftDate = Date ( Month ( Employees::gDate ) ; 1 ; Year ( Employees::gDate ) )

For the portal under the day 1, it is what I show in red. For portal under day 2, change that to a 2 and so on. cCalDays is indeed a date but I display just the day of that date using layout-level formatting (see Inspector > Data tab and at the bottom, date formatting). You must keep that portal with the field because the field filters depending upon criteria specified in its portal.

[comment]

This is almost identical to the file I started. A few notes, though:

Filtering by =

EmployeeShifts::EmployeeID = Employees::EmployeeID

is redundant when the relationship itself is based on such match.





I would still consider creating another relationship for this, based on:



Employees::EmployeeID = EmployeeShifts 2::EmployeeID

AND

Employees::cCalDays = EmployeeShifts 2::Date



then filter by =

Day ( EmployeeShifts 2::Date ) = 1 //etc.

This would reduce the burden of filtering to a maximum of 31 related records per employee.

IMHO, clearing the shift type should delete the record instead.

[LaRetta]

Using cCalDays in the relationship - that was the piece I was missing. I originally tried using it and I was surprised that it didn't work. I should have rechecked myself because that is exactly what it needed. And, as you say, this also drops the filter down to using Day() because the day would be relationally filtered down to the Employee and month, instead of requiring a comparison. Nice!

This is almost identical to the file I started.

That makes me feel better. It just doesn't feel elegant when I do it like it does when you do it.

Aha! I forgot that clearing the field should delete the EmployeeShifts record! I added sub-script to delete the EmployeeShift if the record existed but the Shift field is cleared. I also noticed that I left conditional formats on slots 1-28 by accident - they were empty but nonetheless it was slop, so I cleaned it up. I have replaced the file just in case anyone wants to see the changes that were made.

Thank you for helping me clean it up. :smile3:

[comment]

One more thing: you need to prevent cCalDays rolling over into the next month, e.g. =

Let ( [

g = Extend ( gDate ) ;

d = g - Day ( g ) + Get ( CalculationRepetitionNumber )

] ;

Case ( Month ( d ) = Month ( g ) ; d )

)

Otherwise you'll get March 2, 2012 in repetition #31 when gDate is in February - and a shift in March 2 will pass the portal filter in column 2.

[Buckie]

I'm slowly starting to understand that. Hidden portals is a very neat idea. You guys seriously rock with FileMaker.

The very first column however behaves oddly, sometimes nothing shows up yet the shift number is being increased or sometimes its contents changes with the changing of the third column...

[LaRetta]

Hi Michael,

I thought about this possibility before; now with change, I did not re-test. I had tried before to terminate cCalDays and did not succeed. I have never seen a calculation like this one, not even from you! It will add to my depth of understanding how reps work.

I found that slot 1, when you select it in Feb 2012, broke. It was set still on the original table occurrence. Making your new change also means we can simplify the conditional formatting on cCalDays by removing the font size 500 portion. I also added day of week which might help when assigning work days. We could also conditional format the weekend slots so data-enter won't get off-column but I figure it is easier to reverse engineer if we leave the slots themselves as clean as possible.

Hi Buckie,

Careful. FM should contain a warning label, "This product can become addictive. Study at your own risk." I realize I've been changing the files on you and I just did it again. Please see original post #17 for new Shifts3.fp7 for the latest corrections.

[Buckie]

Well, thanks so much for helping me out! There's a lot to study and learn in that one for me. Hopefully someone else with similar needs will find this template useful! I'm watching this thread for more optimization wisdom, however!

[Buckie]

One thing I've noticed is that switching through shift values gets slower the more fields are present in the form. It works quite fast when there are 5 of them but when you fill a whole month of those it starts to get slow. Is that to be expected due to the way the layout was designed?

[LaRetta]

I almost suggested that you display it in week increments when data-entering into it. Then use the full display only for printing. It is not really a surprise since it is display-intensive. It might help to remove the 'Commit' from the end of the script, if you can handle it not updating the totals. You can then have a button by the totals called Update. I believe that should help. Test it by just disabling the Commit and see how that runs.

[Buckie]

Well, using smaller intervals is one way to solve it, however in this particular situation it's not an option. Disabling the commit step did speed up switching somewhat but the delete step still takes a lot of time. There are ways around it of course such as purging 'deleted' records on open/close...

Do you think this could somehow be redone with a single portal, in theory? I tried initially to think about that approach but it seemed impossible.

Oh, and by the way, thanks again! It was really easy to fill all the missing bits such as counts of shifts per single day and modify the right column to display total hours instead of total shifts... So it's working great so far!