Scheduling of Employees using Filemaker

There was a request to write an app using FileMaker to do employee scheduling as part of a overall project for HR to check employee attendance. The request is quite peculiar.

1. The business opens 7 days a week.
2. Each employee is entitled to have 2 days OFF each week.
3. Supervisor and back up must not be off on the same day.
4. Special task and back up must not be off on the same day.
5. Other team members must be evenly distributed across the week.
6. The whole team must be randomly and evenly distributed across the week.
7. The order of schedule by person must also be random.
8. Employees can request for OFF on specific day on per request basis (to coincide with their Leave).
9. The schedule must be displayed by month with days of the month on columns and employees on rows.

Although you can define up to 250 million fields, it is impossible to use date for fields/columns otherwise you must keep adding fields with specific date. FileMaker does not have scripts that could create fields and add to table layout. It is easier to use employee for columns as the number of employees are limited. Moreover, the business is small thus easier to set a maximum number of fields/columns and increase them when necessary.

The first table is structured with first field/column as date followed by a fixed number of employees with field names designated as Exx. xx denotes a two digit leading 0 number. Its a small business thus need not set a large number. The first row is designated as Employee name. The date field is blank and each employee column is set with an employee name. It is easy then to just add rows with dates by month. This is highly unconventional. However, since the requirement is to show by month, it is still viable.

The search function should have 2 criteria

1. blank date
2. dates of the specific year/month.

Sort the records by date and you get a nice table with employee on row one and the rest of the dates below it.

Item 9 is using dates as columns. Since it is displayed by Month, It is easy to create a temporary table by transposing the rows into columns and add weekdays as row 2. The table have 32 fields. First field is employee name. Next field is day 1 of the month and so forth.

It is easy to create one row with days of the month as data. Row 2 will then be the dayofweek translated into text using case function. I am sure you know how to translate number to text.

The method is to use ExecuteSQL to retrieve one employee at a time for the whole month. Since we set a maximum number of employees. We can retrieve the whole list of employee like below.

ExecuteSQL("select E01,E02 from firsttable where scheduleDate is null fetch first 1 row only";¶;"")

We will also retrieve the whole months of data like below

ExecuteSQL("select E01,E02 from firsttable where sMonth=? and sYear=?";¶;"";firsttable::gMonth;firsttable::gYear)

gMonth and gYear are global fields for user to indicate which month and year to get data from. sMonth and sYear is the month and year of the date (calculated field)

Do a loop within loop. The outer loop adds a new row and uses the result of the employee list to retrieve them one by one. The inner loop picks up one particular value from the list as defined by the first loop.

For example, outer loop choose item 1, inner loop goes through the entire list but picks item 1 from list only. This will populate the table row by employee E01 and those E01 values by date.

The result is a nice transformation of row into columns.

The next thing is to fill in the data for the first table. Use a drop down list tor each field in the table. It ensure standardized data definition.  This will allow leave applications and OFFs to be added into the table. With this data added, we need to do the actual scheduling.

Item 8 above allows for pre-request OFF days. Therefore, the scheduling must take into consideration the pre-defined off days. It is much harder to find those pre-defined off days and leaves and do a schedule on the first table directly.

Therefore, a third temporary table is required. The table structure is similar to the second table except the columns are only 7 which consists of the days of week starting from Sunday. We retrieve the weeks data from the first table sorted by date like the translation method for second method except we don't have to add the first 2 rows. Also, you should skip those columns without employee name defined to shorten the computing time.

In addition to retrieve employee name and the 7 days. we need to define additional fields to facilitate the scheduling. This is for reasons which we will explain later. The additions are as follows

1. We need to find out which Employee is assigned to which field/column in the first table. This is to allow for locating the particular employee after we do random sorting.

2. We need a column to fill in the random number for sorting purposes.

3. We need 7 column as summary fields (count) for each day of week to check the off and leave distribution.

4. We need a global field to store the first day's date so that we could update the table later.

Now, we retrieve the data from the first table and transpose it in to this third table. However, there is a twist. Instead of retrieving the literal text, we have to translate it to 1 or 2. Leaves are designated as 1 and off and pre-defined OFFs are designated as 2.

Immediately you will see that the 7 summary fields will show the count of all leaves and offs for the day.

Item 3 requires Supervisor and backup to be scheduled not on the same day. Obviously, there must be at least 4 days available for scheduling. First schedule one person by randomly choose between the two. Just get the list of days which are blanks. You can just go to the row and read each day to see if it is blank then add the field name to list. Randomize the list and set the first two day as OFF. you must first count the number of 2s. Set the number of OFFs accordingly.

The schedule for the second person is a bit tricky. You must use a count function in the SQL and limit to the two person to retrieve the blank dates. This will effectively ensure that only those days that both are not schedule can be selected. Randomize the list again and set the first two days as OFF. Before setting any OFFs, you must first count the number of 2s. Set the number of OFFs accordingly.

Repeat this for Item 4. Suggest to put Supervisor and back up as row E01 and E02. Special task and backup as E03 and E04. This will make it easy to identify which one to schedule.

Now, before we could do anything for the rest of employee, we need to randomize the employee list. This is easily done by filling in a random number. Random numbers in FileMaker are from 0 to 1. There are custom functions that could provide integer random numbers within a range. Suggest to use a range at least 3 times the maximum number of employee list in table 1.

Sort the table by the random number field. This will effectively randomize the employee list. Note that it is the entire employee list. This include supervisors and special tasks. This action satisfy Item 5 and 6.

This is when the 7 summary fields are coming into play. We will choose the first summary field that has the smallest count. Since is it is summary field, it appears in every row. First set a variable with a large number. Second set a variable that is blank. Go through each summary field. if the count is smaller than the first variable, replace the first variable with the count and set the second field as the corresponding field name of the day of week. Just set the fiend name as OFF ("2").

Go through the entire employee list using a loop of 2. You need to redo the random number and sort again to scramble the employee list before the second loop. To schedule exactly two OFFs, you need to first count the number of 2s for the person. If it is already 2 then skip to the next employee. This will effectively allows for pre-defined OFFs to be treated like off. You can even repeat the same week 's schedule and it will not alter the schedule if there are already 2 OFFs assigned to the person. This will satisfy Item 7.

Once the schedule is complete, you need to translate the table back to the first table. First limit the first table to just the week and sort it by date. Remember we stored the first day of the week as date? Just create 7 variables to store a date by adding 0 to 6 days to the first date. Use the 7 variables to create 7 search condition and put them in a "find" function.  Use a SQL to read the third table by the weekday and the employee field name.

Again we need to do a loop on the rows in the first table. Get the date and translate it to dayofweek text. We do a inner loop to go to the whole list of the third table to find the employee field name and the particular day of week. Update the first table with "Off Days" if the first table is blank and the third table data is "2".

The result is a nicely updated OFF schedule.

Note that you may need to get the data of the field by using GetField("fieldname"). This is to allow for getting the content of the field by calculated field name.

You also need to do update to fields by using "Set field by name("fieldname"). However, the field name must be in this format "tablename::fieldname". I don't know why two different ways of defining field names but that is FileMaker. I end up using Get(LayoutTableName) & "::" & "fieldname".